mysql - 在 Node js代码中获取mysql查询的输出文件中的日期

Question

我正在尝试在 sql 中编写包含时间戳的输出文件。:

sql query:SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = 'now';
SET @EXT    = '.csv';
SET @CMD = CONCAT("SELECT * FROM test.extract INTO     OUTFILE'",@FOLDER,@TS,@EXT,"' FIELDS TERMINATED BY ',' LINES TERMINATED BY     '\n';");
PREPARE statement FROM @CMD;
EXECUTE statement;

如果我在下面给出的 Node js 脚本中给出相同的查询，它就会失败。有人可以帮忙吗？

var mysql      = require('mysql');
var connection = mysql.createConnection({
host     : 'localhost',
user     : 'root',
password : 'root',
database : 'test'
});

connection.connect();

var timeInMs = Date.now();
console.log(timeInMs);


connection.query("SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');
SET @FOLDER = 'now';
SET @EXT    = '.csv';
SET @CMD = CONCAT(
"SELECT * FROM test.extract INTO OUTFILE'",@FOLDER,@TS,@EXT,"' FIELDS     TERMINATED BY ',' LINES TERMINATED BY '\n';");

PREPARE statement FROM @CMD;
EXECUTE statement;",function(err, rows, fields) {
if (!err)
  console.log('The solution is: ', rows);
else
  console.log('Error while performing Query.');
});
connection.end();

Answer 1

根据documentation mysql Nodejs 模块:

Support for multiple statements is disabled for security reasons (it allows for SQL injection attacks if values are not properly escaped). To use this feature you have to enable it for your connection:

var connection = mysql.createConnection({multipleStatements: true});

此外，为了使多行正常工作，请将其放在反引号中。以下代码对我有用:

var mysql      = require('mysql');
var connection = mysql.createConnection({
host     : 'localhost',
user     : 'root',
password : 'root',
database : 'test',
multipleStatements: true
});

connection.connect();

var timeInMs = Date.now();
console.log(timeInMs);

connection.query(`SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s'); 
SET @FOLDER = 'now';
SET @EXT  = '.csv';
SET @CMD = CONCAT(
"SELECT * FROM mysql.user INTO OUTFILE'",@FOLDER,@TS,@EXT,"' FIELDS     TERMINATED BY ',' LINES TERMINATED BY '\n';");
PREPARE statement FROM @CMD;
EXECUTE statement;`    
,function(err, rows, fields) {
if (!err)
  console.log('The solution is: ', rows);
else
  console.log('Error while performing Query.', err);
});
connection.end();