MySQL存储过程执行动态创建触发器SQL报语法错误排查

• MySQL的PREPARE预处理语句机制不支持在单条预处理字符串中执行多条以分号分隔的SQL语句。你拼接的@queryString变量里同时包含了DROP TRIGGER和CREATE TRIGGER两条独立语句，预处理解析完第一条DROP TRIGGER后，会认为语句已经结束，后续的CREATE TRIGGER内容就会被判定为非法语法，和你看到的报错位置完全吻合。
• 你把@queryString的拼接结果复制到客户端单独执行能正常运行，是因为普通MySQL客户端会自动按分号拆分多条语句，逐条发送给服务端执行，和预处理的执行逻辑完全不同。
• 存在兼容性隐患：你在存储过程里用||做字符串拼接，MySQL默认模式下||是逻辑或运算符，只有开启PIPES_AS_CONCAT的sql_mode时才会作为字符串拼接符使用，换环境运行很容易出现拼接错误。

• 拆分多语句为独立预处理执行：先单独执行DROP TRIGGER的逻辑，再单独执行CREATE TRIGGER的逻辑，不要把两条语句放在同一个预处理字符串里。
• 把所有||字符串拼接替换为MySQL原生CONCAT()函数，避免sql_mode差异导致的拼接错误。
• 增加触发器存在性判断，避免首次执行时触发器不存在导致DROP TRIGGER报错。

修复后的完整存储过程代码如下：

DROP PROCEDURE IF EXISTS `prcTriggersLogsRefreshFields`;
DELIMITER //
CREATE PROCEDURE `prcTriggersLogsRefreshFields`(
    par_dbName text,
    par_tableName text,
    par_keyField text
)
BEGIN   
    SET @strJsonObj = null;
    SET @change_object = CONCAT(par_dbName,'.',par_tableName);

    SELECT GROUP_CONCAT('\'',COLUMN_NAME, '\',', COLUMN_NAME) 
    FROM INFORMATION_SCHEMA.COLUMNS  
    WHERE TABLE_SCHEMA = par_dbName AND TABLE_NAME = par_tableName 
    INTO @strJsonObj;

    -- 单独执行删除触发器逻辑
    SET @dropTriggerSql = CONCAT('DROP TRIGGER IF EXISTS `', par_dbName, '`.`triggers_after_insert`');
    PREPARE dropStmt FROM @dropTriggerSql;
    EXECUTE dropStmt;
    DEALLOCATE PREPARE dropStmt;

    -- 单独拼接创建触发器语句，作为单条预处理执行
    SET @createTriggerSql = CONCAT(
        'CREATE TRIGGER `triggers_after_insert` AFTER INSERT ON `',par_dbName,'`.`',par_tableName,'` FOR EACH ROW BEGIN
        SELECT JSON_ARRAYAGG(JSON_OBJECT(',@strJsonObj,')) change_obj FROM  `',par_dbName,'`.`',par_tableName,'` WHERE ',par_keyField,'=New.',par_keyField,' INTO @jsonRow; 
        INSERT INTO mylog_db.table_log (`change_id`, `change_date`, `db_name`, `table_name`, `change_object`, `change_event_name`, `previous_content`, `change_content`, `change_user`) VALUES (DEFAULT, NOW(), ''',par_dbName,''',''',par_tableName,''',''',@change_object,''',''insert'', ''{}'', @jsonRow, New.user_created);
        END;'
    );
    
    PREPARE createStmt FROM @createTriggerSql;
    EXECUTE createStmt;
    DEALLOCATE PREPARE createStmt;

END //
DELIMITER ;

调用方式和原来保持一致即可：

CALL prcTriggersLogsRefreshFields('mydb','mytable','myidtable');

内容的提问来源于stack exchange，提问作者Juan Perez