Mysql 行转列，把逗号分隔的字段拆分成多行

目录

效果如下

源数据

变更后的数据

方法

第一种

先执行下面的SQL，看不看能不能执行，如果有结果，代表数据库版本是可以的，可以看下面和自己表关联的SQL，如果不行用第二种。

示例SQL

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1)AS num 
FROM mysql.help_topic 
WHERE help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1

和业务结合在一起使用

SELECT
        a.store_signer_name,
        substring_index( substring_index( a.concatStoreId,',', b.help_topic_id +1),',',-1)AS concatStoreId 
    FROM(select store_signer_nameconcatStoreId from test) a
        INNERJOIN mysql.help_topic b ON b.help_topic_id <(
            length( a.concatStoreId )- length(REPLACE( a.concatStoreId,',',''))+1)

其核心在于mysql.help_topic，但是版本太低的数据库版本不支持，如果不支持，可以用下面第二种。

第二种

示例SQL

SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(table_name.csv_values,',', numbers.n),',',-1)AS split_value
FROM
  table_name
  INNERJOIN(SELECT1 n UNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5UNIONALLSELECT6UNIONALLSELECT7UNIONALLSELECT8UNIONALLSELECT9UNIONALLSELECT10UNIONALLSELECT11UNIONALLSELECT12UNIONALLSELECT13UNIONALLSELECT14UNIONALLSELECT15UNIONALLSELECT16UNIONALLSELECT17UNIONALLSELECT18UNIONALLSELECT19UNIONALLSELECT20UNIONALLSELECT21UNIONALLSELECT22UNIONALLSELECT23UNIONALLSELECT24UNIONALLSELECT25UNIONALLSELECT26UNIONALLSELECT27UNIONALLSELECT28UNIONALLSELECT29UNIONALLSELECT30UNIONALLSELECT31UNIONALLSELECT32UNIONALLSELECT33UNIONALLSELECT34UNIONALLSELECT35UNIONALLSELECT36UNIONALLSELECT37UNIONALLSELECT38UNIONALLSELECT39UNIONALLSELECT40) numbers
  ON CHAR_LENGTH(table_name.csv_values)- CHAR_LENGTH(REPLACE(table_name.csv_values,',',''))>= numbers.n -1;

在上面的查询中，因为我逗号分隔的最大个数是36，所以我添加了40个UNION ALL SELECT子句，以生成数字序列1到40。你可以根据需要调整这个序列的长度。

请注意，如果你的逗号分隔值个数大于40，那么你需要相应地增加数字序列的长度。

和业务结合在一起使用

SELECT
table_name.store_signer_name,
table_name.store_signer_contact,
  SUBSTRING_INDEX(SUBSTRING_INDEX(table_name.concatStoreId,',', numbers.n),',',-1)AS store_id
FROM(select store_signer_name,store_signer_contact,GROUP_CONCAT(store_id) concatStoreId from t_store_esgin_info where business_status =1003andtype=0and start_year =2023groupby store_signer_name,store_signer_contact havingcount(1)>1) table_name
  INNERJOIN(SELECT1 n UNIONALLSELECT2UNIONALLSELECT3UNIONALLSELECT4UNIONALLSELECT5UNIONALLSELECT6UNIONALLSELECT7UNIONALLSELECT8UNIONALLSELECT9UNIONALLSELECT10UNIONALLSELECT11UNIONALLSELECT12UNIONALLSELECT13UNIONALLSELECT14UNIONALLSELECT15UNIONALLSELECT16UNIONALLSELECT17UNIONALLSELECT18UNIONALLSELECT19UNIONALLSELECT20UNIONALLSELECT21UNIONALLSELECT22UNIONALLSELECT23UNIONALLSELECT24UNIONALLSELECT25UNIONALLSELECT26UNIONALLSELECT27UNIONALLSELECT28UNIONALLSELECT29UNIONALLSELECT30UNIONALLSELECT31UNIONALLSELECT32UNIONALLSELECT33UNIONALLSELECT34UNIONALLSELECT35UNIONALLSELECT36UNIONALLSELECT37UNIONALLSELECT38UNIONALLSELECT39UNIONALLSELECT40) numbers
  ON CHAR_LENGTH(table_name.concatStoreId)- CHAR_LENGTH(REPLACE(table_name.concatStoreId,',',''))>= numbers.n -1;

结论

如果Mysql版本较低，使用第二种，如果可以执行第一种示例SQL，那么推荐使用第一种，动态的。