MySQL中string_split函数的替代方案与字符串转多行实现

基础字符串拆分转多行实现 #

方法一：递归CTE（MySQL 8.0+） #

适合支持CTE的新版本MySQL，写法简洁：

WITH RECURSIVE split_strings AS (
    SELECT 
        SUBSTRING_INDEX('r,a,d,o', ',', 1) AS value,
        SUBSTRING('r,a,d,o', LENGTH(SUBSTRING_INDEX('r,a,d,o', ',', 1)) + 2) AS remaining
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(remaining, ',', 1),
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2)
    FROM split_strings
    WHERE remaining != ''
)
SELECT value FROM split_strings;

执行后会将'r,a,d,o'拆分为每行一个值的结果集。

方法二：数字表（兼容低版本MySQL） #

如果你的MySQL版本低于8.0，不支持CTE，可以用数字辅助表实现：

-- 创建临时数字表，包含足够覆盖拆分后元素数量的数字
CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (1),(2),(3),(4),(5); -- 按需扩展数量

-- 拆分字符串
SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX('r,a,d,o', ',', n), ',', -1) AS value
FROM nums
WHERE n <= LENGTH('r,a,d,o') - LENGTH(REPLACE('r,a,d,o', ',', '')) + 1;

通过计算原字符串中的逗号数量，确定拆分后的元素总数，再用数字表逐个提取每个元素。

适配IN查询的场景实现 #

针对select id from month_tab where name in (string_split('jan,feb,mar,may'))的需求，直接将拆分逻辑作为子查询嵌入IN子句即可：

递归CTE版本 #

SELECT id 
FROM month_tab 
WHERE name IN (
    WITH RECURSIVE split_strings AS (
        SELECT 
            SUBSTRING_INDEX('jan,feb,mar,may', ',', 1) AS value,
            SUBSTRING('jan,feb,mar,may', LENGTH(SUBSTRING_INDEX('jan,feb,mar,may', ',', 1)) + 2) AS remaining
        UNION ALL
        SELECT 
            SUBSTRING_INDEX(remaining, ',', 1),
            SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2)
        FROM split_strings
        WHERE remaining != ''
    )
    SELECT value FROM split_strings
);

数字表版本（低版本兼容） #

-- 假设已创建nums临时表
SELECT id 
FROM month_tab 
WHERE name IN (
    SELECT 
        SUBSTRING_INDEX(SUBSTRING_INDEX('jan,feb,mar,may', ',', n), ',', -1) AS value
    FROM nums
    WHERE n <= LENGTH('jan,feb,mar,may') - LENGTH(REPLACE('jan,feb,mar,may', ',', '')) + 1
);

简化替代方案：FIND_IN_SET函数 #

如果数据量不大，也可以用FIND_IN_SET直接替代逻辑，但注意该函数无法利用索引，大数据量场景性能较差：

SELECT id 
FROM month_tab 
WHERE FIND_IN_SET(name, 'jan,feb,mar,may') > 0;

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