MySQL find_in_set指定分隔符|极客笔记

MySQL find_in_set指定分隔符

在MySQL中，find_in_set是一个用于查找指定值在一个逗号分隔的字符串列表中的位置的函数。默认情况下，find_in_set函数的分隔符是逗号“,”。但是有时候我们需要在不同的分隔符下进行查找操作，这时就需要自定义分隔符。本文将详细介绍如何在MySQL中使用find_in_set函数，并指定不同的分隔符进行查询。

find_in_set函数

在MySQL中，find_in_set函数用于在一个逗号分隔的字符串列表中查找指定值的位置。该函数的语法如下：

FIND_IN_SET(search_string, string_list)

search_string：要查找的字符串。
string_list：逗号分隔的字符串列表。

函数将返回search_string在string_list中的位置，如果没有找到则返回0。

指定分隔符进行查询

默认情况下，find_in_set函数使用逗号作为分隔符。但是有时候我们可能需要使用其他分隔符进行查询，比如分号“;”或者竖线“|”。这时可以通过自定义函数来实现。

下面是一个自定义函数find_in_set_custom，用于指定不同的分隔符进行查询：

DELIMITER //
CREATE FUNCTION find_in_set_custom(search_string VARCHAR(255), string_list VARCHAR(255), delimiter CHAR(1))
RETURNS INT
BEGIN
    DECLARE position INT DEFAULT 0;
    DECLARE tmp_string VARCHAR(255);
    DECLARE tmp_pos INT DEFAULT 0;

    SET tmp_string = CONCAT(string_list, delimiter);

    WHILE tmp_pos <> 0 DO
        SET tmp_pos = LOCATE(delimiter, tmp_string);

        IF SUBSTRING(tmp_string, 1, tmp_pos - 1) = search_string THEN
            SET position = position + 1;
            RETURN position;
        END IF;

        SET tmp_string = SUBSTRING(tmp_string, tmp_pos + 1);
        SET position = position + 1;
    END WHILE;

    RETURN 0;
END//
DELIMITER ;

以上SQL语句创建了一个名为find_in_set_custom的自定义函数，该函数接受三个参数：

search_string：要查找的字符串。
string_list：逗号分隔的字符串列表。
delimiter：分隔符。

函数将返回search_string在string_list中的位置，如果没有找到则返回0。函数的实现原理是在字符串列表后面添加上指定的分隔符，然后不断地截取字符串进行比较，直到找到目标值或遍历完整个列表。

使用示例

现在让我们来看一个示例，假设我们有一张employees表，其中的skills字段存储了员工的技能，使用竖线“|”作为分隔符。我们要查找具有特定技能的员工在skills中的位置。

首先，我们需要创建employees表并插入一些数据：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    skills VARCHAR(255)
);

INSERT INTO employees (id, name, skills) VALUES
(1, 'Alice', 'Java|Python|SQL'),
(2, 'Bob', 'C#|JavaScript|HTML'),
(3, 'Charlie', 'Python|SQL|C++');

接下来，我们可以使用自定义函数find_in_set_custom来进行查询：

SELECT id, name, skills, find_in_set_custom('Python', skills, '|') AS position
FROM employees
WHERE find_in_set_custom('Python', skills, '|') > 0;

运行以上SQL语句，将会输出具有Python技能的员工在skills中的位置：

| id |   name   |    skills    | position |
|----|----------|--------------|----------|
|  1 |  Alice   | Java|Python|SQL|    2    |
|  3 | Charlie  | Python|SQL|C++|    1    |

从结果可以看出，员工Alice在skills中的位置是2，员工Charlie在skills中的位置是1。