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

问题描述

我正在通过 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:\Codes\Angular\app\Server\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Query.ErrorPacket (C:\Codes\Angular\app\Server\node_modules\mysql\lib\protocol\sequences\Query.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 $$\r\n" +
        'CREATE PROCEDURE create_picture_table_every_hour()\r\n' +
        'BEGIN\r\n' +
        "SET @ho' at line 1",
      sqlState: '42000',
      index: 0,
      sql: 'DELIMITER $$\r\n' +
        'CREATE PROCEDURE create_picture_table_every_hour()\r\n' +
        'BEGIN\r\n' +
        'SET @hourStr = CONCAT( CONVERT(REPLACE(CURDATE(),"-",""),CHAR(8)),CONVERT( HOUR(DATE_ADD(NOW(), INTERVAL 1 HOUR)) ,CHAR(2)));\r\n' +
        'SET @TableCreateSQL = CONCAT("CREATE TABLE picture_ttt_" ,@hourStr,\r\n' +
        '\t\t\t"\r\n' +
        '\t\t\t(\r\n' +
        '\t\t\t`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,\r\n' +
        '\t\t\t`capture_time` int(10) unsigned NOT NULL,\r\n' +
        '\t\t\t`binary_data` longblob NOT NULL,\r\n' +
        '\t\t\t`parent_ID` bigint(20) unsigned NOT NULL,\r\n' +
        '\t\t\tKEY `ID` (`ID`)\r\n' +
        '\t\t\t)\r\n' +
        '\t\t\tENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;\r\n' +
        '\t\t\t"\r\n' +
        ');\r\n' +
        'PREPARE stmt FROM @TableCreateSQL;\r\n' +
        'EXECUTE stmt;\r\n' +
        'END \r\n' +
        '$$\r\n' +
        'DELIMITER ;\r\n'

}

标签： mysqlnode.jsnpm

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

问题描述

解决方案

推荐阅读