68.日常问题整理[2023/04/06]sql在逗号分隔的字符串列表中查找指定字符串的位置

MySQL FIND_IN_SET函数

MySQL提供了一个名为FIND_IN_SET()的内置字符串函数，允许您在逗号分隔的字符串列表中查找指定字符串的位置。

下面说明了FIND_IN_SET()函数的语法。

FIND_IN_SET(needle,haystack);

SQL

FIND_IN_SET()函数接受两个参数：

• 第一个参数needle是要查找的字符串。
• 第二个参数haystack是要搜索的逗号分隔的字符串列表。

FIND_IN_SET()函数根据参数的值返回一个整数或一个NULL值：

• 如果needle或haystack为NULL，则函数返回NULL值。
• 如果needle不在haystack中，或者haystack是空字符串，则返回零。
• 如果needle在haystack中，则返回一个正整数。

请注意，如果needle包含逗号(，)，该函数将无法正常工作。 此外，如果needle是一个常量字符串，而且haystack是一个类型为SET的列，MySQL将使用位算术优化。

---

MySQL FIND_IN_SET示例

下面让我们举一些例子来了解和学习MySQL FIND_IN_SET的工作原理。

MySQL FIND_IN_SET：简单的例子

以下语句返回2，因为字符'y'是'x，y，z'字符串中的第二个位置。

mysql> SELECT FIND_IN_SET('y','x,y,z');
+--------------------------+
| FIND_IN_SET('y','x,y,z') |
+--------------------------+
|                        2 |
+--------------------------+
1 row in set

SQL

以下语句返回0，因为'a'不在'x，y，z'字符串列表中。

mysql> SELECT FIND_IN_SET('a','x,y,z');
+--------------------------+
| FIND_IN_SET('a','x,y,z') |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set

SQL

以下语句也返回0，因为第二个参数是一个空字符串。

mysql> SELECT FIND_IN_SET('a','');
+---------------------+
| FIND_IN_SET('a','') |
+---------------------+
|                   0 |
+---------------------+
1 row in set

SQL

以下语句返回NULL，因为第一个参数为NULL。

SELECT FIND_IN_SET(NULL,'x,y,z');

SQL

以下语句也返回NULL，因为第二个参数为NULL。

mysql> SELECT FIND_IN_SET('a',NULL);
+-----------------------+
| FIND_IN_SET('a',NULL) |
+-----------------------+
| NULL                  |
+-----------------------+
1 row in set

SQL

MySQL FIND_IN_SET：从表查询数据示例

为了演示目的，这里将创建一个名为divisions的新表。

首先，使用以下语句创建divisions表 -

USE testdb;
CREATE TABLE IF NOT EXISTS divisions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(25) NOT NULL,
    belts VARCHAR(200) NOT NULL
);

SQL

其次，将几行数据插入到divisions表。

INSERT INTO divisions(name,belts)
VALUES ('O-1','white,yellow,orange'),
 ('O-2','purple,green,blue'),
 ('O-3','brown,red,black'),
 ('O-4','white,yellow,orange'),
 ('O-5','purple,green,blue'),
 ('O-6','brown,red'),
 ('O-7','black'),
 ('O-8','white,yellow,orange'),
 ('O-9','purple,green,blue'),
 ('O-10','brown,red');

SQL

第三，要井底接受red皮带的部门，可以使用FIND_IN_SET函数，如下所示：

SELECT 
    name, belts
FROM
    divisions
WHERE
    FIND_IN_SET('red', belts);

SQL

执行上面查询语句，得到以下结果 -

+------+-----------------+
| name | belts           |
+------+-----------------+
| O-3  | brown,red,black |
| O-6  | brown,red       |
| O-10 | brown,red       |
+------+-----------------+
3 rows in set

SQL

MySQL NOT FIND_IN_SET示例

因为FIND_IN_SET()函数在第二个参数中找不到第一个参数时返回0。 因此，您可以使用NOT运算符来否定FIND_IN_SET()函数返回的结果值。

例如，要找到不接受黑(black)皮带的部门，请使用以下语句：

SELECT 
    name, belts
FROM
    divisions
WHERE
    NOT FIND_IN_SET('black', belts);

SQL

执行上面语句，得到以下结果 -

+------+---------------------+
| name | belts               |
+------+---------------------+
| O-1  | white,yellow,orange |
| O-2  | purple,green,blue   |
| O-4  | white,yellow,orange |
| O-5  | purple,green,blue   |
| O-6  | brown,red           |
| O-8  | white,yellow,orange |
| O-9  | purple,green,blue   |
| O-10 | brown,red           |
+------+---------------------+
8 rows in set

SQL

MySQL FIND_IN_SET函数与IN运算符

IN运算符确定值是否匹配指定值列表。 例如，要查找名称为O-1或O-2的部门，请按如下所示使用IN运算符。

SELECT 
    name, belts
FROM
    divisions
WHERE
    name IN ('O-1' , 'O-2');

SQL

执行上面查询语句，得到以下结果 -

+------+---------------------+
| name | belts               |
+------+---------------------+
| O-1  | white,yellow,orange |
| O-2  | purple,green,blue   |
+------+---------------------+
2 rows in set

SQL

可以使用FIND_IN_SET函数获得与上面查询相同的结果：

SELECT 
    name, belts
FROM
    divisions
WHERE
    FIND_IN_SET(name, 'O-1,O-2');

SQL

因此，column IN (x, y, z)表达式与FIND_IN_SET(column, 'x,y,z')相同。

IN运算符可以接受任意数量的参数，每个参数用逗号分隔。 但是，FIND_IN_SET函数只有两个参数。

当您希望将值与数据库中的值列表进行匹配时，可以使用IN运算符。 并且当希望将值与数据库中以逗号分隔存储的值列表进行匹配时，可以使用FIND_IN_SET函数。