在MySQL中将逗号分隔的字符串拆分为行

Question

当我有类似于1, 2, 3...的字符串列表时，我希望将其用作一个列。

Ids
1
2
3

能否通过 SQL 查询实现？

例如：SELECT Ids from (1, 2, 3...) <- 我知道这个不起作用。

- Bewan

---

可能是MySQL中GROUP_CONCAT的相反操作是什么？的重复问题。 - philipxy

Answer 1

使用任意数字的子查询来拆分您的字符串。您可以使用'1,2,3'代替vals。

SELECT
  DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(vals, ',', n.digit+1), ',', -1) val
FROM
  tt1
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
  ON LENGTH(REPLACE(vals, ',' , '')) <= LENGTH(vals)-n.digit;

查看它的工作示例

- Mihai

---

抱歉，我有一个问题要问。如果字符串的长度不固定，是否可以进行转换？ - Bewan

非常类似于这个答案。 - Steve Chambers

Answer 2

对于MySQL 8.0.4+

SELECT *
FROM
  JSON_TABLE(
          CONCAT('[', '1,2,3,4', ']'),
          "$[*]"
          COLUMNS(
              ids BIGINT(20) PATH "$"
              )
      ) AS tt

在你的字符串周围加上方括号（[]），以将其转换为JSON数组。然后使用JSON_TABLE将其转换为表格。有关更多信息，请参见MySQL JSON Table Functions。

- Dmitry

Answer 3

您可以使用以下存储过程来拆分由任何字符分隔的字符串：

CREATE PROCEDURE `split_delimited` (
IN inputstr NVARCHAR(1000),
IN delimiter CHAR(1)
)
BEGIN
DROP TEMPORARY TABLE Items;
CREATE TEMPORARY TABLE Items(item NVARCHAR(50)); 
WHILE LOCATE(delimiter,inputstr) > 1 DO
INSERT INTO Items SELECT SUBSTRING_INDEX(inputstr,delimiter,1);
SET inputstr = REPLACE (inputstr, (SELECT LEFT(inputstr,LOCATE(delimiter,inputstr))),'');
END WHILE;
INSERT INTO Items(item) VALUES(inputstr);
select * from Items;
END

输入：'a，b，c' 输出：a b c

- singhk

Answer 4

注意，这个答案可能与提问时可用的发布版本的MySQL不兼容。我为那些有兴趣使用递归CTE方法的人添加了此选项以供将来参考。此外，还添加了不规则数据以测试异常处理。

create table test(ids varchar(50));
insert test values('1,2,3');

-- Note: the next_start_pos is the position to the immediate right of the comma.

with recursive cte as
    (select locate(',',ids,1)+1 as next_start_pos,
    substring(ids,1,locate(',',ids,1)-1) as i
    from test 
    union
    select locate(',',t.ids,next_start_pos)+1 ,
    case when locate(',',t.ids,next_start_pos) !=0 then substring(t.ids,next_start_pos,locate(',',t.ids,next_start_pos)-next_start_pos)
    else  substring(t.ids,next_start_pos) end 
    from test t join cte c
    where next_start_pos!=1
    )
select next_start_pos, i from cte where i !='';

+----------------+------+
| next_start_pos | i    |
+----------------+------+
|              3 | 1    |
|              5 | 2    |
|              1 | 3    |
+----------------+------+

请注意，在查询cte结果表的主查询中，应包含where i !=''作为一个防错功能。它可以避免由于前导/尾随逗号或连续逗号而导致的空格结果。以下面的案例为例：

truncate test;
insert test values(',,,1,,2,3,,,,'); 

-- run the same query again and we get:
+----------------+------+
| next_start_pos | i    |
+----------------+------+
|              6 | 1    |
|              9 | 2    |
|             11 | 3    |
+----------------+------+

-- if the WHERE clause is removed, then we get:
+----------------+------+
| next_start_pos | i    |
+----------------+------+
|              2 |      |
|              3 |      |
|              4 |      |
|              6 | 1    |
|              7 |      |
|              9 | 2    |
|             11 | 3    |
|             12 |      |
|             13 |      |
|             14 |      |
|              1 |      |
+----------------+------+

- blabla_bingo