mysql查询包含逗号的数据,并逗号拆分为多行展现

在做系统开发的时候,有可能是由于之前的遗留问题,导致在数据入库的时候,将多个数据以逗号分隔的实行,存储在一条数据中,例如:

ID VALUE
1 yang,zheng,song
2 zhao,qian,sun
3 jiang

现在因为新的需求,需要将这些数据以下边的方式存储在数据库中,方便查询和处理:

ID VALUE
1 yang
1 zheng
1 song
2 zhao
2 qian
2 sun
3 jiang

所以需要将原有的数据查询出来,然后进行逗号分隔为多行,并插入到新的表中。

假设我们需要处理的表的结构为:

NAME TYPE LEN
ID INT 11
NAME VARCHAR 255

将数据查询并分隔的sql语句为:

1
2
3
4
5
SELECT a.id,
substring_index( substring_index( a.name, ',', b.help_topic_id + 1 ), ',',- 1 ) name
FROM table1 a
JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.name ) - length( REPLACE ( a.name, ',', '' ) ) + 1 )
ORDER BY a.id

 

插入就比较简单了,使用insert into语句将查出的数据插入到相应的表中即可。

查询的主要思路在于,和一个包含连续自增长字段的表进行 join,得到字符串分隔后的索引值,其中

1
2
3
4
5
在JOIN之后会得到:之后对查询中的结果,借助substring_index方法进行截取,然后得到自己想要的数据。 我们在JOIN的时候借助了 mysql.help_topic 表,表中的help_topic_id是从0582左右连续自增的数值,所以我们可以使用,如果有遇到数据经过逗号分隔之后得到的数组长度大于582,则需要自己建立一个连续自增表来进行JOIN,比如:create table incre_table (AutoIncreID int);
insert into incre_table values (0);
insert into incre_table values (1);
insert into incre_table values (2);
。。。。。。。。。。
ID NAME HELP_TOPIC_ID
1 yang,zheng,song 0
1 yang,zheng,song 1
1 yang,zheng,song 2

 转载于:https://www.cnblogs.com/icesnow521/p/9253946.html

posted @   乔小生1221  阅读(2253)  评论(0编辑  收藏  举报
编辑推荐:
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示