MySQL按逗号拆分列为多行

小蓝 • 2025-01-06 15:17 • 编程

一、SQL按逗号拆分列为多行

在实际工作中，我们经常会遇到将一列中的逗号分隔的字符串切分为多行的需求，这个需求在MySQL中可以使用正则表达式实现。

SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', n), ',', -1) value
FROM
  (SELECT 'A,B,C,D,E' str) t 
  INNER JOIN
  (SELECT 1 n UNION ALL SELECT 2 
   UNION ALL SELECT 3 UNION ALL SELECT 4 
   UNION ALL SELECT 5) nums 
  ON LENGTH(REPLACE(str, ',' , '')) <= LENGTH(str) - nums.n + 1;

上面的查询语句中，我们首先使用SUBSTRING_INDEX和UNION ALL拆分了1-5行的数据，得到n表示逗号前面有n个元素，然后使用SUBSTRING_INDEX和正则表达式拆分每个元素，并使用内连接将切分后的值取出来。

二、MySQL按逗号分割字符串

MySQL提供了内置函数SUBSTRING_INDEX来实现字符串的切割，使用它可以轻松地实现对逗号分隔的字符串的切割。

SELECT SUBSTRING_INDEX('A,B,C,D,E', ',', 1) as first,
       SUBSTRING_INDEX(SUBSTRING_INDEX('A,B,C,D,E', ',', 2), ',', -1) as second,
       SUBSTRING_INDEX(SUBSTRING_INDEX('A,B,C,D,E', ',', 3), ',', -1) as third,
       SUBSTRING_INDEX(SUBSTRING_INDEX('A,B,C,D,E', ',', 4), ',', -1) as fourth,
       SUBSTRING_INDEX(SUBSTRING_INDEX('A,B,C,D,E', ',', 5), ',', -1) as fifth;

上述代码中，我们按照逗号切割了逗号隔开的字符串，并通过SUBSTRING_INDEX取出每个逗号前面的数据。

三、MySQL逗号分割拆多行

使用MySQL的内置函数实现将逗号拆分为多行的方法，也是使用内置函数SUBSTRING_INDEX，使用UNION ALL，可以生成需要的行数。

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('A,B,C,D,E', ',', n), ',', -1) AS value
FROM (
    SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 
    UNION ALL SELECT 4 UNION ALL SELECT 5
) num
WHERE n <= LENGTH('A,B,C,D,E') - LENGTH(REPLACE('A,B,C,D,E', ',', '')) + 1;

上述代码将每个逗号前面的数据拆分成一行，并通过UNION ALL生成相应的行数，从而实现逗号分割拆多行的功能。

四、MySQL按逗号拆分为数组

在MySQL中，可以使用SUBSTRING_INDEX将逗号分割的字符串转换为数组类型的数据。

SELECT JSON_ARRAYAGG(
    SUBSTRING_INDEX(
        SUBSTRING_INDEX('A,B,C,D,E', ',', nums.n), ',', -1)
    ) AS splitted
FROM (
    SELECT 1 AS n
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
) nums
WHERE LENGTH(REPLACE('A,B,C,D,E', ',', '')) <= LENGTH('A,B,C,D,E') - nums.n + 1;

上述代码中，我们使用SUBSTRING_INDEX将逗号分割的字符串转换为数组，并使用JSON_ARRAYAGG将数组拼接成为一个完整的数组。

五、MySQL按逗号拆分列为多列

使用MySQL内置函数SUBSTRING_INDEX可以将逗号拆分列为多个列。

SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX('A,B,C,D,E', ',', 1), ',', -1) AS col1,
    SUBSTRING_INDEX(SUBSTRING_INDEX('A,B,C,D,E', ',', 2), ',', -1) AS col2,
    SUBSTRING_INDEX(SUBSTRING_INDEX('A,B,C,D,E', ',', 3), ',', -1) AS col3,
    SUBSTRING_INDEX(SUBSTRING_INDEX('A,B,C,D,E', ',', 4), ',', -1) AS col4,
    SUBSTRING_INDEX(SUBSTRING_INDEX('A,B,C,D,E', ',', 5), ',', -1) AS col5;

上述代码将逗号分隔的字符串转换为多列，每列存储逗号分隔后的一个元素。

原创文章，作者：小蓝，如若转载，请注明出处：https://www.506064.com/n/312751.html