MySQL 一行数据全部分解为多行|极客笔记

MySQL 一行数据全部分解为多行

在数据库中，我们常常需要将一行数据拆解为多行。例如，当某个字段存储了多个值并使用分隔符进行了拼接时，我们需要将这些值拆分为多行记录，以方便进行查询和分析。本文将详细介绍如何将一行数据全部分解为多行，涵盖常见的场景和解决方法。

场景一：一对多的关系表

在关系型数据库中，常见的一对多关系可以通过外键关联两个表来表达。例如，我们有一个students表存储学生信息，其中的courses字段用逗号分隔表示学生所选的课程，如下所示：

+----+---------+-------------------+
| ID | Name    | Courses           |
+----+---------+-------------------+
| 1  | Alice   | Math, English, Art|
| 2  | Bob     | Physics, Chemistry|
| 3  | Charlie | Math, Biology     |
+----+---------+-------------------+

现在我们希望将每个学生以及他们选择的课程拆解成多行记录，来方便进行查询和分析。我们可以使用SUBSTRING_INDEX函数结合UNION操作来实现，具体步骤如下：

创建一个名为courses的临时表，用于存储拆分后的数据。
使用SUBSTRING_INDEX函数将Courses字段按照逗号拆分为多个子串。
使用UNION操作将每个子串作为一行插入到courses表中。
使用JOIN操作将students表和courses表进行关联，以获取每个学生对应的课程。

下面是具体的SQL代码：

-- 创建临时表
CREATE TABLE courses (
    ID INT,
    Course VARCHAR(255)
);

-- 将Courses字段拆分为多行
INSERT INTO courses (ID, Course)
SELECT ID, SUBSTRING_INDEX(SUBSTRING_INDEX(Courses, ',', n.digit+1), ',', -1) AS Course
FROM students
JOIN 
(
    SELECT 0 AS digit 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 SELECT 8 UNION ALL SELECT 9
) n
ON LENGTH(REPLACE(Courses, ',' , '')) <= LENGTH(Courses)-n.digit;

-- 查询结果
SELECT students.ID, students.Name, courses.Course
FROM students
JOIN courses ON students.ID = courses.ID
ORDER BY students.ID;

运行以上代码，可以得到以下结果：

+----+---------+------------+
| ID | Name    | Course     |
+----+---------+------------+
| 1  | Alice   | Math       |
| 1  | Alice   | English    |
| 1  | Alice   | Art        |
| 2  | Bob     | Physics    |
| 2  | Bob     | Chemistry  |
| 3  | Charlie | Math       |
| 3  | Charlie | Biology    |
+----+---------+------------+

通过将一行数据拆解为多行，我们可以更灵活地进行查询和分析，比如统计每门课程的选课人数、查找同时选择某两门课的学生等。

场景二：一对一的关系表

除了一对多的关系，有时我们还需要处理一对一的关系表。例如，我们有一个users表存储用户信息，其中的address字段存储了多个地址，以逗号分隔，如下所示：

+----+---------+-----------------------------------+
| ID | Name    | Address                           |
+----+---------+-----------------------------------+
| 1  | Alice   | 123 Main St, Apt 4, City1, State1 |
| 2  | Bob     | 456 Oak St, City2, State2         |
| 3  | Charlie | 789 Maple Dr, City3, State3       |
+----+---------+-----------------------------------+

我们希望将每个用户的地址拆解为多行记录，方便查询和分析。可以使用SUBSTRING_INDEX函数结合UNION和REPLACE操作来实现，具体步骤如下：

创建一个名为addresses的临时表，用于存储拆分后的数据。
使用SUBSTRING_INDEX函数将Address字段按照逗号拆分为多个子串。
使用UNION操作将每个子串作为一行插入到addresses表中。
使用JOIN操作将users表和addresses表进行关联，以获取每个用户对应的地址。

下面是具体的SQL代码：

-- 创建临时表
CREATE TABLE addresses (
    ID INT,
    Address VARCHAR(255)
);

-- 将Address字段拆分为多行
INSERT INTO addresses (ID, Address)
SELECT ID, REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(Address, ',', n.digit+1), ',', -1), ' ', '') AS Address
FROM users
JOIN 
(
    SELECT 0 AS digit 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 SELECT 8 UNION ALL SELECT 9
) n
ON LENGTH(REPLACE(Address, ',' , '')) <= LENGTH(Address)-n.digit;

-- 查询结果
SELECT users.ID, users.Name, addresses.Address
FROM users
JOIN addresses ON users.ID = addresses.ID
ORDER BY users.ID;

运行以上代码，可以得到以下结果：

+----+---------+-----------------------------+
| ID | Name    | Address                     |
+----+---------+-----------------------------+
| 1  | Alice   | 123MainSt                   |
| 1  | Alice   | Apt4                        |
| 1  | Alice   | City1                       |
| 1  | Alice   | State1                      |
| 2  | Bob     | 456OakSt                    |
| 2  | Bob     | City2                       |
| 2  | Bob     | State2                      |
| 3  | Charlie | 789MapleDr                  |
| 3  | Charlie | City3                       |
| 3  | Charlie | State3                      |
+----+---------+-----------------------------+

通过将一行数据拆解为多行，我们可以更方便地进行地址的搜索和分析，比如按照城市统计用户数、查找拥有某个特定地址的用户等。