mysql怎么将字符串通过逗号分割
时间: 2023-07-16 18:16:17 浏览: 52
可以使用MySQL内置函数`SUBSTRING_INDEX()`和`GROUP_CONCAT()`来将字符串通过逗号分割。
假设有一个字符串`'a,b,c,d,e'`,要将其通过逗号分割成五个部分,可以使用以下语句:
```
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d,e', ',', 1), ',', -1) AS part1,
SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d,e', ',', 2), ',', -1) AS part2,
SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d,e', ',', 3), ',', -1) AS part3,
SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d,e', ',', 4), ',', -1) AS part4,
SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d,e', ',', 5), ',', -1) AS part5;
```
结果为:
```
+-------+-------+-------+-------+-------+
| part1 | part2 | part3 | part4 | part5 |
+-------+-------+-------+-------+-------+
| a | b | c | d | e |
+-------+-------+-------+-------+-------+
```
如果要将多个字符串进行分割并合并成一个字符串,可以使用`GROUP_CONCAT()`函数,例如:
```
SELECT GROUP_CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d,e', ',', n), ',', -1) SEPARATOR ',') AS result
FROM (
SELECT 1 AS n UNION ALL
SELECT 2 AS n UNION ALL
SELECT 3 AS n UNION ALL
SELECT 4 AS n UNION ALL
SELECT 5 AS n
) AS nums;
```
结果为:
```
+----------------+
| result |
+----------------+
| a,b,c,d,e |
+----------------+
```
其中,`nums`是一个内部表,用于展开`n`的值,从而将字符串分割成多个部分。`SEPARATOR ','`表示使用逗号作为分隔符将多个部分合并成一个字符串。