MySQL中将含逗号分隔字段的行拆分为多行的实现求助

原始表结构及数据：

+-----+-----+
|name |total|
+-----+-----+
|a,d  | 17  |
|b,f  | 9   |
|a    | 8   |
+-----+-----+

目标结果：

+-----+-----+
|name |total|
+-----+-----+
|a    | 17  |
|d    | 17  |
|b    | 9   |
|f    | 9   |
|a    | 8   |
+-----+-----+

解决方案1：使用递归CTE（MySQL 8.0+） #

递归CTE可处理任意数量的逗号分隔值，无需预先知晓拆分数量：

WITH RECURSIVE split_names AS (
    SELECT 
        SUBSTRING_INDEX(name, ',', 1) AS name,
        SUBSTRING(name FROM LOCATE(',', name) + 1) AS remaining_names,
        total
    FROM your_table
    WHERE name IS NOT NULL AND name != ''
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(remaining_names, ',', 1) AS name,
        SUBSTRING(remaining_names FROM LOCATE(',', remaining_names) + 1) AS remaining_names,
        total
    FROM split_names
    WHERE remaining_names IS NOT NULL AND remaining_names != ''
)
SELECT name, total
FROM split_names
ORDER BY total DESC, name;

解决方案2：数字辅助表法（兼容MySQL 5.x） #

若你的MySQL版本不支持CTE，可通过数字表配合字符串截取实现：

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.name, ',', n.num), ',', -1) AS name,
    t.total
FROM your_table t
JOIN (
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
    SELECT 4 UNION ALL SELECT 5 -- 数字数量需大于name字段最多的分隔值个数
) n
ON CHAR_LENGTH(t.name) - CHAR_LENGTH(REPLACE(t.name, ',', '')) >= n.num - 1
ORDER BY t.total DESC, name;

注意事项 #

• 将代码中的your_table替换为实际表名
• 数字辅助表中的数字数量要覆盖name字段中最多的逗号分隔值个数，避免遗漏数据
• 若存在NULL或空字符串的name记录，可通过WHERE条件过滤

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