mysql根据逗号列转行

原创

mob64ca12d5dd85 2024-02-10 08:20:18 ©著作权

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

MySQL根据逗号列转行

在处理数据时，我们经常会遇到需要将一列数据按照逗号分隔转换成多行数据的情况。MySQL提供了一种简单而高效的方法，可以帮助我们轻松地实现这一转换。本文将介绍如何使用MySQL将逗号列转换为多行，并提供相应的代码示例。

背景

在某些情况下，我们可能会遇到这样的数据结构：一列中包含了多个值，这些值之间由逗号进行分隔。例如，我们有一个包含用户标签的表，每个用户可能有多个标签，标签之间用逗号隔开。这种情况下，如果我们想要对每个标签进行分析或者进行关联查询，那么将逗号列转换为多行会更加方便和有效。

解决方案

MySQL提供了一种函数FIND_IN_SET，可以帮助我们实现逗号列转行的功能。该函数的语法如下：

FIND_IN_SET(str, strlist)

其中，str是要查找的字符串，strlist是逗号分隔的字符串列表。

我们可以使用该函数将逗号列转换为多行。下面是一个具体的例子。假设我们有一个名为users的表，其中包含了用户ID和用户标签的列：

ID	Tags
1	A,B,C
2	A,D
3	B,C

我们可以使用下面的代码示例来实现逗号列转行：

SELECT ID, SUBSTRING_INDEX(SUBSTRING_INDEX(Tags, ',', n.digit+1), ',', -1) as Tag
FROM users
CROSS JOIN 
(
  SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
) n
WHERE n.digit < LENGTH(Tags)-LENGTH(REPLACE(Tags, ',', ''))+1
ORDER BY ID, Tag;

在上面的代码中，我们使用了两个嵌套的SUBSTRING_INDEX函数来实现逗号列的转换。首先，SUBSTRING_INDEX(Tags, ',', n.digit+1)会将逗号列按照逗号进行分隔，然后取出第n.digit+1个分隔后的子字符串。接着，SUBSTRING_INDEX(..., ',', -1)会从上一步得到的子字符串中取出最后一个逗号分隔的子字符串，也就是我们想要的结果。

为了使代码适用于不同的逗号分隔的个数，我们使用了一个CROSS JOIN来构建一个临时表n，其中包含了从0到逗号分隔个数减一的数字。通过WHERE条件n.digit < LENGTH(Tags)-LENGTH(REPLACE(Tags, ',', ''))+1，我们可以实现根据逗号个数来生成对应的行数。

最后，我们使用ORDER BY对结果进行排序，以确保每个用户的标签按照字母顺序排列。

示例运行

我们可以通过以下示例来验证上述代码的运行结果。

CREATE TABLE users (
  ID INT,
  Tags VARCHAR(255)
);

INSERT INTO users (ID, Tags)
VALUES (1, 'A,B,C'),
       (2, 'A,D'),
       (3, 'B,C');

SELECT ID, SUBSTRING_INDEX(SUBSTRING_INDEX(Tags, ',', n.digit+1), ',', -1) as Tag
FROM users
CROSS JOIN 
(
  SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
) n
WHERE n.digit < LENGTH(Tags)-LENGTH(REPLACE(Tags, ',', ''))+1
ORDER BY ID, Tag;

运行以上示例后，我们将得到如下结果：