JavaScript nodejs mysql with queries in a loop - Stack Overflow

admin管理员组

文章数量:1315359

I feel a bit dumbstruck right now. I am fairly new to nodejs and javaScript and can't figure this one out. I guess it is because of the async nature of queries to mysql...

I made an example that shows my problem. I just want to cycle over a number of sql queries and do stuff with the results. for the sake of the example I just print out stuff. I know that I could use a single sql query like this "SELECT id, name FROM player WHERE id IN (1,2,3,4,5)" but this is not possible in the real application I am trying to write.

this is the relevant part of my nodejs app.js

var mysql = require("mysql");
var mysqlPool = mysql.createPool(conf.mysqlArbData);

for (var i = 0; i<5; i++){

    mysqlPool.getConnection(function(err, connection) {

        var detailSql = "SELECT id, name FROM player " +
            "WHERE id = "+i;
        if (err){
            throw err;
        }
        connection.query(detailSql, function(err, detailRows, fields) {
            connection.end();
            console.log("detailSql="+detailSql);
            if (err){
                console.log("can't run query=" + detailSql +"\n Error="+err);
            }
            else{

                console.log(detailRows[0].id + " " +detailRows[0].name);

            }

        });
    });

};

Now the output:

web server listening on port 3000 in development mode
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla

My question is, why do I get only the result for database entry with id=5? What needs to be changed in order to receive each individual result in the callback?

I feel a bit dumbstruck right now. I am fairly new to nodejs and javaScript and can't figure this one out. I guess it is because of the async nature of queries to mysql...

I made an example that shows my problem. I just want to cycle over a number of sql queries and do stuff with the results. for the sake of the example I just print out stuff. I know that I could use a single sql query like this "SELECT id, name FROM player WHERE id IN (1,2,3,4,5)" but this is not possible in the real application I am trying to write.

this is the relevant part of my nodejs app.js

var mysql = require("mysql");
var mysqlPool = mysql.createPool(conf.mysqlArbData);

for (var i = 0; i<5; i++){

    mysqlPool.getConnection(function(err, connection) {

        var detailSql = "SELECT id, name FROM player " +
            "WHERE id = "+i;
        if (err){
            throw err;
        }
        connection.query(detailSql, function(err, detailRows, fields) {
            connection.end();
            console.log("detailSql="+detailSql);
            if (err){
                console.log("can't run query=" + detailSql +"\n Error="+err);
            }
            else{

                console.log(detailRows[0].id + " " +detailRows[0].name);

            }

        });
    });

};

Now the output:

web server listening on port 3000 in development mode
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla

My question is, why do I get only the result for database entry with id=5? What needs to be changed in order to receive each individual result in the callback?

• javascript
• mysql
• node.js

Share Improve this question Follow asked Apr 17, 2013 at 19:14 lukschluksch 11.7k66 gold badges4040 silver badges5454 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 4

The problem is that getConnection is asynchronous and that Javascript doesn't have a block scope, which means that by the time the callback for getConnection is called, the i variable will point to the value of what it had last in the loop (which is 5).

You can use a trick to create a partial function (think of it as a function with the first argument already applied to it) for each turn of the loop, which will pass the current value of i as the first argument of the getConnection callback:

for (var i = 0; i<5; i++) {
  mysqlPool.getConnection(function(i, err, connection) {
    ...
  }.bind(mysqlPool, i));
};

FWIW, your code will open 5 connections (and perform 5 queries) to your database almost instantly (that's how asynchronous I/O works). That's probably not a big issue, but it's something worth realising if that 5 could get higher :)

Also, the for loop will generate [0, 1, 2, 3, 4], whereas in your example query, you write WHERE id IN (1, 2, 3, 4, 5).

For node, you can use let statement. It limit i scope in for loop.

for (let i = 0; i<5; i++) {
  mysqlPool.getConnection(function(err, connection) {
    console.log(i);
  });
};

本文标签： JavaScript nodejs mysql with queries in a loopStack Overflow