MySQL中将逗号分隔的列值拆分为多行并拼接显示

业务场景与目标

假设数据库中有一张记录表，其中 intention_exhibits 字段存储了以逗号分隔的分类ID（如 "10,11,12"）。业务需求是将这些ID拆分后，关联到分类字典表获取具体的名称（label），最后将名称以逗号分隔的形式展示出来。

示例输入数据结构如下：

期望的最终输出效果为纯净的文本标签字符串，例如：甜品，休闲食品，饮料。

核心难点与准备条件

要将单个字符串按分隔符拆分成多行，通常需要一个“数字辅助表”来提供计数索引。虽然MySQL内置了 mysql.help_topic 表，但在生产环境中，DBA通常禁止直接查询或依赖系统内部表。因此，我们需要创建一张自定义的自增ID辅助表来替代。

第1步：创建自增辅助表

目的：构建一个包含足够数量连续整数的临时或永久表，用于驱动字符串切割循环。

操作说明：创建一个简单的表，插入从0开始递增的整数。表中最大ID必须大于业务数据中可能出现的最大逗号分隔项数量。

create table add_self
(
    id int(20) null
);

INSERT INTO add_self (id) VALUES (0);
INSERT INTO add_self (id) VALUES (1);
INSERT INTO add_self (id) VALUES (2);
INSERT INTO add_self (id) VALUES (3);
INSERT INTO add_self (id) VALUES (4);
INSERT INTO add_self (id) VALUES (5);
INSERT INTO add_self (id) VALUES (6);
INSERT INTO add_self (id) VALUES (7);
INSERT INTO add_self (id) VALUES (8);
INSERT INTO add_self (id) VALUES (9);
INSERT INTO add_self (id) VALUES (10);

第2步：执行拆分与关联查询

目的：利用 SUBSTRING_INDEX 嵌套调用按索引截取子串，并与字典表关联获取名称。

关键逻辑：

• length(str) - length(replace(str, ',', '')) + 1：计算字段中逗号分隔的元素个数。
• SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', n), ',', -1)：通过双重截取定位第N个元素。内层取前N部分，外层取最后一部分。
• group_concat(...)：将分散的多行结果重新合并为单行字符串。

最终SQL语句如下：

SELECT group_concat(edn.name) AS final_labels
FROM (
    SELECT v1.id,
           SUBSTRING_INDEX(SUBSTRING_INDEX(v1.intention_exhibits, ',', b.id + 1), ',', -1) AS exhibit
    FROM test1 v1
    JOIN add_self AS b ON b.id < (LENGTH(v1.intention_exhibits) -
                                   LENGTH(REPLACE(v1.intention_exhibits, ',', '')) + 1)
    WHERE v1.id = '63591ee4f8204212837e447b34c61fef'
) t
LEFT JOIN test2 edn ON t.exhibit = edn.local_key;

拆分后的中间过程数据示例：

最终关联并拼接后的结果：

涉及的关键函数说明

• group_concat()：聚合函数，将分组后的字符串连接起来，默认以逗号分隔。
• substring_index(str, delim, count)：返回分隔符之前的子字符串。当 count 为负数时，从末尾开始计数。
• length()：返回字符串长度，用于动态计算分割次数。

注意事项

1. 性能考虑：此方法适用于数据量较小的场景。如果单字段分隔值极大或表数据量千万级，建议重构数据库设计，采用第三范式，将拆分数据存储到独立的关联表中。
2. 分隔符唯一性：确保业务数据中没有包含额外的特殊字符干扰截取逻辑。
3. 排序问题：默认 group_concat 的输出顺序可能与原始ID顺序不一致，如需保持特定顺序，可在 group_concat 中添加 ORDER BY 子句。

以上就是MySQL中列以逗号分隔转成多行的详细内容，更多关于MySQL数据处理技巧的资料请关注本站其它相关文章！