mysql逗号分隔拆多行|极客笔记

mysql逗号分隔拆多行

在实际工作中，我们经常会遇到需要将一行数据中的逗号分隔的内容拆分到多行的情况。比如在数据库表中，某个字段存储了多个值，这些值使用逗号进行分隔，我们希望将这些值分解成多行展示。本文将详细介绍如何使用MySQL来实现这一功能。

场景描述

假设我们有一个名为student的表，其中有一个字段interests存储了学生的兴趣爱好，这些兴趣爱好使用逗号进行分隔。我们希望将每个学生的兴趣爱好分解成多行展示，方便查看和分析。

解决方案

为了实现将逗号分隔的值拆分成多行，我们可以借助MySQL中的一些函数和操作符。下面是具体的实现步骤：

步骤1：创建示例表

首先，我们需要创建一个示例表student，并插入一些数据用于演示。可以通过以下SQL语句来创建表和插入数据：

CREATE TABLE student (
    id INT,
    name VARCHAR(50),
    interests VARCHAR(100)
);

INSERT INTO student (id, name, interests) VALUES
(1, 'Alice', 'Reading,Music'),
(2, 'Bob', 'Sports,Travel,Photography'),
(3, 'Cathy', 'Movies,Gaming');

步骤2：拆分逗号分隔的值

接下来，我们使用MySQL的SUBSTRING_INDEX函数和LENGTH函数来实现逗号分隔的值拆分。具体步骤如下：

SELECT id, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(interests, ',', n.digit+1), ',', -1) as interest
FROM student
JOIN
(SELECT 0 as digit UNION ALL
 SELECT 1 UNION ALL
 SELECT 2 UNION ALL
 SELECT 3) n
ON LENGTH(REPLACE(interests, ',' , '')) <= LENGTH(interests)-n.digit
ORDER BY id, n.digit;

上述SQL语句中，我们首先使用JOIN子查询生成一个数字序列n.digit，表示逗号拆分后的序号，然后通过SUBSTRING_INDEX和LENGTH函数来实现逗号分隔的值拆分，并最终实现将多个兴趣爱好拆分成多行展示的效果。

步骤3：运行SQL语句

最后，我们执行上述SQL语句，即可得到拆分后的结果。在我们的示例数据中，拆分结果如下：

+----+--------------+
| id | interest     |
+----+--------------+
|  1 | Reading      |
|  1 | Music        |
|  2 | Sports       |
|  2 | Travel       |
|  2 | Photography  |
|  3 | Movies       |
|  3 | Gaming       |
+----+--------------+

通过以上步骤，我们成功将逗号分隔的值拆分成多行展示，实现了我们的需求。