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

目录

• • 效果如下
  • • 源数据
    • 变更后的数据
    • 方法
    • • 第一种
      • • 示例SQL
        • 和业务结合在一起使用
        • 第二种
        • • 示例SQL
          • 和业务结合在一起使用
          • 结论

            效果如下

            源数据

            

            变更后的数据

            

            方法

            第一种

            先执行下面的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  
            和业务结合在一起使用
             
            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
            		INNER JOIN mysql.help_topic b ON b.help_topic_id  
            其核心在于mysql.help_topic，但是版本太低的数据库版本不支持，如果不支持，可以用下面第二种。
             
            第二种
             
            示例SQL
             
            SELECT
              SUBSTRING_INDEX(SUBSTRING_INDEX(table_name.csv_values, ',', numbers.n), ',', -1) AS split_value
            FROM
              table_name
              INNER JOIN
              (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
               SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
               SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
               SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL
               SELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40) 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 = 1003 and type =0 and start_year = 2023  group by store_signer_name,store_signer_contact having count(1) > 1) table_name
              INNER JOIN
              (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
               SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
               SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL
               SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL
               SELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40) numbers
              ON CHAR_LENGTH(table_name.concatStoreId) - CHAR_LENGTH(REPLACE(table_name.concatStoreId, ',', '')) >= numbers.n - 1;
             
            结论
             
            如果Mysql版本较低，使用第二种，如果可以执行第一种示例SQL，那么推荐使用第一种，动态的。