mysql逗号分割拆分成行

原创

mob649e8162c013 2023-11-16 18:40:34 ©著作权

©著作权归作者所有：来自51CTO博客作者mob649e8162c013的原创作品，请联系作者获取转载授权，否则将追究法律责任

Mysql逗号分割拆分成行

在数据库中，有时我们会遇到需要将一列以逗号分割的数据拆分成多行的情况。这种情况通常发生在数据规范化过程中，或是需要对某个字段进行分析和处理的时候。本文将介绍如何使用MySQL来实现将逗号分割的数据拆分成多行的方法。

1. 问题描述

假设我们有一个表格 employees，其中有一个字段 skills 存储了每个员工的技能，多个技能之间用逗号分割。现在我们需要将这些技能分割成多行，以便进行更方便的查询和分析。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  skills VARCHAR(200)
);

INSERT INTO employees (id, name, skills)
VALUES (1, 'John Doe', 'Java,Python,C++,SQL'),
       (2, 'Jane Smith', 'JavaScript,HTML,CSS'),
       (3, 'Bob Johnson', 'PHP,MySQL');

上述代码创建了一个 employees 表格，并插入了三条记录。其中 skills 列以逗号分割的格式存储了每个员工的技能。

2. 解决方法

为了将逗号分割的技能拆分成多行，我们可以使用MySQL内置的函数 SUBSTRING_INDEX 和 REPLACE。

具体步骤如下：

使用 SUBSTRING_INDEX 函数获取逗号分割的技能中的第一个技能；
使用 SUBSTRING_INDEX 函数将第一个技能从原字符串中移除；
重复上述两个步骤直到原字符串中不再包含逗号。

下面是实现上述步骤的MySQL代码：

SELECT id, name, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(skills, ',', num), ',', -1) AS skill
FROM employees
JOIN (
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    -- 可以根据实际情况增加更多的数字
) numbers
ON CHAR_LENGTH(skills) - CHAR_LENGTH(REPLACE(skills, ',', '')) >= num - 1
ORDER BY id, num;

上述代码中使用了一个子查询 numbers，它生成了一个数字序列，用于控制逗号分割的技能的拆分次数。通过 JOIN 连接 numbers 表和 employees 表，我们可以将每个员工的技能拆分成多行。

3. 示例结果

运行上述代码后，将得到以下结果：

| id | name        | skill       |
|----|-------------|-------------|
| 1  | John Doe    | Java        |
| 1  | John Doe    | Python      |
| 1  | John Doe    | C++         |
| 1  | John Doe    | SQL         |
| 2  | Jane Smith  | JavaScript |
| 2  | Jane Smith  | HTML        |
| 2  | Jane Smith  | CSS         |
| 3  | Bob Johnson | PHP         |
| 3  | Bob Johnson | MySQL       |

结果中每一行表示一个员工的一项技能。我们可以看到，通过将逗号分割的技能拆分成多行，我们可以更方便地对技能进行查询和分析。