MySQL 逗号分隔转多行|极客笔记

MySQL 逗号分隔转多行

在实际的数据库操作中，我们有时会遇到需要将一列数据中以逗号分隔的值转换成多行的需求。这种需求可能是为了方便后续的数据分析、处理或展示。在MySQL中，我们可以使用一些内置的函数来实现这个功能。

场景描述

假设有一张名为books的表，其中有一列categories存储了每本书的分类信息，数据格式如下：

CREATE TABLE books (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    categories VARCHAR(100)
);

INSERT INTO books VALUES (1, 'Book1', 'Fiction,Sci-Fi');
INSERT INTO books VALUES (2, 'Book2', 'Non-Fiction,History');
INSERT INTO books VALUES (3, 'Book3', 'Self-Help');

我们希望将categories列以逗号分隔的值转换成多行，即将上述数据转换成如下形式：

1, Book1, Fiction
1, Book1, Sci-Fi
2, Book2, Non-Fiction
2, Book2, History
3, Book3, Self-Help

解决方法

使用内置函数`SUBSTRING_INDEX`和`LENGTH`

MySQL提供了SUBSTRING_INDEX函数来获取字符串中指定分隔符之前或之后的子串，结合LENGTH函数可以循环解析逗号分隔的值。以下是实现转换的SQL语句：

SELECT 
    id, 
    name, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(categories, ',', n.digit+1), ',', -1) AS category
FROM books
JOIN 
(
    SELECT 0 AS digit UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
) n
ON LENGTH(REPLACE(categories, ',' , '')) <= LENGTH(categories)-n.digit
ORDER BY id, n.digit

运行以上SQL语句后，就可以将逗号分隔的categories列转换成多行的形式。

示例代码及运行结果

SELECT 
    id, 
    name, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(categories, ',', n.digit+1), ',', -1) AS category
FROM books
JOIN 
(
    SELECT 0 AS digit UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
) n
ON LENGTH(REPLACE(categories, ',' , '')) <= LENGTH(categories)-n.digit
ORDER BY id, n.digit;

运行结果如下所示：

+----+-------+-------------+
| id | name  | category    |
+----+-------+-------------+
|  1 | Book1 | Fiction     |
|  1 | Book1 | Sci-Fi      |
|  2 | Book2 | Non-Fiction |
|  2 | Book2 | History     |
|  3 | Book3 | Self-Help   |
+----+-------+-------------+