一个查询中的node-mysql多条语句 [英] node-mysql multiple statements in one query
问题描述
我正在使用nodejs 10.26
+ express 3.5
+ node-mysql 2.1.1
+
MySQL-Server Version: 5.6.16
.
I'm using nodejs 10.26
+ express 3.5
+ node-mysql 2.1.1
+
MySQL-Server Version: 5.6.16
.
我有4个DELETE,并且只需要1个数据库请求,因此我将DELETE命令与;" ...连接起来,但是它总是失败.
I got 4 DELETE's and want only 1 Database Request, so i connected the DELETE commands with a ";"... but it fails always.
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');
});
它抛出此错误:
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
但是,如果我将此SQL粘贴到PhpMyAdmin中,它将始终成功...
But if i paste this SQL in PhpMyAdmin it is always successful...
如果我在单个查询中写它也是成功的.
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');
});
});
});
});
感谢帮助!
推荐答案
我猜您正在使用 node-mysql . (,但也应适用于 node-mysql2 )
I guess you are using node-mysql. (but should also work for node-mysql2)
文档说:
出于安全原因禁用了对多条语句的支持(它 如果值未正确转义,则允许进行SQL注入攻击.
Support for multiple statements is disabled for security reasons (it allows for SQL injection attacks if values are not properly escaped).
多个语句查询
要使用此功能,必须将其启用以进行连接:
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.
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}]
});
因此,如果您启用了multipleStatements
,则您的第一个代码应该可以使用.
So if you have enabled the multipleStatements
, your first code should work.
这篇关于一个查询中的node-mysql多条语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!