以节点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;

如果我在下面给出的节点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

根据文档的mysql nodejs模块：

出于安全原因，禁用了对多个语句的支持(如果值没有正确转义，则允许SQL注入攻击)。若要使用此功能，必须为连接启用该功能： 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();