自定义mysql函数之字符串逗号分割查询（find_in_set）

增强 find_in_set()

在mysql中，我们有时候设计数据库某个字段需要通过逗号进行分割，然后根据传入的字符串查询是否存在的方法进行判断，mysql默认的 find_in_set() 可以对比某个逗号分割的字符串中是否存在指定字符串，例如下面的例子

create table t_test(
    id bigint auto_increment primary key comment 'id',
    name varchar(255) default null comment '名称'
) comment '测试表';

insert into t_test(name) values ('a,b'),('z,k'),('a,c');
1
2
3
4
5
6

例如：我需要查询，字段中有a的数据，通过 find_in_set() 就可以查询出来

select * from t_test where find_in_set('a', name);
1

假如我现在需要判断多个字符串是否都存在于字符串里面，那么就需要下面这么写了，如果字符多了，那么就会很长，所以我们对其进行增强一下，让它可以支持多个字符串

select * from t_test where find_in_set('a', name) or find_in_set('z', name);
1

增强函数，这样就可以实现多个字符串的查找了

DELIMITER //

CREATE FUNCTION find_in_set_multiple(str_list VARCHAR(255), search_str VARCHAR(255))
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE pos INT DEFAULT 1;          -- 当前查找的起始位置
    DECLARE result INT DEFAULT 0;       -- 默认返回的匹配结果false
    DECLARE current_str VARCHAR(255);   -- 当前字符串
    DECLARE comma_pos INT;              -- 逗号的位置

    WHILE pos > 0 DO
        SET comma_pos = INSTR(str_list, ',');   -- 查找逗号的位置

        IF comma_pos = 0 THEN                   -- 如果没有逗号，那么字符串就是全部的字符串
            SET current_str = str_list;
            SET pos = 0;
        ELSE
            SET current_str = SUBSTRING(str_list, 1, comma_pos - 1);    -- 截取逗号之前的字符串
            SET str_list = SUBSTRING(str_list, comma_pos + 1);
        END IF;

        -- 如果当前字符串存在于搜索的字符串中，设置结果为1，结束循环
        IF FIND_IN_SET(current_str, search_str) > 0 THEN
            SET result = 1;
            SET pos = 0;
        END IF;
    END WHILE;

    RETURN result;
END //

DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

创建好函数之后对应的改造sql

select * from t_test where find_in_set_multiple(name, 'a,z');
1