MySQL中逗号分隔字符串行转列的终极指南 在数据库操作中，我们经常遇到需要将逗号分隔的字符串转换为多行数据的需求

    这种需求在MySQL中尤为常见，尤其是在处理一些不规范的数据导入或日志分析时

    虽然MySQL不像某些高级数据库系统那样内置了直接处理此类问题的函数，但通过巧妙的SQL查询和存储过程，我们仍然可以高效地完成这一任务

    本文将详细介绍如何在MySQL中实现逗号分隔字符串行转列的操作，并提供多种解决方案以满足不同场景的需求

     一、问题背景 假设我们有一个表`user_info`，其中有一列`hobbies`存储了用户的兴趣爱好，这些兴趣爱好以逗号分隔，如下所示： sql CREATE TABLE user_info( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), hobbies VARCHAR(255) ); INSERT INTO user_info(name, hobbies) VALUES (Alice, reading,swimming,cooking), (Bob, running,cycling), (Charlie, gaming,reading,travelling); 我们的目标是将`hobbies`列中的逗号分隔字符串转换为多行数据，如下所示： +----+---------+----------+ | id | name| hobby| +----+---------+----------+ |1 | Alice | reading| |1 | Alice | swimming | |1 | Alice | cooking| |2 | Bob | running| |2 | Bob | cycling| |3 | Charlie | gaming | |3 | Charlie | reading| |3 | Charlie | travelling | +----+---------+----------+ 二、基础解决方案：使用递归CTE（MySQL8.0及以上） 从MySQL8.0开始，MySQL引入了公共表表达式（CTE）和递归CTE，这使得我们可以更高效地处理此类问题

    以下是一个使用递归CTE的解决方案： sql WITH RECURSIVE hobby_split AS( SELECT id, name, SUBSTRING_INDEX(hobbies, ,,1) AS hobby, SUBSTRING(hobbies FROM LOCATE(,, hobbies) +1) AS remaining_hobbies, 1 AS level FROM user_info WHERE hobbies IS NOT NULL AND CHAR_LENGTH(hobbies) >0 UNION ALL SELECT id, name, SUBSTRING_INDEX(remaining_hobbies, ,,1) AS hobby, IF(LOCATE(,, remaining_hobbies) >0, SUBSTRING(remaining_hobbies FROM LOCATE(,, remaining_hobbies) +1), NULL) AS remaining_hobbies, level +1 FROM hobby_split WHERE remaining_hobbies <> ) SELECT id, name, hobby FROM hobby_split ORDER BY id, level; 解释： 1.初始选择：从`user_info`表中选取第一组数据，使用`SUBSTRING_INDEX`函数提取第一个逗号前的字符串作为`hobby`，剩余部分作为`remaining_hobbies`

     2.递归部分：继续处理`remaining_hobbies`，直到没有剩余的逗号分隔字符串

     3. 最终选择：从递归CTE中选择所需的列，并按`id`和递归层级`level`排序

     三、使用存储过程（适用于所有MySQL版本） 如果你的MySQL版本低于8.0，或者你更喜欢使用存储过程来处理此类问题，以下是一个使用存储过程的解决方案： sql DELIMITER // CREATE PROCEDURE SplitHobbies() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE user_id INT; DECLARE user_name VARCHAR(50); DECLARE user_hobbies VARCHAR(255); DECLARE hobby VARCHAR(255); DECLARE cur CURSOR FOR SELECT id, name, hobbies FROM user_info; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; CREATE TEMPORARY TABLE temp_hobbies( id INT, name VARCHAR(50), hobby VARCHAR(255) ); OPEN cur; read_loop: LOOP FETCH cur INTO user_id, user_name, user_hobbies; IF done THEN LEAVE read_loop; END IF; SET hobby = SUBSTRING_INDEX(user_hobbies, ,,1); WHILE hobby <> DO INSERT INTO temp_hobbies(id, name, hobby) VALUES(user_id, user_name, hobby); SET user_hobbies = IF(LOCATE(,, user_hobbies) >0, SUBSTRING(user_hobbies FROM LOCATE(,, user_hobbies) +1),); SET hobby = SUBSTRING_INDEX(user_hobbies, ,,1); END WHILE; END LOOP; CLOSE cur; SELECT - FROM temp_hobbies ORDER BY id; DROP TEMPORARY TABLE temp_hobbies; END // DELIMITER ; CALL SplitHobbies(); 解释： 1.声明游标`cur`，用于遍历`user_info`表

     2.创建一个临时表`temp_hobbies`来存储结果

     3. 打开游标，并在循环中逐行处理数据

     4. 使用`SUBSTRING_INDEX`和`LOCATE`函数分割字符串，并将结果插入临时表

     5. 关闭游标，选择临时表中的数据，并删除临时表

     四、使用数字表（适用于所有MySQL版本） 有时，我们可能希望避免使用存储过程，而是使用纯SQL查询

    这时，我们可以创建一个数字表来辅助处理

    数字表是一个包含一系列数字的表，这些数字用于拆分字符串

     首先，创建一个数字表（假设我们只需要处理最多10个逗号分隔的值）： sql CREATE TABLE numbers(n INT); INSERT INTO numbers(n) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); 然后，使用以下查询拆