mysql-npm在执行动态生成的sql脚本时出现语法错误，但脚本在HeidiSQL中正常工作

Question

我正在通过sql模板生成一个存储过程和事件。我生成的脚本在HeidiSQL中正常工作，但在我的nodejs代码中不正常。

我不知道这里出了什么问题，正在寻求帮助，提前谢谢。

以下是我在后台函数中生成的脚本。

DELIMITER $$
CREATE PROCEDURE create_picture_table_every_hour()
BEGIN
SET @hourStr = CONCAT( CONVERT(REPLACE(CURDATE(),"-",""),CHAR(8)),CONVERT( HOUR(DATE_ADD(NOW(), INTERVAL 1 HOUR)) ,CHAR(2)));
SET @TableCreateSQL = CONCAT("CREATE TABLE picture_ttt_" ,@hourStr,
"
(
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`capture_time` int(10) unsigned NOT NULL,
`binary_data` longblob NOT NULL,
`parent_ID` bigint(20) unsigned NOT NULL,
KEY `ID` (`ID`)
)
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
"
);
PREPARE stmt FROM @TableCreateSQL;
EXECUTE stmt;
END
$$
DELIMITER ;
DELIMITER $$
SET GLOBAL event_scheduler = 1;
CREATE EVENT event_create_table_every_hour
ON SCHEDULE EVERY 1 HOUR
STARTS NOW()
ON  COMPLETION  PRESERVE
ENABLE
DO
BEGIN
CALL create_picture_table_every_hour();
END
$$
DELIMITER ;

错误消息从这里开始。

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 'DELIMITER $$
CREATE PROCEDURE create_picture_table_every_hour()
BEGIN
SET @ho' at line 1
at Query.Sequence._packetToError (C:CodesAngularappServernode_modulesmysqllibprotocolsequencesSequence.js:47:14)
at Query.ErrorPacket (C:CodesAngularappServernode_modulesmysqllibprotocolsequencesQuery.js:79:18)
at Protocol._parsePacket {
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage: "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 

'DELIMITER $$rn" +
'CREATE PROCEDURE create_picture_table_every_hour()rn' +
'BEGINrn' +
"SET @ho' at line 1",
sqlState: '42000',
index: 0,
sql: 'DELIMITER $$rn' +
'CREATE PROCEDURE create_picture_table_every_hour()rn' +
'BEGINrn' +
'SET @hourStr = CONCAT( CONVERT(REPLACE(CURDATE(),"-",""),CHAR(8)),CONVERT( HOUR(DATE_ADD(NOW(), INTERVAL 1 HOUR)) ,CHAR(2)));rn' +
'SET @TableCreateSQL = CONCAT("CREATE TABLE picture_ttt_" ,@hourStr,rn' +
'ttt"rn' +
'ttt(rn' +
'ttt`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,rn' +
'ttt`capture_time` int(10) unsigned NOT NULL,rn' +
'ttt`binary_data` longblob NOT NULL,rn' +
'ttt`parent_ID` bigint(20) unsigned NOT NULL,rn' +
'tttKEY `ID` (`ID`)rn' +
'ttt)rn' +
'tttENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;rn' +
'ttt"rn' +
');rn' +
'PREPARE stmt FROM @TableCreateSQL;rn' +
'EXECUTE stmt;rn' +
'END rn' +
'$$rn' +
'DELIMITER ;rn'
}

Answer 1

我自己找到了答案，在脚本文件中，删除DELIMITER。不太确定为什么我需要在HeidiSQL Query窗口中运行脚本来创建存储过程时使用它们。MySql新手，我已经使用MSSQL很长时间了。

我删除了以下内容。

分隔符$$

$$分隔符；