node-mysql 一个查询中的多条语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23266854/
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-mysql multiple statements in one query
提问by L.rp
I'm using nodejs 10.26+ express 3.5+ node-mysql 2.1.1+
MySQL-Server Version: 5.6.16.
我正在使用nodejs 10.26+ express 3.5+ node-mysql 2.1.1+
MySQL-Server Version: 5.6.16。
I got 4 DELETE's and want only 1 Database Request, so i connected the DELETE commands with a ";"... but it fails always.
我有 4 个 DELETE 并且只想要 1 个数据库请求，所以我用“;”连接了 DELETE 命令……但它总是失败。
var sql_string = "DELETE FROM user_tables WHERE name = 'Testbase';";
sql_string += "DELETE FROM user_tables_structure WHERE parent_table_name = 'Testbase';";
sql_string += "DELETE FROM user_tables_rules WHERE parent_table_name = 'Testbase';";
sql_string += "DELETE FROM user_tables_columns WHERE parent_table_name = 'Testbase';";
connection.query(sql_string, function(err, rows, fields) {
if (err) throw err;
res.send('true');
});
It throws this error:
它抛出这个错误：
Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM user_tables_structure WHERE parent_table_name = 'Testbase';DELETE FR' at line 1
But if i paste this SQL in PhpMyAdmin it is always successful...
但是如果我将这个 SQL 粘贴到 PhpMyAdmin 中，它总是成功的...
If i write it in single query's its succeed, too.
如果我用单个查询写它，它也成功了。
connection.query("DELETE FROM user_tables WHERE name = 'Testbase'", function(err, rows, fields) {
if (err) throw err;
connection.query("DELETE FROM user_tables_structure WHERE parent_table_name = 'Testbase'", function(err, rows, fields) {
if (err) throw err;
connection.query("DELETE FROM user_tables_rules WHERE parent_table_name = 'Testbase'", function(err, rows, fields) {
if (err) throw err;
connection.query("DELETE FROM user_tables_columns WHERE parent_table_name = 'Testbase'", function(err, rows, fields) {
if (err) throw err;
res.send('true');
});
});
});
});
Thanks for help!
感谢帮助！
回答by majidarif
I guess you are using node-mysql. (but should also work for node-mysql2)
我猜你正在使用node-mysql。（但也应该适用于node-mysql2）
The docssays:
该文件说：
Support for multiple statements is disabled for security reasons (it allows for SQL injection attacks if values are not properly escaped).
出于安全原因，禁用了对多个语句的支持（如果值未正确转义，则允许 SQL 注入攻击）。
Multiple statement queries
多语句查询
To use this feature you have to enable it for your connection:
要使用此功能，您必须为您的连接启用它：
var connection = mysql.createConnection({multipleStatements: true});
Once enabled, you can execute queries with multiple statements by separating each statement with a semi-colon ;. Result will be an array for each statement.
启用后，您可以通过用分号分隔每个语句来执行包含多个语句的查询;。结果将是每个语句的数组。
Example
例子
connection.query('SELECT ?; SELECT ?', [1, 2], function(err, results) {
if (err) throw err;
// `results` is an array with one element for every statement in the query:
console.log(results[0]); // [{1: 1}]
console.log(results[1]); // [{2: 2}]
});
So if you have enabled the multipleStatements, your first code should work.
因此，如果您启用了multipleStatements，您的第一个代码应该可以工作。
