MySQL中列如何以逗号分隔转成多行
目录
- MySQL列以逗号分隔转成多行
- 场景
- 解决方案
- 总结
MySQL列以逗号分隔转成多行
业务场景:
在数据库中,有一张的一个字段存储方式是采用以逗号分隔存储多个值,现在需要将其进行拆分成多个独立的值,与另外一张字典表进行关联,取的最终的字典表中的 label,再以逗号拼接成显示 label 的形式展现。
场景
表中存储的值:
期待最终的展现效果:
甜品,休闲食品,饮料
解决方案
1. 将一列转成多行
select v1.id,SUBSTRING_INDEX(SUBSTRING_INDEX(v1.intention_exhibits, ',', b.help_topic_id + 1), ',', - 1) AS exhibit from test v1 JOIN mysql.help_topic AS b ON b.help_topic_id < (length(v1.intention_exhibits) - length(REPLACE(v1.intention_exhibits, ',', '')) + 1) where v1.id = '63591ee4f8204212837e447b34c61fef';
说明:
mysql.help_topic 表的自增id是从0开始,所以在进行截取时要对id进行+1。【系统表,不建议使用,真正的线上环境,dba 是不允许使用系统表的,所以,我们需要自己创建一张类似的表】
创建一张自增表,来代替 mysql.help_topic 系统表,自增表的值,需要大于自己业务表中逗号拆出来的集合数:
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. 最终 SQL
select group_concat(edn.name) 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
length
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。
赞 (0)