node.js 如何使用 Node 从 mysql 正确返回结果？

Question

提问by Ken Ingram

In the code

在代码中

var stuff_i_want = '';
stuff_i_want = get_info(parm);

And the function get_info:

和函数get_info：

get_info(data){
      var sql = "SELECT a from b where info = data"
      connection.query(sql, function(err, results){
            if (err){ 
              throw err;
            }
            console.log(results[0].objid); // good
            stuff_i_want = results[0].objid;  // Scope is larger than function
            console.log(stuff_i_want); // Yep. Value assigned..
    }

in the larger scope

在更大的范围内

stuff_i_want = null

What am i missing regarding returning mysql data and assigning it to a variable?

关于返回 mysql 数据并将其分配给变量，我缺少什么？

============ New code per Alex suggestion

============ 每个亚历克斯建议的新代码

var parent_id = '';
    get_info(data, cb){
          var sql = "SELECT a from b where info = data"
          connection.query(sql, function(err, results){
                if (err){ 
                  throw err;
                }
                return cb(results[0].objid);  // Scope is larger than function
    }

==== New Code in Use

==== 正在使用的新代码

 get_data(parent_recording, function(result){ 
    parent_id = result;
    console.log("Parent ID: " + parent_id); // Data is delivered
  });

However

然而

console.log("Parent ID: " + parent_id);

In the scope outside the function parent_id is null

在函数parent_id之外的作用域内为null

Answer 1

回答by Alex

You're going to need to get your head around asynchronous calls and callbacks with javascript, this isn't c# / php etc...

您将需要了解使用 javascript 的异步调用和回调，这不是 c#/php 等...

Here's an example using your code:

这是使用您的代码的示例：

function get_info(data, callback){

      var sql = "SELECT a from b where info = data";

      connection.query(sql, function(err, results){
            if (err){ 
              throw err;
            }
            console.log(results[0].objid); // good
            stuff_i_want = results[0].objid;  // Scope is larger than function

            return callback(results[0].objid);
    }
}


//usage

var stuff_i_want = '';

 get_info(parm, function(result){
    stuff_i_want = result;

    //rest of your code goes in here
 });

When you call get_infothis in turn calls connection.query, which takes a callback (that's what function(err, results)is
The scope is then passed to this callback, and so on.

当你调用get_info它时又调用 connection.query，它接受一个回调（这function(err, results)就是
范围被传递给这个回调，依此类推。

Welcome to javascript callback hell...

欢迎来到 javascript 回调地狱...

It's easy when you get the hang of it, just takes a bit of getting used to, coming from something like C#

当你掌握它的时候很容易，只需要一点点习惯，来自像 C# 这样的东西

Answer 2

回答by gtugnolo

I guess what you really want to do here is returning a Promiseobject with the results. This way you can deal with the async operation of retrieving data from the DBMS: when you have the results, you make use of the Promise resolvefunction to somehow "return the value" / "resolve the promise".

我猜你在这里真正想做的是返回一个带有结果的Promise对象。通过这种方式，您可以处理从 DBMS 检索数据的异步操作：当您获得结果时，您可以使用 Promise解析函数以某种方式“返回值”/“解析承诺”。

Here's an example:

下面是一个例子：

getEmployeeNames = function(){
  return new Promise(function(resolve, reject){
    connection.query(
        "SELECT Name, Surname FROM Employee", 
        function(err, rows){                                                
            if(rows === undefined){
                reject(new Error("Error rows is undefined"));
            }else{
                resolve(rows);
            }
        }
    )}
)}

On the caller side, you use the thenfunction to manage fulfillment, and the catchfunction to manage rejection.

在调用方，您使用该then函数来管理履行，并使用该catch函数来管理拒绝。

Here's an example that makes use of the code above:

这是一个使用上述代码的示例：

getEmployeeNames()
.then(function(results){
  render(results)
})
.catch(function(err){
  console.log("Promise rejection error: "+err);
})

At this point you can set up the view for your results (which are indeed returned as an array of objects):

此时，您可以为结果设置视图（确实作为对象数组返回）：

render = function(results){ for (var i in results) console.log(results[i].Name) }

EditI'm adding a basic example on how to return HTML content with the results, which is a more typical scenario for Node. Just use the thenfunction of the promise to set the HTTP response, and open your browser at http://localhost:3001

编辑我正在添加一个关于如何返回带有结果的 HTML 内容的基本示例，这是 Node.js 的一个更典型的场景。使用thenpromise的功能设置HTTP响应，打开浏览器http://localhost:3001

require('http').createServer( function(req, res){
if(req.method == 'GET'){
    if(req.url == '/'){
        res.setHeader('Content-type', 'text/html');
        getEmployeeNames()
        .then(function(results){
          html = "<h2>"+results.length+" employees found</h2>"
          html += "<ul>"
          for (var i in results) html += "<li>" + results[i].Name + " " +results[i].Surname + "</li>";
          html += "</ul>"
          res.end(html);
        })
        .catch(function(err){
          console.log("Promise rejection error: "+err);
          res.end("<h1>ERROR</h1>")
        })
    }
}
}).listen(3001)

Answer 3

回答by Ken Ingram

This was a situation where I was inserting new records to a child table and needed the prent record key, based only on a name.

在这种情况下，我将新记录插入到子表中，并且仅基于名称需要 prent 记录键。

This was a good example of understanding the asynchronous nature of node.

这是理解节点异步性质的一个很好的例子。

I needed to wrap the all the code affecting the child records inside the call to find the parent record id.

我需要在调用中包装影响子记录的所有代码以找到父记录 ID。

I was approaching this from a sequential (PHP, JAVA) perspective, which was all wrong.

我是从顺序（PHP、JAVA）的角度来解决这个问题的，这完全是错误的。

Answer 4

回答by Epirocks

Easier if you send in a promise to be resolved e.g

如果您发送要解决的承诺，则更容易，例如

function get_info(data, promise){

      var sql = "SELECT a from b where info = data";

      connection.query(sql, function(err, results){
            if (err){ 
              throw err;
            }
            console.log(results[0].objid); // good
            stuff_i_want = results[0].objid;  // Scope is larger than function
            promise.resolve(results[0].objid);
    }
}

This way Node.js will stay fast because it's busy doing other things while your promise is waiting to be resolved

这样 Node.js 将保持快速，因为它正在忙于做其他事情而你的承诺正在等待解决

Answer 5

回答by Vincent Rouillard

I've been working on this goal since few weeks, without any result, and I finally found a way to assign in a variable the result of any mysql query using await/async and promises.

几周以来我一直在努力实现这个目标，但没有任何结果，我终于找到了一种方法，可以使用 await/async 和 promise 将任何 mysql 查询的结果分配给变量。

You don't need to understand promises in order to use it, eh, I don't know how to use promises neither anyway

你不需要理解promise就可以使用它，呃，反正我也不知道如何使用promise

I'm doing it using a Model class for my database like this :

我正在为我的数据库使用模型类，如下所示：

class DB {
    constructor(db) {
        this.db = db;
    }


    async getUsers() {
        let query = "SELECT * FROM asimov_users";
          return this.doQuery(query)
    }

    async getUserById(array) {
      let query = "SELECT * FROM asimov_users WHERE id = ?";
      return this.doQueryParams(query, array);
    }


    // CORE FUNCTIONS DON'T TOUCH
    async doQuery(queryToDo) {
        let pro = new Promise((resolve,reject) => {
            let query = queryToDo;
            this.db.query(query, function (err, result) {
                if (err) throw err; // GESTION D'ERREURS
                resolve(result);
            });
        })
        return pro.then((val) => {
            return val;
        })
    }
    async doQueryParams(queryToDo, array) {
      let pro = new Promise((resolve,reject) => {
        let query = queryToDo;
        this.db.query(query, array, function (err, result) {
            if (err) throw err; // GESTION D'ERREURS
            resolve(result);
        });
      })
      return pro.then((val) => {
        return val;
      })
    }
}

Then, you need to instantiate your class by passing in parameter to constructor the connectionvariable given by mysql. After this, all you need to do is calling one of your class methods with an awaitbefore. With this, you can chain queries without worrying of scopes.

然后，您需要通过将参数传递给构造函数connectionmysql 给出的变量来实例化您的类。在此之后，您需要做的就是使用awaitbefore调用您的类方法之一。有了这个，您可以链接查询而不必担心范围。

Example :

例子：

connection.connect(function(err) {
    if (err) throw err;
    let DBModel = new DB(connection);

    (async function() {
        let oneUser = await DBModel.getUserById([1]);
        let allUsers = await DBModel.getUsers();

        res.render("index.ejs", {oneUser : oneUser, allUsers : allUsers});
    })();

});

Notes :

注意事项：

if you need to do another query, you just have to write a new method in your class and calling it in your code with an awaitinside an async function, just copy/paste a method and modify it
there are two "core functions" in the class, doQueryand doQueryParams, the first one only takes a string as a parameter which basically is your mysql query. The second one is used for parameters in your query, it takes an array of values.
it's relevant to notice that the return value of your methods will always be an array of objects, it means that you'll have to do var[0]if you do a query which returns only one row. In case of multiple rows, just loop on it.

如果你需要做另一个查询，你只需要在你的类中编写一个新方法并在你的代码中使用await内部 an调用它async function，只需复制/粘贴一个方法并修改它
有两个“核心功能”中的类，doQuery并且doQueryParams，第一个只需要一个字符串作为参数，基本上是你的MySQL查询。第二个用于查询中的参数，它需要一个值数组。
需要注意的是，您的方法的返回值将始终是一个对象数组，这意味着var[0]如果您执行仅返回一行的查询，则必须这样做。在多行的情况下，只需循环即可。

node.js 如何使用 Node 从 mysql 正确返回结果？

提问by Ken Ingram

回答by Alex

回答by gtugnolo

回答by Ken Ingram

回答by Epirocks

回答by Vincent Rouillard

相关推荐

最近更新

标签

node.js 如何使用 Node 从 mysql 正确返回结果？

提问by Ken Ingram

回答by Alex

回答by gtugnolo

回答by Ken Ingram

回答by Epirocks

回答by Vincent Rouillard

相关推荐

Ruby-on-rails ActionController::UrlGenerationError, 没有路由匹配

Ruby-on-rails “开发”环境缺少“secret_token”和“secret_key_base”，请在“config/secrets.yml”中设置这些值

Ruby-on-rails 您如何保护 database.yml？

Ruby-on-rails 在 Rails 中添加数组列

相关推荐

最近更新

标签