MySQL中列如何以逗号分隔转成多行

MySQL列以逗号分隔转成多行

业务场景：

在数据库中，有一张的一个字段存储方式是采用以逗号分隔存储多个值，现在需要将其进行拆分成多个独立的值，与另外一张字典表进行关联，取的最终的字典表中的 label，再以逗号拼接成显示 label 的形式展现。

场景

表中存储的值：

期待最终的展现效果：

甜品，休闲食品，饮料

解决方案

1. 将一列转成多行

1. select v1.id,SUBSTRING_INDEX(SUBSTRING_INDEX(v1.intention_exhibits, ',', b.help_topic_id + 1), ',', - 1) AS exhibit
2. from test v1
3. JOIN mysql.help_topic AS b ON b.help_topic_id < (length(v1.intention_exhibits) -
4. length(REPLACE(v1.intention_exhibits, ',', '')) + 1)
5. where v1.id = '63591ee4f8204212837e447b34c61fef';

说明：

mysql.help_topic 表的自增id是从0开始，所以在进行截取时要对id进行+1。【系统表，不建议使用，真正的线上环境，dba 是不允许使用系统表的，所以，我们需要自己创建一张类似的表】

创建一张自增表，来代替 mysql.help_topic 系统表，自增表的值，需要大于自己业务表中逗号拆出来的集合数：

1. create table add_self
2. (
3. id int(20) null
4. );
5.  
6. INSERT INTO add_self (id) VALUES (0);
7. INSERT INTO add_self (id) VALUES (1);
8. INSERT INTO add_self (id) VALUES (2);
9. INSERT INTO add_self (id) VALUES (3);
10. INSERT INTO add_self (id) VALUES (4);
11. INSERT INTO add_self (id) VALUES (5);
12. INSERT INTO add_self (id) VALUES (6);
13. INSERT INTO add_self (id) VALUES (7);
14. INSERT INTO add_self (id) VALUES (8);
15. INSERT INTO add_self (id) VALUES (9);
16. INSERT INTO add_self (id) VALUES (10);

2. 最终 SQL

1. select group_concat(edn.name)
2. from (
3. select v1.id,SUBSTRING_INDEX(SUBSTRING_INDEX(v1.intention_exhibits, ',', b.id + 1), ',', - 1) AS exhibit
4. from test1 v1
5. JOIN add_self AS b ON b.id < (length(v1.intention_exhibits) -
6. length(REPLACE(v1.intention_exhibits, ',', '')) + 1)
7. where v1.id = '63591ee4f8204212837e447b34c61fef') t
8. left join test2 edn on t.exhibit = edn.local_key;

使用到的相关函数：

• group_concat
• substring_index
• length

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。

原文链接：https://blog.csdn.net/qq_20315217/article/details/123473365