将逗号分隔的列数据拆分为Mysql中的其他列？

Question

我在一列中有逗号分隔的数据：

Column 
------- 
a,b,c,d, n etc

我希望将逗号分隔的数据分割成多个列，以获得以下输出：

Column1  Column2 Column3 Column4 ColumnN 
-------  ------- ------- ------- -------
a        b       c       d       n

如何才能做到这一点？

使用Mysql 5.6.17

Answer 1

您可以编写自己的拆分函数来实现这一点。

CREATE FUNCTION SPLIT_STR( x VARCHAR(255), delim VARCHAR(12), pos INT )
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),delim, '');

Use可能像这样，第二个参数决定要从字符串中检索哪个位置的值。

SELECT
 SPLIT_STR(Column,',',1) AS a,
 SPLIT_STR(Column,',',2) AS b,
 SPLIT_STR(Column,',',3) AS c,
 SPLIT_STR(Column,',',4) AS d

Answer 2

您还可以使用这样的查询：

WITH RECURSIVE cte_count (n,m) 
AS (
      SELECT 1,3
      UNION ALL
      SELECT n + 1,n*n 
      FROM cte_count 
      WHERE n < 100
    )
SELECT TRIM( BOTH FROM SUBSTRING_INDEX( SUBSTRING_INDEX(mycol, ',', n) ,',',-1) )AS single
FROM myTable m
JOIN cte_count cnt
WHERE
cnt.n <= LENGTH(mycol) -LENGTH(REPLACE(m.mycol,',','')) +1

样本

MariaDB [test]> select * from myTable;
+----+----------------------------+
| id | mycol                      |
+----+----------------------------+
|  1 | a,b,c,d                    |
|  2 | Peter,Paul,Mary,john,David |
|  3 | one                        |
|  4 | one, two , three           |
+----+----------------------------+
4 rows in set (0.001 sec)

MariaDB [test]> WITH RECURSIVE cte_count (n,m) 
    -> AS (
    ->       SELECT 1,3
    ->       UNION ALL
    ->       SELECT n + 1,n*n 
    ->       FROM cte_count 
    ->       WHERE n < 100
    ->     )
    -> SELECT TRIM( BOTH FROM SUBSTRING_INDEX( SUBSTRING_INDEX(mycol, ',', n) ,',',-1) )AS single
    -> FROM myTable m
    -> JOIN cte_count cnt
    -> WHERE
    -> cnt.n <= LENGTH(mycol) -LENGTH(REPLACE(m.mycol,',','')) +1
    -> ;
+--------+
| single |
+--------+
| a      |
| b      |
| c      |
| d      |
| Peter  |
| Paul   |
| Mary   |
| john   |
| David  |
| one    |
| one    |
| two    |
| three  |
+--------+
13 rows in set (0.004 sec)

MariaDB [test]>