MySQL 中将使用逗号分隔的字段转换为多行数据

在我们的实际开发中,经常需要存储一些字段,它们使用像, - 等连接符进行连接。在查询过程中,有时需要将这些字段使用连接符分割,然后查询多条数据。今天,我们将使用一个实际的生产场景来详细解释这个解决方案。

MySQL 中将使用逗号分隔的字段转换为多行数据

04981b82946c4eb4a9bf75845a32d968_1.png

场景介绍

最近我们对一个需求进行了改造。在此之前,我们有一个工单信息表名为bus_mark_info,其中包含一个配置字段pages。以前,为了方便配置,配置人员直接将多个页面使用逗号连接后保存,就像是将page1, page2, page3等直接存储在了该字段中。随着业务的发展,我们现在需要对每个页面进行单独配置,并添加一些其他属性。为了实现这一需求,我们在bus_mark_info表中添加了一个关联表bus_pages。在上线时,我们需要将已有的pages字段中配置历史数据的页面值使用逗号进行分割,并存入新的表中,然后废弃掉工单信息表中的pages字段。bus_mark_info表数据如下:MySQL 中将使用逗号分隔的字段转换为多行数据

查询SQL 语句编写

我们首先是将要新增的数据查询出来,然后使用insert into … select 迁移到我们的新表中。话不多说,我们直接上sql:

SELECT 
 T1.id,
 SUBSTRING_INDEX( SUBSTRING_INDEX( T1.pages, ',', T2.help_topic_id + 1 ), ',',- 1 ) AS page 
FROM
 bus_mark_info T1
 JOIN MySQL.help_topic T2 ON T2.help_topic_id < ( length( T1.pages )- lengthREPLACE ( T1.pages, ',''' ))+ 1 ) 
WHERE
 T1.pages IS NOT NULL 
ORDER BY
 T1.id,
 T2.help_topic_id

在这个sql中,我们使用了mysql 的help_topic表,这个表存储的是各种注释、地址等帮助信息,内容如下:


MySQL 中将使用逗号分隔的字段转换为多行数据


这个表有一个特性,就是它有从0开始自增为1的id属性–help_topic_id 并且 拥有固定数量(701)的数据。

  • 关联数据数量

原始的bus_mark_info表中的每条数据,在与help_topic表关联后会生成多条新数据。具体来说,对于bus_mark_info表中的每条记录,我们期望生成的关联数据数量应该等于该记录中pages字段中逗号的数量加1。例如,如果某条数据的pages字段的取值为page1,page2,page3,那么我们应该生成三条关联数据。因此,我们的关联条件应该是T2.help_topic_id < (length(T1.pages) - length(REPLACE(T1.pages, ',', '')) + 1)

  • 正确分割字段

一旦确保了正确的关联数据数量,我们需要根据help_topic_id的值来截取我们的数据。例如,当help_topic_id为0时,我们应该取pages字段中第一个逗号之前的值;当help_topic_id为1时,我们应该取pages字段中第一个逗号和第二个逗号之间的值,依此类推。为实现这一目标,我们将使用两个SUBSTRING_INDEX函数来进行数据截取。首先,我们将截取从开始位置到help_topic_id+1个逗号之前的部分,然后再截取该部分中最后一个逗号之后的部分,即SUBSTRING_INDEX( SUBSTRING_INDEX( T1.pages, ',', T2.help_topic_id + 1 ), ',',- 1 )。通过这样的处理,我们便成功地利用help_topic_id和SUBSTRING_INDEX函数完成了数据的分割。

  • 注意事项

当然,我们使用help_topic是因为他的help_topic_id是从0开始,每次递增1的,我们也可以使用有次特性的别的表或者数据代替。help_topic_id最大值为700,也就是说我们这个sql只能处理pages最多有701个页面连接的数据,如果有些pages字段分割之后的数量大于701,我们则需要使用别的表来替代。

如果有家人对SUBSTRING_INDEX函数和insert into … select不太熟悉的话可以翻阅下我们历史的文章,有专门介绍过。

迁移数据sql

迁移数据的sql如下:

INSERT INTO bus_pages ( mark_id, page ) SELECT
T1.id,
SUBSTRING_INDEX( SUBSTRING_INDEX( T1.pages, ',', T2.help_topic_id + 1 ), ',',- 1 ) AS page 
FROM
 bus_mark_info T1
 JOIN mysql.help_topic T2 ON T2.help_topic_id < ( length( T1.pages )- lengthREPLACE ( T1.pages, ',''' ))+ 1 ) 
WHERE
 T1.pages IS NOT NULL 
ORDER BY
 T1.id,
 T2.help_topic_id

执行后数据表如下:

MySQL 中将使用逗号分隔的字段转换为多行数据
_20240402230223.jpg

总结

在实际开发中,当需要对包含多个字段连接符的数据进行查询与迁移时,可以使用SQL中的SUBSTRING_INDEX函数结合一些辅助表的特性进行数据分割和迁移。通过合理的SQL编写,可以有效处理数据关联与拆分,达到迁移数据的目的。


原文始发于微信公众号(修己xj):MySQL 中将使用逗号分隔的字段转换为多行数据

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

文章由半码博客整理,本文链接:https://www.bmabk.com/index.php/post/267378.html

(0)
土豆大侠的头像土豆大侠

相关推荐

发表回复

登录后才能评论
半码博客——专业性很强的中文编程技术网站,欢迎收藏到浏览器,订阅我们!