MySQL存储过程中拆分字符串批量插入ResumeActivityLog表

我有一个包含6个ResumeDID的字符串resumeids：

var resumeids = "R2W5D16T8BRYWZKRF28,RHL1B05ZTQ546JKF0RG,RD95SY747Z89M7GN8TP,R2N54T767CZTBY79N00,RHT2GR6K0FTYTPFG985,RDH4KV5Z3WX7SV2850N";

希望将每个ResumeDID单独插入ResumeActivityLog表，生成6条独立记录。我已创建存储过程SaveMultipleResumeActivityLog，调用方式为：SaveMultipleResumeActivityLog(resumeids, 6);

存储过程代码如下：

CREATE  PROCEDURE `SaveMultipleResumeActivityLog`(
    _resumeDIDs           Text,
    _totalRecords          int(11),
)
BEGIN
    DECLARE i INT  DEFAULT 0;
    
    WHILE i < _totalRecords DO 
    INSERT INTO `ResumeActivityLog`
    ( `ResumeDID`, `CreatedOn`)
    VALUES(resumeDID, NOW());
    SET i =i +1;
    ENDWHILE;
END

请问如何在WHILE循环的每次迭代中拆分该字符串，获取单个ResumeDID以完成批量插入？

方法1：循环内用SUBSTRING_INDEX拆分字符串 #

利用MySQL的SUBSTRING_INDEX函数按逗号分隔截取单个ResumeDID，修改后的存储过程如下：

CREATE  PROCEDURE `SaveMultipleResumeActivityLog`(
    _resumeDIDs           Text,
    _totalRecords          int(11)
)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE currentResumeDID VARCHAR(50); -- 根据实际ResumeDID长度调整
    
    WHILE i < _totalRecords DO 
        -- 提取当前循环对应的ResumeDID
        SET currentResumeDID = SUBSTRING_INDEX(SUBSTRING_INDEX(_resumeDIDs, ',', i+1), ',', -1);
        
        INSERT INTO `ResumeActivityLog`
        ( `ResumeDID`, `CreatedOn`)
        VALUES(currentResumeDID, NOW());
        
        SET i = i + 1;
    END WHILE;
END

逻辑说明 #

• SUBSTRING_INDEX(_resumeDIDs, ',', i+1)：从字符串开头截取到第i+1个逗号前的内容
• 嵌套SUBSTRING_INDEX(..., ',', -1)：从截取结果中取最后一段，即当前循环对应的单个ResumeDID

方法2：临时表拆分+批量插入（更高效） #

如果后续需要处理大量ResumeDID，循环单条插入效率较低，可先拆分字符串到临时表，再批量插入目标表：

CREATE  PROCEDURE `SaveMultipleResumeActivityLog`(
    _resumeDIDs           Text
)
BEGIN
    -- 创建临时表存储拆分后的ResumeDID
    CREATE TEMPORARY TABLE IF NOT EXISTS TempResumeDIDs (
        ResumeDID VARCHAR(50)
    );
    
    -- 循环拆分字符串并插入临时表
    WHILE LOCATE(',', _resumeDIDs) > 0 DO
        SET @current = SUBSTRING_INDEX(_resumeDIDs, ',', 1);
        INSERT INTO TempResumeDIDs VALUES(@current);
        SET _resumeDIDs = SUBSTRING(_resumeDIDs, LOCATE(',', _resumeDIDs) + 1);
    END WHILE;
    -- 插入最后一个无逗号分隔的ResumeDID
    INSERT INTO TempResumeDIDs VALUES(_resumeDIDs);
    
    -- 批量插入到目标表
    INSERT INTO `ResumeActivityLog`(`ResumeDID`, `CreatedOn`)
    SELECT ResumeDID, NOW() FROM TempResumeDIDs;
    
    -- 清理临时表
    DROP TEMPORARY TABLE TempResumeDIDs;
END

优势 #

• 无需传入_totalRecords参数，自动适配任意数量的ResumeDID
• 批量插入比循环单条插入性能更优，适合数据量较大的场景

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