Node.js 与 MySQL 的同步查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32850045/
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
Node.js Synchronous queries with MySQL
提问by Alex Beebe
I'm working on creating a user registration system for a website that I am working on but I am running into a few issues.
我正在为我正在处理的网站创建用户注册系统，但我遇到了一些问题。
I'm trying to stay away from having to nest callbacks because it gets kind of messy, What I need help with is finding if there is a way to create synchronous queries with node-mysql
我试图避免嵌套回调，因为它有点混乱，我需要帮助的是找到是否有办法使用 node-mysql 创建同步查询
Here's what I'm trying to achieve.
这就是我想要实现的目标。
connection.query("select 1 as email from users where email = " + connection.escape(email), function(err, rows, fields) {
if(err) {
var error = {
error_message: err.code,
error_number: err.errno
};
return res.send(error);
}
if(rows.length > 0) {
var error = {
message: 'Email Address is Taken',
code: 2
};
return res.send(error);
}
});
connection.query("insert into users (email, password) values ("+connection.escape(email)+", "+connection.escape(hash)+")", function(err, rows, fields) {
if(err) {
var error = {
error_message: err.code,
error_number: err.errno
};
return res.send(error);
}
});
My goal is to have the first query run and if that returns a row then to not execute the second query but if the first query returns 0 rows then continue and run the second query.
我的目标是运行第一个查询，如果返回一行，则不执行第二个查询，但如果第一个查询返回 0 行，则继续并运行第二个查询。
I know I can nest the second query inside the first query and put if in an else but that's what I don't want to do because while I have those two queries I also have it set u to use bcrypt to encrypt the password which would have to be nested as well.
我知道我可以将第二个查询嵌套在第一个查询中并将 if 放在 else 中，但这是我不想做的，因为虽然我有这两个查询，但我也将它设置为使用 bcrypt 来加密密码，这将也必须嵌套。
Is there a way to write it so that I don't need to nest the two queries or is nesting them going to be my only option?
有没有办法编写它，这样我就不需要嵌套两个查询，或者嵌套它们将是我唯一的选择？
回答by will.I4M
You could simply use a module for node that provide synchronous functions. Here you'll find a module that provide sync/async functions to deal with mysql.
您可以简单地为提供同步功能的节点使用模块。在这里，您将找到一个提供同步/异步功能来处理 mysql 的模块。
https://github.com/Will-I4M/node-mysql-libmysqlclient
https://github.com/Will-I4M/node-mysql-libmysqlclient
Here is how you could use it in order to execute a synchronous query :
以下是您如何使用它来执行同步查询：
var config = require("./config.json") ;
var mysql = require('mysql-libmysqlclient') ;
var client = mysql.createConnectionSync(config.host, config.user, config.password, config.database) ;
var query = "SELECT * FROM Users ;" ;
var handle = client.querySync(query) ;
var results = handle.fetchAllSync() ;
console.log(JSON.stringify(results)) ;
回答by paulsm4
As jfriend00 said above, if you're going to develop in node.js, then you MUST become comfortable with writing async code.
正如 jfriend00 上面所说，如果您打算在 node.js 中进行开发，那么您必须熟悉编写异步代码。
"chained promises" is probably your best bet:
“连锁承诺”可能是你最好的选择：
ADDENDUM:
附录：
This tutorial illustrates promise chaining with node.js SQL queries. It also discusses how you can use Qand/or Stepto simplify your code:
本教程说明了使用 node.js SQL 查询进行 Promise 链接。它还讨论了如何使用Q和/或Step来简化代码：
回答by Zoltán Hajdú
There could be conditions when you need sync queries (or at least for readability or simplicity). I do not agree with that everything have to be done in the async way at node.js.
当您需要同步查询（或至少为了可读性或简单性）时，可能存在一些条件。我不同意在 node.js 中一切都必须以异步方式完成。
I have tested a lot of available solutions and ended up with the "sync-mysql" module (https://github.com/ForbesLindesay/sync-mysql).
我测试了很多可用的解决方案，最终得到了“sync-mysql”模块（https://github.com/ForbesLindesay/sync-mysql）。
Easy to install and use, but not that good in performance (especially if you have to do a lot of sub-queries).
易于安装和使用，但性能不是很好（尤其是当您必须执行大量子查询时）。
回答by ThereBeDragons
I know I am late to this party but I feel I can help people like me that needed a way to use MySQL in a synchronous way.
我知道我参加这个聚会迟到了，但我觉得我可以帮助像我这样需要以同步方式使用 MySQL 的人。
Answer is here.
答案在这里。
Oh and I had to add a pool.end(); after my query code to close the connection and stop the infinite wait loop. See here.
哦，我不得不添加一个 pool.end(); 在我的查询代码关闭连接并停止无限等待循环之后。见这里。
回答by user1018645
For most things I code in node.js, I like asynchronous code. However, I completely understand that asynchronous code is extremely and dangerously incompatible with the need to write and maintain business logic. I've used a variety of alternative methods. The modules to make things synchronous still leave you with data scoping issues that complicate things. Promises worked best for me. Using that approach, I found myself practically writing an interpreter for a new language on top of JavaScript. I may seem absurd but the most practical and safest method for me ended up being to use the shelljs module and the mysql shell client. It's not great execution performance but it makes for much better developer performance and keeps business logic clear and orderly, as is crucial for business logic. Here's snippet of code to give an example of some of what I created:
对于我在 node.js 中编码的大多数事情，我喜欢异步代码。但是，我完全理解异步代码与编写和维护业务逻辑的需要极其不兼容。我使用了多种替代方法。使事情同步的模块仍然给您留下使事情复杂化的数据范围问题。Promise 对我来说效果最好。使用这种方法，我发现自己实际上是在 JavaScript 之上为一种新语言编写解释器。我可能看起来很荒谬，但对我来说最实用和最安全的方法最终是使用 shelljs 模块和 mysql shell 客户端。这不是很好的执行性能，但它可以提高开发人员的性能并保持业务逻辑清晰有序，这对业务逻辑至关重要。这里'
var shell = require('shelljs');
module.exports = {
user: '',
password: '',
runSql: function (sql) {
var command = "echo '" + sql.replace(/'/g, "'\''") + "' | mysql -u" + this.user.replace(/'/g, "'\''") + " -p'" + this.password.replace(/'/g, "'\''") + "'";
var raw = shell.exec(command, {silent: true}).stdout;
//console.log( 'BASH -> MySQL YIELD: "' + raw + '"' );
if (raw.substr(0, 5) === 'ERROR') {
console.log('ERROR Resulting from: ' + sql + '\n' + raw);
return [];
}
var rows = raw.split('\n');
var names = [];
for (var r = 0; r < rows.length; r += 1) {
columns = rows[r].split('\t');
// Capture column names
if (r === 0) {
names = columns;
continue;
}
// Reformat row into named valued
var fields = {};
for (var c = 0; c < columns.length; c += 1) {
fields[names[c]] = columns[c];
}
rows[r] = fields;
}
// Eliminate extraneous first and last rows
rows.splice(0, 1);
rows.splice(rows.length - 1, 1);
return rows;
},
}
