MySQL如何实现无长度限制的逗号分隔字符串拆分？

针对你这种存储了不定数量逗号分隔值的字段，又不想每次拆分都指定位置参数的需求，这里有几种实用的MySQL实现方案：

方案1：使用递归CTE（MySQL 8.0及以上版本） #

递归CTE是最灵活的方式，不需要依赖额外的表或函数，还能自动处理末尾带逗号的情况：

WITH RECURSIVE split_cte AS (
    -- 初始化：处理原始字符串，去掉首尾逗号，避免空值
    SELECT 
        1 AS pos,
        TRIM(BOTH ',' FROM your_column) AS remaining_str,
        SUBSTRING_INDEX(TRIM(BOTH ',' FROM your_column), ',', 1) AS split_value
    FROM your_table
    WHERE your_column IS NOT NULL AND your_column != ''
    
    UNION ALL
    
    -- 递归拆分剩余字符串
    SELECT
        pos + 1,
        SUBSTRING(remaining_str, LENGTH(split_value) + 2), -- +2是跳过逗号和已拆分的字符
        SUBSTRING_INDEX(SUBSTRING(remaining_str, LENGTH(split_value) + 2), ',', 1)
    FROM split_cte
    WHERE remaining_str != ''
)
SELECT split_value FROM split_cte;

说明： #

• TRIM(BOTH ',' FROM your_column) 会先把字符串首尾的逗号去掉，避免拆分出空字符串
• 递归部分会不断截取剩余的字符串，直到没有内容为止，自动覆盖所有拆分值

方案2：借助数字辅助表（兼容低版本MySQL） #

如果你的MySQL版本低于8.0，不支持CTE，可以先创建一个数字表，存储足够多的连续整数（比如1到1000，覆盖你的最大拆分数量）：

-- 创建数字表（只需执行一次）
CREATE TABLE numbers (n INT PRIMARY KEY AUTO_INCREMENT);
INSERT INTO numbers VALUES (),(),(),(),(),(),(),(),(),(); -- 先插入10条，不够可以继续插或者用循环生成更多

然后用这个数字表配合SUBSTRING_INDEX来拆分：

SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH ',' FROM t.your_column), ',', n.n), ',', -1) AS split_value
FROM your_table t
JOIN numbers n ON n.n <= LENGTH(TRIM(BOTH ',' FROM t.your_column)) - LENGTH(REPLACE(TRIM(BOTH ',' FROM t.your_column), ',', '')) + 1
WHERE t.your_column IS NOT NULL AND t.your_column != '';

说明： #

• LENGTH(...) - LENGTH(REPLACE(...)) +1 用来计算逗号分隔值的总数，确保只匹配需要的数字行数
• 同样用TRIM处理了首尾逗号的问题，避免出现空结果

方案3：自定义返回表的拆分函数 #

如果你想封装成函数，替代原来的SPLIT_STR，可以创建一个返回结果集的函数：

DELIMITER //
CREATE FUNCTION SPLIT_STR_TO_TABLE(x VARCHAR(255), delim VARCHAR(12))
RETURNS TABLE
BEGIN
    RETURN (
        WITH RECURSIVE split_cte AS (
            SELECT
                1 AS pos,
                TRIM(BOTH delim FROM x) AS remaining_str,
                SUBSTRING_INDEX(TRIM(BOTH delim FROM x), delim, 1) AS split_value
            WHERE x IS NOT NULL AND x != ''
            
            UNION ALL
            
            SELECT
                pos + 1,
                SUBSTRING(remaining_str, LENGTH(split_value) + LENGTH(delim) + 1),
                SUBSTRING_INDEX(SUBSTRING(remaining_str, LENGTH(split_value) + LENGTH(delim) + 1), delim, 1)
            FROM split_cte
            WHERE remaining_str != ''
        )
        SELECT split_value FROM split_cte
    );
END //
DELIMITER ;

调用的时候直接用：

SELECT * FROM SPLIT_STR_TO_TABLE('1,2,3,4,5,', ',');

这样就能直接得到所有拆分后的字符串值，不需要指定位置参数。

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