MySQL 将一行中使用逗号分隔符的值拆分成多行

在MySQL中，我们有时需要将一行中使用逗号分隔符的值拆分成多行。例如，我们可能需要将某个表中的一列的值拆分为多个值，这些值可以被用于另一个表中的行。例如，我们可能有一个名为“社交媒体”的表，其中包含用户id和他们喜欢的社交媒体网站，网站使用逗号分隔。现在我们需要创建一个新表，其中每行都表示用户和其所喜欢的社交媒体网站之一。

为了实现这一点，我们需要编写一个MySQL查询，声明如下：

SELECT user_id, social_media 
FROM (SELECT user_id, SUBSTRING_INDEX(SUBSTRING_INDEX(social_media , ',', n.digit+1), ',', -1) social_media 
FROM `social_media`, 
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n 
WHERE n.digit < LENGTH(social_media) - LENGTH(REPLACE(social_media, ',', ''))+1) x;

在以下示例中，我们将显式解释我们所做的事情。让我们首先创建一个名为social_media的表，并使用下面的数据填充它。

CREATE TABLE `social_media` (
  `id` int(11) NOT NULL,
  `user_id` varchar(50) NOT NULL,
  `social_media` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `social_media` (`id`, `user_id`, `social_media`) VALUES
(1, 'user1', 'facebook, twitter, instagram'),
(2, 'user2', 'whatsapp, wechat, telegram'),
(3, 'user3', 'whatsapp, facebook'),
(4, 'user4', 'twitter, facebook, tiktok, linkedin'),
(5, 'user5', 'twitter, pinterest, linkedin, facebook');

现在，我们需要将上表中的值拆分为多个行，如下所示：

user1 | facebook
user1 | twitter
user1 | instagram
user2 | whatsapp
user2 | wechat
user2 | telegram
user3 | whatsapp
user3 | facebook
user4 | twitter
user4 | facebook
user4 | tiktok
user4 | linkedin
user5 | twitter
user5 | pinterest
user5 | linkedin
user5 | facebook

现在，我们来看看上面的SQL查询中都做了些什么。

步骤一：查询“social_media”表中的“用户ID”和“Social_Media”列的值。

SELECT user_id, social_media FROM `social_media`;

结果：

+--------+---------------------------------+
| user_id| social_media                    |
+--------+---------------------------------+
| user1  | facebook, twitter, instagram   |
| user2  | whatsapp, wechat, telegram     |
| user3  | whatsapp, facebook             |
| user4  | twitter, facebook, tiktok, linkedin|
| user5  | twitter, pinterest, linkedin, facebook |
+--------+---------------------------------+

步骤二：使用SUBSTRING_INDEX()函数将“Social_Media”列的值拆分成逗号分隔的值。

SUBSTRING_INDEX()函数返回在输入字符串中出现指定数目的子字符串。如果在输入字符串中未找到该子字符串，则返回整个输入字符串。

以下是SUBSTRING_INDEX()函数的语法：

SUBSTRING_INDEX(input_string, delimiter, count);

参数解释如下：

input_string：需要被拆分的输入字符串。
delimiter：分隔符。
count：需要返回的出现次数。如果为正数，则返回的是输入字符串的前count个出现的分隔符之后的子串。如果为负数，则返回的是输入字符串的后count个出现的分隔符之前的子串。

在我们的例子中，我们希望将逗号分隔的行中的每个值拆分为单独的行。因此，我们的分隔符将是逗号，count参数将是一个数字序列的数量。我们可以通过使用LENGTH()和REPLACE()函数来获取逗号分隔的字符串中的数字序列数量。

以下是从输入字符串中获取数字序列数量并将其添加到子查询中以获取结果的代码：

(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n 
WHERE n.digit < LENGTH(social_media) - LENGTH(REPLACE(social_media, ',', ''))+1

此代码段返回一个名为“n”的虚拟表，其中包括ID为0、1、2、3的四个数字序列，这些数字序列用于生成查询结果中的每个输出行。

步骤三：使用SUBSTRING_INDEX()函数和SUBSTRING()函数来获取逗号分隔的值，同时将其拆分为多行。

SUBSTRING_INDEX(SUBSTRING_INDEX(social_media , ',', n.digit+1), ',', -1) social_media

上面的语句首先使用SUBSTRING_INDEX()函数查找Social_Media列中第n个逗号之前的子字符串，并返回该子字符串。之后，它使用另一个SUBSTRING_INDEX()函数查找该子字符串中最后一个逗号之后的子字符串，并返回该子字符串。因此，这会将包含逗号分隔值的字符串拆分为单独的值，并用逗号隔开。

这就是我们如何将“Social_Media”列中的多个值拆分为多行的全部内容。现在，让我们来看看完整的SQL查询及其结果。

SELECT user_id, social_media 
FROM (SELECT user_id, SUBSTRING_INDEX(SUBSTRING_INDEX(social_media , ',', n.digit+1), ',', -1) social_media 
FROM `social_media`, 
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n 
WHERE n.digit < LENGTH(social_media) - LENGTH(REPLACE(social_media, ',', ''))+1) x;

结果：

+--------+--------------+
| user_id| social_media |
+--------+--------------+
| user1  | facebook     |
| user1  | twitter      |
| user1  | instagram    |
| user2  | whatsapp     |
| user2  | wechat       |
| user2  | telegram     |
| user3  | whatsapp     |
| user3  | facebook     |
| user4  | twitter      |
| user4  | facebook     |
| user4  | tiktok       |
| user4  | linkedin     |
| user5  | twitter      |
| user5  | pinterest    |
| user5  | linkedin     |
| user5  | facebook     |
+--------+--------------+

阅读更多：MySQL 教程