Mysql一个字段的值是“1，2，3，4，5，6”，怎么分别取出用逗号分割的这些值？

Question

比如我要分别取出1，2，3。
要怎么写？

Answer 1

您可以使用MySQL内置函数SUBSTRING_INDEX()来实现这个目标。

具体步骤如下：

使用SUBSTRING_INDEX()函数将逗号分割的值转换为多行。

SELECT SUBSTRING_INDEX('1,2,3,4,5,6', ',', 1) AS val
UNION ALL SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5,6', ',', 2), ',', -1)
UNION ALL SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5,6', ',', 3), ',', -1)
-- add more UNION ALL SELECT statements for additional values as needed

这将返回：

+------+
| val  |
+------+
| 1    |
| 2    |
| 3    |
+------+

---

以上内容来自 ChatGPT，有修改

实际测试：

mysql> SELECT SUBSTRING_INDEX('1,2,3,4,5,6', ',', 1) AS val
    -> UNION ALL SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5,6', ',', 2), ',', -1)
    -> UNION ALL SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5,6', ',', 3), ',', -1)
    -> -- add more UNION ALL SELECT statements for additional values as needed
    -> ;
+------+
| val  |
+------+
| 1    |
| 2    |
| 3    |
+------+
3 rows in set (0.00 sec)

mysql> 

Answer 2

SQL

SELECT *
FROM JSON_TABLE(
       CONCAT('[', '1, 2, 3, 4, 5, 6', ']'),
       '$[*]' COLUMNS(num INT PATH '$')
     ) AS jt
WHERE num <= 3;

结果

num
1
2
3