MySQL逗号隔开拆成多条数据|极客笔记

MySQL逗号隔开拆成多条数据

在MySQL中，有时候我们会遇到一些数据存储在一个字段中，用逗号隔开的情况。这种数据格式不利于数据分析和查询，需要将其拆分成多条数据方便处理。本文将介绍如何使用MySQL来将逗号隔开的数据拆分成多条记录。

场景描述

假设我们有一个名为user的表，其中有一个字段interests存储了用户的兴趣爱好，而这些兴趣爱好是用逗号隔开的，如下所示：

id	name	interests
1	张三	游泳,健身,读书
2	李四	电影,旅行,美食
…	…	…

现在我们希望将interests字段中的数据拆分成多条记录，使得每个兴趣爱好对应一条记录，如下所示：

id	name	interest
1	张三	游泳
1	张三	健身
1	张三	读书
2	李四	电影
2	李四	旅行
2	李四	美食
…	…	…

解决方案

为了达到上述的效果，我们可以通过MySQL中的一些函数来实现。具体步骤如下：

步骤1：创建一个临时表

首先，我们创建一个临时表tmp_user，用于存储拆分后的数据：

CREATE TEMPORARY TABLE tmp_user (
    id INT,
    name VARCHAR(50),
    interest VARCHAR(50)
);

步骤2：将逗号隔开的数据拆分成多条记录

接下来，我们可以使用SUBSTRING_INDEX函数和LENGTH函数来拆分interests字段中的数据。具体步骤如下：

INSERT INTO tmp_user (id, name, interest)
SELECT 
    id,
    name,
    SUBSTRING_INDEX(SUBSTRING_INDEX(interests, ',', n), ',', -1) AS interest
FROM
    user
JOIN
    (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) numbers
ON 
    CHAR_LENGTH(interests) - CHAR_LENGTH(REPLACE(interests, ',', '')) >= n - 1;

在上面的SQL语句中，我们首先使用JOIN操作将用户表user和一个包含数字的临时表numbers联系起来，其中numbers表中包含了我们需要的拆分次数。然后，我们通过SUBSTRING_INDEX函数和LENGTH函数来实现将逗号隔开的数据拆分并插入到临时表tmp_user中。