MySQL中将逗号分隔的字段转换为多行数据的方法_Mysql

场景介绍

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

查询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 )- length( replace ( t1.pages, ',', '' ))+ 1 ) 
where
 t1.pages is not null 
order by
 t1.id,
 t2.help_topic_id

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

这个表有一个特性，就是它有从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 )- length( replace ( t1.pages, ',', '' ))+ 1 ) 
where
 t1.pages is not null 
order by
 t1.id,
 t2.help_topic_id

执行后数据表如下：