MySQL迁移场景下星号分隔字符串动态拆分为独立列的实现咨询

业务需求 #

数据迁移任务中需要将以*为分隔符的字符串拆分为独立列存储。例如输入字符串Case Western Reserve University* University of Kansasi*Middle East Tech，需要输出Uni1、Uni2、Uni3三个独立字段，分别对应拆分后的大学名称。
由于待处理字符串包含的分隔值数量不固定，要求查询逻辑具备动态适配能力。
当前初步验证拆分逻辑的测试语句如下：

SET @test = "Case Western Reserve University* University of Kansasi* Middle East Technical University";
SELECT SPLIT_STR (@test, '*',3) as third, SPLIT_STR (@test, '*',2) as second;

现有实现的缺陷 #

当前开发的初始版本存储过程存在两类核心问题：

• 输出不符合预期：所有拆分值被拼接在同一个表单元格中，无法为不同拆分值设置val1/val2这类独立列别名实现分栏输出
• 逻辑冗余无效：循环内固定调用SPLIT_STR取第1个位置的拆分值并拼接结果，没有利用循环变量定位不同的拆分位置，遍历逻辑完全失效

现有待优化的初始存储过程代码：

DELIMITER //
CREATE PROCEDURE SplitValuesIntoColumns ( delim_str VARCHAR(1000) )
BEGIN
   DECLARE i INT;
   SET i = 0;
   WHILE (i <= 2) DO
    SET i = i + 1;
        SET @rval =  concat(@rval,SPLIT_STR (delim_str, '*', 1));  
   END WHILE;
   SELECT @rval;
END; //
DELIMITER ;

优化实现方案 #

修正思路 #

1. 先计算输入字符串最多可拆分的段数，替代原硬编码的循环上限，实现对不固定分隔值数量的适配
2. 循环时根据循环变量定位对应位置的拆分值，不再固定取第1个分段
3. 动态拼接SQL语句，将每个拆分值映射为带指定别名的独立查询列，通过预处理SQL执行输出分栏结果，替代原单变量拼接的输出方式

修正后的参考实现 #

DELIMITER //
CREATE PROCEDURE SplitValuesIntoColumns ( IN delim_str VARCHAR(1000) )
BEGIN
    -- 计算最大拆分数量：原字符串长度 - 去除所有*后的长度 + 1
    DECLARE max_split_cnt INT;
    SET max_split_cnt = CHAR_LENGTH(delim_str) - CHAR_LENGTH(REPLACE(delim_str, '*', '')) + 1;
    SET @loop_idx = 1;
    SET @col_list = '';
    -- 动态拼接每个拆分值的查询列
    WHILE @loop_idx <= max_split_cnt DO
        IF @loop_idx > 1 THEN
            SET @col_list = CONCAT(@col_list, ',');
        END IF;
        -- 列别名与业务要求的Uni1、Uni2规则对齐
        SET @col_list = CONCAT(
            @col_list, 
            'SPLIT_STR(''', REPLACE(delim_str, '''', ''''''), ''', ''*'', ', @loop_idx, ') AS Uni', @loop_idx
        );
        SET @loop_idx = @loop_idx + 1;
    END WHILE;
    -- 组装并执行最终查询
    SET @final_sql = CONCAT('SELECT ', @col_list);
    PREPARE stmt FROM @final_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END; //
DELIMITER ;

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