MySQL SQL 切割逗号分隔的行

在实际开发工作中，我们常常需要处理包含逗号分隔符的字符串数据。例如，某表的某个字段存储了多个值，这些值用逗号分隔开来，我们需要将它们拆分成多行或者进行一些其他的操作。本文将介绍一些 MySQL SQL 切割逗号分隔的行的方法和技巧。

阅读更多：MySQL 教程

方法一：使用 SUBSTRING_INDEX 函数

SUBSTRING_INDEX 函数可以从字符串数据中截取指定数量的子串，根据指定的分隔符将字符串分隔成多个子串，然后返回这些子串中的其中一个或者全部。以下是该函数的语法：

SUBSTRING_INDEX(str,delim,count)

其中，str 是需要截取的字符串，delim 是分隔符，count 是截取的子串数量，当 count 为正数时，函数会从左到右开始截取，从右到左开始截取。

例如，假设有如下表格：

id	name
1	John,Michael,William
2	Mary,Kate,Sandra
3	Tom,Lucy

我们想将 name 字段拆分成多行，以下是使用 SUBSTRING_INDEX 函数的 SQL 语句：

SELECT
   SUBSTRING_INDEX(name,',',1) AS name1,
   SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',2),',',-1) AS name2,
   SUBSTRING_INDEX(SUBSTRING_INDEX(name,',',3),',',-1) AS name3
FROM
   students;

运行结果如下：

name1	name2	name3
John	Michael	William
Mary	Kate	Sandra
Tom	Lucy	NULL

在上述 SQL 语句中，我们使用了 SUBSTRING_INDEX 函数将 name 字段逗号分隔成了多个子串，然后用 AS 子句重新定义了每个子串的列名。需要注意的是，当某行内的子串数量不足以匹配该 SQL 语句中所指定的子串数量时，返回值为 NULL 。

方法二：使用 FIND_IN_SET 函数

FIND_IN_SET 函数可以在指定的列表中查找与指定字符串相匹配的元素，并返回它的位置。以下是该函数的语法：

FIND_IN_SET(str,strlist)

其中，str 是需要查找的字符串，strlist 是以逗号分隔的列表字符串。

例如，对于上述表格和数据，以下是使用 FIND_IN_SET 函数的 SQL 语句：

SELECT
   FIND_IN_SET('John',name) AS pos1,
   FIND_IN_SET('Michael',name) AS pos2,
   FIND_IN_SET('William',name) AS pos3
FROM
   students;

运行结果如下：

pos1	pos2	pos3
1	2	3
0	0	0
0	0	0

在上述 SQL 语句中，我们使用了 FIND_IN_SET 函数在 name 字段中查找指定的子串，然后返回它的位置。如果找不到，则返回 0 。

方法三：使用 REGEXP 和 SUBSTRING 函数

REGEXP 函数有效地使用正则表达式来查找匹配的字符串，SUBSTRING 函数则将字符串的指定部分提取出来。结合使用这两个函数，我们可以用较为复杂的正则表达式来处理较为复杂的数据。

例如，我们假设有如下表格：

id	keywords
1	apple,tree,forest,garden,road,car,taxi,traffic
2	love,dating,romance,music,movies,travelling,food

我们想将 keywords 字段拆分成多行，并只保留以字母 “a” 开头的关键字，以下是使用 REGEXP 函数和 SUBSTRING 函数的 SQL 语句：

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(keywords,',',t.n),',',-1) AS keyword
FROM
   keywords
JOIN (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t
ON CHAR_LENGTH(keywords)-CHAR_LENGTH(REPLACE(keywords,',',''))+1 >= t.n
WHERE
   SUBSTRING_INDEX(SUBSTRING_INDEX(keywords,',',t.n),',',-1) REGEXP '^a';

运行结果如下：

keyword
apple
road

在上述 SQL 语句中，我们使用了 REGEXP 函数和 SUBSTRING_INDEX 函数将 keywords 字段进行了拆分，并且只保留以字母 “a” 开头的关键字，同时使用了 JOIN 子句和派生表来模拟循环操作，其中 t 是一个包含数字 1 至 5 的临时表。该 SQL 语句的复杂度较高，但是处理的是较为复杂的数据，适合在实际开发中使用。