MySQL逗号分隔列转行
在MySQL数据库中，有时候我们会遇到一种情况，即某一列的数据是以逗号分隔的形式存储的，如下所示：
表名: students
| id | name | subjects |
|----|------------|-----------------------------|
| 1 | John Smith | Math,Science,English |
| 2 | Emma Brown | Art,History,Geography |
| 3 | Tom White | Physics,Chemistry,Biology |
上述示例中的subjects列包含了每个学生所学科目的名称，多个科目之间以逗号进行分隔。但是，这种存储方式在进行数据分析和查询时并不方便。
本文将介绍如何将逗号分隔的列转换为行，以便更方便地进行数据分析和查询。
解决方案
为了将逗号分隔的列转换为行，我们可以使用MySQL内置的字符串函数和表达式。以下是一种常见的方法，可以将逗号分隔的列转换为多行数据。
步骤一：创建新表
首先，我们需要创建一个新的表，用于存储拆分后的行数据。
CREATE TABLE subjects (
id INT,
name VARCHAR(255),
subject VARCHAR(255)
);
步骤二：插入数据
接下来，我们需要将原表中的数据插入到新表中，同时拆分逗号分隔的列为多行数据。
INSERT INTO subjects (id, name, subject)
SELECT id, name, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(subjects, ',', n), ',', -1)) AS subject
FROM students
JOIN (
SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 -- 根据最大科目数调整
) AS numbers
ON CHAR_LENGTH(subjects) - CHAR_LENGTH(REPLACE(subjects, ',', '')) >= n - 1;
上述代码中，我们使用了SUBSTRING_INDEX函数来拆分逗号分隔的字符串，并使用TRIM函数去除拆分后的数据中的空格。
步骤三：检查结果
现在，我们可以查询新表中的数据，以检查逗号分隔的列是否已成功转换为行。
SELECT * FROM subjects;
运行以上查询语句后，你将会得到以下结果：
| id | name | subject |
|----|------------|------------|
| 1 | John Smith | Math |
| 1 | John Smith | Science |
| 1 | John Smith | English |
| 2 | Emma Brown | Art |
| 2 | Emma Brown | History |
| 2 | Emma Brown | Geography |
| 3 | Tom White | Physics |
| 3 | Tom White | Chemistry |
| 3 | Tom White | Biology |
如你所见，逗号分隔的列已经被转换为了多行的形式，每行对应一个科目。
进一步优化
上述方法虽然可以将逗号分隔的列转换为行，但是它使用了一个子查询来生成数字序列，并且无法应对不同学生所学科目数量不同的情况。下面是一种更优化的方法，可以自动适应不同的情况。
步骤一：创建新表
首先，我们仍然需要创建一个新的表，用于存储拆分后的行数据。
CREATE TABLE subjects (
id INT,
name VARCHAR(255),
subject VARCHAR(255)
);
步骤二：插入数据
然后，我们使用SUBSTRING_INDEX函数和LENGTH函数来动态拆分逗号分隔的列。
INSERT INTO subjects (id, name, subject)
SELECT id, name,
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(subjects, ',', numbers.n), ',', -1)) AS subject
FROM students
JOIN (
SELECT a.N + b.N * 10 + 1 AS n
FROM (SELECT 0 AS N UNION ALL SELECT 1 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
