Javascript 使用 Node.js 处理多个 MySQL 查询的方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6622746/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Approach to multiple MySQL queries with Node.js
提问by luso
I'm a newbie both on event/callback style programming and NodeJS. I'm trying to implement a little http server which serves ddbb data using node-mysql module.
我是事件/回调风格编程和 NodeJS 的新手。我正在尝试实现一个小的 http 服务器，它使用 node-mysql 模块提供 ddbb 数据。
My problems comes from queries structuration. Since there are often queries that require results from previous queries to run, I am not able to run all them simultaneously (asynchronously) and I am forced to wait some results.
我的问题来自查询结构。由于经常有查询需要运行先前查询的结果，因此我无法同时（异步）运行所有这些查询，我不得不等待一些结果。
My first approach was to run all the non-dependant queries at the same time and then loop until all of them have set a flag up saying I'm done so I can continue with the dependant (synchronized) ones, but I don't know if this is the correct approach.
我的第一种方法是同时运行所有非依赖查询，然后循环，直到所有查询都设置了一个标志，说我完成了，这样我就可以继续使用依赖（同步）查询，但我没有知道这是否是正确的方法。
Something like this:
像这样的东西：
function x(){
var result_for_asynch_query_1 = null
var result_for_asynch_query_2 = null
mainLoop(){
// call non-dependant query 1
// call non-dependant query 2
// loop until vars are != null
// continue with queries that require data from the first ones
}
}
//for each browser request
httpServer{
call_to_x();
}.listen();
This way I can save some time in the final result since I don't wait all responses in a serial way but just to the longest one.
这样我可以在最终结果中节省一些时间，因为我不会以串行方式等待所有响应，而是等待最长的响应。
Is there a common way to do this? Any design pattern I'm not following?
有没有一种通用的方法来做到这一点？我没有遵循任何设计模式？
采纳答案by Andrey Sidorov
try to think other way (there is good introduction on async flow howtonode.org)
尝试以其他方式思考（异步流howtonode.org上有很好的介绍）
var db = get_link_or_pool();
do_queries( callback ) {
db.query(sql1, function(err, res1) {
if (err) {
callback(err);
return;
}
// use res1 ...
db.query(sql2, function(err, res2) {
if (err) {
callback(err);
return;
}
callback(null, res2); // think 'return'
}
});
}
request_handler(req) {
do_queries( function(err, result) {
if(err)
report_error(err);
else
write_result(req, result);
});
}
回答by programaths
One should avoid the pyramid of doom:
人们应该避免厄运金字塔：
var express = require('express');
var Q = require('Q');
var app = express();
app.get('/',function(req,res){
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : ''
});
connection.connect();
function doQuery1(){
var defered = Q.defer();
connection.query('SELECT 1 AS solution',defered.makeNodeResolver());
return defered.promise;
}
function doQuery2(){
var defered = Q.defer();
connection.query('SELECT 2 AS solution',defered.makeNodeResolver());
return defered.promise;
}
Q.all([doQuery1(),doQuery2()]).then(function(results){
res.send(JSON.stringify(results[0][0][0].solution+results[1][0][0].solution));
// Hint : your third query would go here
});
connection.end();
});
app.listen(80);
console.log('Listening on port 80');
This sample show a result which depend of 2 independent computed values. Each of these values a queried in doQuery1 and doQuery2. They are executed in sequence, but asynchronously.
此示例显示了取决于 2 个独立计算值的结果。在 doQuery1 和 doQuery2 中查询这些值中的每一个。它们按顺序执行，但异步执行。
Next you can see Q.all(...which basically call the "then" callback on success. Within that callback, the calculation is done.
接下来你可以看到Q.all(...它基本上在成功时调用“then”回调。在该回调中，计算完成。
Using promises (details : Github Q: promise for Javascriptand wikipedia) permit to make your code cleaner, separate computation and handling of results and move things arround.
使用承诺（详细信息：Github Q：Javascript和维基百科的承诺）允许使您的代码更清晰，将结果的计算和处理分开并移动周围的东西。
Look at how easy it would be to add "doQuery3" as prerequisit for your calculation !
看看添加“doQuery3”作为计算的先决条件是多么容易！
And bellow the "package.json" bellonging to the sample code:
然后是示例代码的“package.json”：
{
"name": "hello-world",
"description": "hello world test app",
"version": "0.0.1",
"private": true,
"dependencies": {
"express": "3.2.0",
"q": "0.9.3",
"mysql":"2.0.0-alpha7"
}
}
回答by Hafiz Arslan
Another solution is to concatenate all statements, ending each with a semicolon. For example, to select from multiple tables you could use this query:
另一种解决方案是连接所有语句，每个语句以分号结尾。例如，要从多个表中进行选择，您可以使用以下查询：
var sql = 'select * from user; select * from admin;'
var sql = 'select * from user; select * from admin;'
Then, you can use only one connection to execute the multiple statements:
然后，您可以只使用一个连接来执行多条语句：
var connection = mysql.createConnection({multipleStatements: true})
connection.query(sql)
var connection = mysql.createConnection({multipleStatements: true})
connection.query(sql)
Note: Multiple statements is disabled by default to prevent SQL injection. Be sure to properly escape all values (see docs).
注意：默认情况下禁用多条语句以防止 SQL 注入。确保正确转义所有值（参见文档）。
回答by David
I found the below very helpful in getting over this problem:
我发现以下内容对解决此问题非常有帮助：
Taken from http://book.mixu.net/node/ch7.html- Lots of other great examples here!!
取自http://book.mixu.net/node/ch7.html- 这里有很多其他很棒的例子！！
function async(arg, callback) {
console.log('do something with \''+arg+'\', return 1 sec later');
//replace setTimeout with database query
setTimeout(function() { callback(arg * 2); }, 1000);
}
// Final task (return data / perform further operations)
function final() { console.log('Done', results); }
// A simple async series:
var items = [ 1, 2, 3, 4, 5, 6 ];
var results = [];
function series(item) {
if(item) {
async( item, function(result) {
results.push(result);
return series(items.shift());
});
} else {
return final();
}
}
series(items.shift());
"Take a set of items and call the series control flow function with the first item. The series launches one async() operation, and passes a callback to it. The callback pushes the result into the results array and then calls series with the next item in the items array. When the items array is empty, we call the final() function." (from http://book.mixu.net/node/ch7.html)
"取一组项目并用第一个项目调用系列控制流函数。该系列启动一个 async() 操作，并将回调传递给它。回调将结果推送到结果数组中，然后使用下一个调用系列items 数组中的 item。当 items 数组为空时，我们调用 final() 函数。” （来自http://book.mixu.net/node/ch7.html）
