MySQL 5.7.12创建存储过程时出现语法错误求助

问题场景 #

使用MySQL 5.7.12版本创建名为SaveMultipleResumeActivityLog的存储过程时触发语法错误，原代码意图通过CSV格式的_resumeDIDs参数批量插入数据到ResumeActivityLog表，错误源于使用了MySQL 5.7不支持的特性。

原代码 #

-- 创建表
CREATE TABLE ResumeActivityLog ( 
    Email VARCHAR(255),
    ProfileID int(11),
    ExternalUserDID VARCHAR(255),
    EventType VARCHAR(255),
    ResumeDID VARCHAR(255), 
    EventText VARCHAR(255),
    CreatedOn DATETIME
);

-- 创建存储过程（报错代码）
CREATE PROCEDURE `SaveMultipleResumeActivityLog`(
    _resumeDIDs Text
)
    INSERT INTO `ResumeActivityLog` (`Email`,`ProfileID`,`ExternalUserDID`, `EventType`, `ResumeDID`, `EventText`, `CreatedOn`)
    SELECT "", 0, "", "resume_anon_delete", ResumeDID,  "success", CURRENT_TIMESTAMP
    FROM JSON_TABLE(CONCAT('["', REPLACE(_resumeDIDs, ',', '","'), '"]'),
                    '$[*]' COLUMNS (ResumeDID VARCHAR(255) PATH '$')) jsontable;

-- 调用存储过程
CALL SaveMultipleResumeActivityLog('R2W5D16T8BRYWZKRF28,RHL1B05ZTQ546JKF0RG,RD95SY747Z89M7GN8TP,R2N54T767CZTBY79N00,RHT2GR6K0FTYTPFG985,RDH4KV5Z3WX7SV2850N');

-- 查询结果
SELECT * FROM ResumeActivityLog;

错误原因 #

JSON_TABLE函数是MySQL 8.0及以上版本才引入的特性，MySQL 5.7完全不支持该函数，因此直接使用会触发语法错误。

适配MySQL 5.7的解决方案 #

由于MySQL 5.7既不支持JSON_TABLE也不支持递归CTE，可通过自定义字符串拆分函数实现CSV参数的拆分，再完成批量插入：

步骤1：创建字符串拆分函数 #

DELIMITER //
CREATE FUNCTION SplitString(str TEXT, delim VARCHAR(12), pos INT) 
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
           LENGTH(SUBSTRING_INDEX(str, delim, pos -1)) + 1),
           delim, '');
END //
DELIMITER ;

步骤2：创建适配版存储过程 #

DELIMITER //
CREATE PROCEDURE `SaveMultipleResumeActivityLog`(
    _resumeDIDs Text
)
BEGIN
    -- 定义变量
    DECLARE total INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    DECLARE currentResumeDID VARCHAR(255);
    
    -- 计算CSV中的元素数量
    SET total = LENGTH(_resumeDIDs) - LENGTH(REPLACE(_resumeDIDs, ',', '')) + 1;
    
    -- 循环拆分并插入数据
    WHILE i <= total DO
        SET currentResumeDID = SplitString(_resumeDIDs, ',', i);
        INSERT INTO `ResumeActivityLog` 
        (`Email`,`ProfileID`,`ExternalUserDID`, `EventType`, `ResumeDID`, `EventText`, `CreatedOn`)
        VALUES ("", 0, "", "resume_anon_delete", currentResumeDID, "success", CURRENT_TIMESTAMP);
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

步骤3：调用存储过程并验证 #

-- 调用存储过程
CALL SaveMultipleResumeActivityLog('R2W5D16T8BRYWZKRF28,RHL1B05ZTQ546JKF0RG,RD95SY747Z89M7GN8TP,R2N54T767CZTBY79N00,RHT2GR6K0FTYTPFG985,RDH4KV5Z3WX7SV2850N');

-- 查询插入结果
SELECT * FROM ResumeActivityLog;

补充说明 #

如果业务场景允许，升级到MySQL 8.0可直接使用原代码中的JSON_TABLE方案，代码会更简洁高效。

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