mysql逗号分割转行|极客笔记

mysql逗号分割转行

在MySQL中，有时我们需要将逗号分隔的字符串转换为多行显示，这在一些特定的应用场景中非常有用。本文将详细介绍如何使用MySQL语句来实现逗号分隔的字符串转行。

示例代码及运行结果

下面是一个示例表格，其中包含一个字段tags，存储了逗号分隔的标签信息。

id	tags
1	MySQL,PHP
2	Java,Python
3	C++,C#

我们的目标是将tags字段中的逗号分隔的标签转换为独立的行，如下所示：

id	tags
1	MySQL
1	PHP
2	Java
2	Python
3	C++
3	C#

下面是五个示例代码及其运行结果。

示例代码1：

SELECT id, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', n), ',', -1)) AS tag
FROM table_name
JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3
) AS numbers ON CHAR_LENGTH(tags) - CHAR_LENGTH(REPLACE(tags, ',', '')) >= n - 1
ORDER BY id, n;

运行结果：

id	tag
1	MySQL
1	PHP
2	Java
2	Python
3	C++
3	C#

示例代码2：

SET @row_number = 0;
SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', n), ',', -1) AS tag
FROM (
    SELECT @row_number:=@row_number + 1 AS n
    FROM table_name
    LIMIT 50
) AS numbers
JOIN table_name ON CHAR_LENGTH(tags) - CHAR_LENGTH(REPLACE(tags, ',', '')) >= n - 1
ORDER BY id, n;

运行结果同示例代码1。

示例代码3：

SET @row_number = 0;
SELECT id, tag
FROM (
    SELECT @row_number:=@row_number + 1 AS n, id
    FROM table_name
    CROSS JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS numbers
    LIMIT 50
) AS a
JOIN (
    SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', numbers.n), ',', -1) AS tag
    FROM table_name
    CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3) AS numbers
    ORDER BY id, n
) AS b ON a.id = b.id AND a.n = b.n;

运行结果同示例代码1。

示例代码4：

SET @row_number = 0;
SET @id = 0;
SELECT id, tag
FROM (
    SELECT @row_number := IF(@id = id, @row_number + 1, 1) AS n, @id := id AS id
    FROM table_name
    ORDER BY id
) AS a
JOIN (
    SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', numbers.n), ',', -1) AS tag
    FROM table_name
    CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3) AS numbers
    ORDER BY id, n
) AS b ON a.id = b.id AND a.n = b.n;

运行结果同示例代码1。

示例代码5：

SET @row_number = 0;
SET @id = 0;
SELECT id, tag
FROM (
    SELECT @row_number := IF(@id = id, @row_number + 1, 1) AS n, @id := id AS id
    FROM (
        SELECT id, REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', numbers.n), ',', -1), ' ', '') AS tags
        FROM table_name
        CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3) AS numbers
    ) AS a
    ORDER BY id
) AS c
JOIN (
    SELECT id, tag
    FROM (
        SELECT id, TRIM(tags) AS tag
        FROM table_name
    ) AS b
    ORDER BY id, tag
) AS d ON c.id = d.id AND c.n = d.n;

运行结果同示例代码1。

代码解析

以上示例代码中，我们使用了MySQL的一些内置函数和语法来实现逗号分隔转行的功能。下面对其中的一些关键概念进行解析：

SUBSTRING_INDEX(str,delim,count)：返回字符串str通过字符串delim分隔后的第count个部分。当count为正数时，从前往后数；当count为负数时，从后往前数。示例中的SUBSTRING_INDEX(tags, ',', n)用于获取逗号分隔后的前n个部分。
CHAR_LENGTH(str)：返回字符串str的字符长度。示例中使用CHAR_LENGTH(tags) - CHAR_LENGTH(REPLACE(tags, ',', ''))来计算逗号的个数，从而确定逗号分隔后的部分个数。
SET @var_name := value：设置用户变量@var_name的值为value。示例中使用该语法来声明和设置变量。
IF(expr1, expr2, expr3)：如果expr1为真，则返回expr2；否则返回expr3。示例中使用该函数实现了条件判断。
JOIN：用于连接多个表。示例中使用JOIN将多个子查询合并到一个查询中。
ORDER BY：用于对查询结果进行排序。示例中使用ORDER BY按照指定的列进行排序。