node-mysql 一个查询中的多条语句

2021-11-20 00:00:00 node.js mysql

我使用的是 nodejs 10.26 + express 3.5 + node-mysql 2.1.1 +MySQL-Server 版本: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.

相关文章