MySQL按逗號拆分列為多行

一、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;

上述代碼將逗號分隔的字符串轉換為多列，每列存儲逗號分隔後的一個元素。