node-mysql 一次查询多条语句

本教程将介绍node-mysql 一次查询多条语句的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

node-mysql 一次查询多条语句 教程 第1张

问题描述

I'm using 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.

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...

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!

解决方案

I guess you are using node-mysql. ()

The docs says:

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.

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.

好了关于node-mysql 一次查询多条语句的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。