Mysql 单行转多行，把逗号分隔的字段拆分成多行
'# Mysql 单行转多行，把逗号分隔的字段拆分成多行
一、背景与问题
在数据处理场景中，经常遇到需要将单行记录中逗号分隔的字段拆分成多行的场景。例如：
- 订单表中用逗号分隔的"商品ID"字段
- 用户表中用逗号分隔的"兴趣标签"字段
- 日志表中用逗号分隔的"错误代码"字段
这类需求的核心问题在于：如何将单行中以逗号分隔的字符串拆分成多行记录。直接使用MySQL的SELECT语句无法直接实现，需要借助字符串处理函数、递归CTE或者自定义函数等技术手段。
二、基本原理
MySQL 8.0+ 支持递归CTE（Common Table Expression），这是实现字符串拆分的核心工具。其核心原理如下：
- 使用
ROW_NUMBER()生成行号 - 使用
SUBSTRING_INDEX()进行字符串截取 - 通过递归CTE生成行号序列
- 与原表进行JOIN操作
对于旧版本MySQL（5.7及以下），需要使用自定义函数或存储过程来实现类似功能。
三、环境准备
-- 创建测试表
CREATE TABLE test_table (
id INT PRIMARY KEY,
csv_field VARCHAR(100)
);
-- 插入测试数据
INSERT INTO test_table (id, csv_field) VALUES
(1, 'A,B,C'),
(2, 'X,Y,Z'),
(3, '1,2,3');四、核心实现
1. 使用递归CTE实现（MySQL 8.0+）
WITH RECURSIVE split AS (
SELECT
id,
csv_field,
1 AS rn
FROM test_table
UNION ALL
SELECT
t.id,
t.csv_field,
s.rn + 1
FROM test_table t
JOIN split s ON s.id = t.id
WHERE
SUBSTRING_INDEX(t.csv_field, ',', s.rn) != SUBSTRING_INDEX(t.csv_field, ',', s.rn + 1)
)
SELECT
t.id,
SUBSTRING_INDEX(SUBSTRING_INDEX(s.csv_field, ',', s.rn), ',', -1) AS value
FROM test_table t
JOIN split s ON t.id = s.id
ORDER BY t.id, s.rn;关键代码解释：
SUBSTRING_INDEX(t.csv_field, ',', s.rn)：获取前n个逗号分割的字符串SUBSTRING_INDEX(..., ',', -1)：获取最后一个分割项rn字段用于控制递归深度- 通过递归CTE生成行号序列，配合字符串截取实现拆分
2. 使用自定义函数（MySQL 5.7+）
DELIMITER $$
CREATE FUNCTION split_csv(
str VARCHAR(1000),
delimiter CHAR(1)
)
RETURNS TEXT
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE len INT DEFAULT LENGTH(str);
DECLARE pos INT DEFAULT 1;
DECLARE result TEXT;
WHILE pos <= len DO
SET i = 1;
WHILE (i < len AND SUBSTRING(str, pos, 1) != delimiter) DO
SET i = i + 1;
END WHILE;
IF i <= len THEN
SET result = CONCAT(result, ',', SUBSTRING(str, pos, i));
SET pos = pos + i;
END IF;
END WHILE;
RETURN TRIM(result);
END $$
DELIMITER ;使用示例：
SELECT split_csv(csv_field, ',') AS split_result FROM test_table;注意事项：
- 该函数返回的是单行字符串，需要配合
SUBSTRING_INDEX()使用 - 存在性能瓶颈，不建议对大数据量使用
3. 使用JSON函数（MySQL 8.0+）
SELECT
id,
JSON_TABLE(
JSON_ARRAYAGG(
SUBSTRING_INDEX(SUBSTRING_INDEX(csv_field, ',', n.n), ',', -1)
)
OVER (PARTITION BY id),
'$[*]'
COLUMNS (
value VARCHAR(255) PATH '$'
)
) AS split_result
FROM test_table
CROSS JOIN
(SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS numbers
ORDER BY id;关键点：
- 使用
JSON_ARRAYAGG()聚合拆分后的值 JSON_TABLE()将数组转换为行- 需要预先知道最大拆分项数（通过numbers表实现）
五、完整案例
场景：拆分订单商品列表
需求：将订单表中的逗号分隔商品ID拆分成多行订单项
表结构：
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
products TEXT
);测试数据：
INSERT INTO orders (order_id, customer_id, products) VALUES
(1, 101, 'P001,P002,P003'),
(2, 102, 'P004,P005'),
(3, 103, 'P006');完整拆分SQL：
WITH RECURSIVE split AS (
SELECT
order_id,
customer_id,
1 AS rn
FROM orders
UNION ALL
SELECT
o.order_id,
o.customer_id,
s.rn + 1
FROM orders o
JOIN split s ON o.order_id = s.order_id
WHERE
SUBSTRING_INDEX(o.products, ',', s.rn) != SUBSTRING_INDEX(o.products, ',', s.rn + 1)
)
SELECT
o.order_id,
o.customer_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(s.products, ',', s.rn), ',', -1) AS product_id
FROM orders o
JOIN split s ON o.order_id = s.order_id
ORDER BY o.order_id, s.rn;输出结果：
order_id | customer_id | product_id
---------|------------|----------
1 | 101 | P001
1 | 101 | P002
1 | 101 | P003
2 | 102 | P004
2 | 102 | P005
3 | 103 | P006六、源码解析
1. 递归CTE拆分逻辑
- 初始查询生成基础行号（rn=1）
- 递归部分通过
SUBSTRING_INDEX()判断是否继续拆分 - 递归终止条件：当
SUBSTRING_INDEX(..., ',', s.rn)等于SUBSTRING_INDEX(..., ',', s.rn+1)时停止 - 通过
JOIN将拆分结果与原表关联
2. JSON函数优化方案
- 使用
JSON_ARRAYAGG()将拆分结果聚合为JSON数组 JSON_TABLE()将数组转换为行- 需要预先准备数字表（numbers）来确定拆分项数
- 适用于已知最大拆分项数的场景
七、进阶使用
1. 结合窗口函数
SELECT
id,
SUBSTRING_INDEX(SUBSTRING_INDEX(csv_field, ',', rn), ',', -1) AS value,
ROW_NUMBER() OVER (ORDER BY id) AS row_num
FROM test_table
CROSS JOIN
(SELECT 1 AS n UNION SELECT 2 UNION SELECT 3) AS numbers
ORDER BY id;2. 动态拆分
SELECT
id,
SUBSTRING_INDEX(SUBSTRING_INDEX(csv_field, ',', rn), ',', -1) AS value
FROM test_table
CROSS JOIN
(SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS numbers
WHERE
SUBSTRING_INDEX(csv_field, ',', rn) != ''
ORDER BY id, rn;3. 多字段拆分
SELECT
t.id,
s1.value AS field1,
s2.value AS field2
FROM test_table t
JOIN split s1 ON t.id = s1.id
JOIN split s2 ON t.id = s2.id
WHERE
s1.rn = s2.rn
ORDER BY t.id, s1.rn;八、性能与工程实践
1. 性能优化策略
| 方案 | 适用场景 | 优化建议 |
|---|---|---|
| 递归CTE | 数据量小 | 限制递归深度 |
| JSON函数 | 已知拆分项数 | 预先准备numbers表 |
| 自定义函数 | 数据量大 | 避免频繁调用 |
| 联表查询 | 需要关联其他表 | 使用JOIN优化 |
2. 索引优化
CREATE INDEX idx_csv_length ON test_table (LENGTH(csv_field));3. 异常处理
SELECT
id,
CASE WHEN SUBSTRING_INDEX(csv_field, ',', rn) = '' THEN NULL ELSE
SUBSTRING_INDEX(SUBSTRING_INDEX(csv_field, ',', rn), ',', -1)
END AS value
FROM test_table
CROSS JOIN numbers
ORDER BY id, rn;4. 安全风险
- SQL注入：使用
CONCAT()拼接SQL时要避免直接使用用户输入 - 数据污染：需要对原始字段进行校验（如长度限制）
- 索引失效：频繁使用
SUBSTRING_INDEX()可能导致索引失效
九、常见问题与踩坑
1. 分隔符不一致问题
错误示例：
SELECT SUBSTRING_INDEX('A,,B', ',', 2); -- 返回 'A'解决方案：
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX('A,,B', ',', n), ',', -1)
FROM numbers
WHERE n <= 3;2. 空值处理问题
错误示例：
SELECT SUBSTRING_INDEX('A,,B', ',', 3); -- 返回 'A,,B'解决方案：
SELECT
CASE WHEN SUBSTRING_INDEX(csv_field, ',', n) = '' THEN NULL
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(csv_field, ',', n), ',', -1)
END AS value3. 性能瓶颈
问题场景：
- 拆分项数超过1000
- 每次查询都使用递归CTE
- 使用
CROSS JOIN生成数字表
优化方案：
- 使用临时表存储数字序列
- 使用
CACHED查询缓存 - 对拆分字段添加索引
十、最佳实践
1. 使用场景建议
| 场景 | 是否适合 | 原因 |
|---|---|---|
| 数据导出 | ✅ | 需要按行处理 |
| 报表统计 | ✅ | 需要按项聚合 |
| 历史数据迁移 | ✅ | 需要转换格式 |
| 实时查询 | ❌ | 会阻塞查询 |
2. 推荐方案
- 优先使用递归CTE（MySQL 8.0+）
- 次选JSON函数（已知拆分项数）
- 避免自定义函数（维护成本高）
- 谨慎使用子查询（性能开销大）
3. 安全建议
- 对用户输入的字段进行校验（长度、格式）
- 使用
CONCAT()代替字符串拼接 - 对敏感字段进行脱敏处理
- 对查询结果进行过滤
十一、总结
MySQL单行转多行的实现涉及字符串处理、递归CTE、JSON函数等技术，需要根据具体场景选择合适方案。递归CTE是MySQL 8.0+推荐的解决方案，具有良好的可读性和性能。在实际开发中需要注意处理空值、分隔符不一致等问题，同时避免在实时查询中使用这种方案。对于大数据量场景，建议预先准备数字表或使用存储过程优化性能。通过合理使用索引和缓存，可以有效提升查询效率。在开发过程中，要始终关注安全性和数据完整性，避免因格式错误导致的数据污染。
评论已关闭