MySQL 存储过程实现字符串split

• 目标 将a表中类似于1,2,3的字符串, 依据逗号分割成多条, 存入到b表中

源表

目标表

创建及调用存储过程

DELIMITER $$

CREATE PROCEDURE SplitAndInsertLessons()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;  -- 标记游标是否已经遍历完所有记录
    DECLARE studentID INT;           -- 当前学生的 ID
    DECLARE lessonString VARCHAR(255); -- 当前学生的课程字符串
    DECLARE lessonValue VARCHAR(10);   -- 分割出来的单个课程 ID
    
    -- 声明游标，用于遍历 student 表中的 id 和 lessons 字段
    DECLARE cur CURSOR FOR SELECT id, lessons FROM student;
    
    -- 设置异常处理程序，当游标没有找到更多记录时，将 done 设置为 TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标开始遍历数据
    OPEN cur;

    -- 循环读取数据
    read_loop: LOOP
        -- 将游标中的数据提取到变量中
        FETCH cur INTO studentID, lessonString;
        
        -- 如果 done 为 TRUE，则退出循环
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 假设 lessons 字符串不为空且只包含由逗号分隔的数字
        WHILE CHAR_LENGTH(TRIM(lessonString)) > 0 DO
            -- 获取第一个逗号之前的部分作为 lessonValue
            SET lessonValue = SUBSTRING_INDEX(lessonString, ',', 1);
            
            -- 将课程 ID 插入到 student_lesson 表中
            INSERT IGNORE INTO student_lesson (student_id, lesson_id) VALUES (studentID, CAST(lessonValue AS SIGNED));
            
            -- 更新 lessonString 去掉已处理的部分
            SET lessonString = INSERT(lessonString, 1, CHAR_LENGTH(lessonValue) + IF(lessonString REGEXP CONCAT('^', lessonValue, ','), 1, 0), '');
        END WHILE;
    END LOOP;

    -- 关闭游标结束遍历
    CLOSE cur;
END$$

DELIMITER ;

-- 调用存储过程
CALL SplitAndInsertLessons();