【MySQL实践】使用FIND_IN_SET查询逗号隔开的数据&Mybatis-Plus的实现方式

1.背景

项目中有一张表中有这么一个字段，这个字段的值是通过逗号隔开的数据，例如：1,2,3,4,5，并且在存储的时候这些数字并不是有序的，有可能是：2,3,5,1,4等等，当然也可能不完全是5个数字，也可能是：1,5，2,3,5等等。
现在接到一个需求，需要将这个字段作为查询条件，传入的参数是这些数字的排列组合，例如：1,3，2,5等，只要传入的查询参数每一个都被包含在这个字段中，就表示条件命中可以被查询出来。

使用到这个查询的接口是一个分页查询的接口，所以需要再数据库层面去实现它，查了一下资料可以使用FIND_IN_SET函数来实现。
当然，需要再在不考虑性能优化的情况下酌情使用，因为这个函数是使用在查询条件的字段上的，会引起索引失效，数据库数据量大的时候，查询速度会变慢。

2.实现方式

2.1.前期准备

假设上述的字段名是type，创建一张demo表，并插入一些测试数据：

create table demo
(
    id   int auto_increment
        primary key,
    type varchar(10) null
);

INSERT INTO demo (type) VALUES ('1,2,3,5,4');
INSERT INTO demo (type) VALUES ('1');
INSERT INTO demo (type) VALUES (null);
INSERT INTO demo (type) VALUES ('2,5,1');
INSERT INTO demo (type) VALUES ('3,4');
1
2
3
4
5
6
7
8
9
10
11
12

然后是find_in_set的语法，用在查询条件中，一般是这样的：

xxx where find_in_set('条件值',字段名);
1

需要注意的是，这里的条件值应该是一个一个的数字，所以当查询参数是多个数字时，需要将其拆分成多个数字，通过and进行连接。

2.2.测试

搞几个测试用例测一下，这里不做完全覆盖，有兴趣可以自己覆盖一下：

参数：1 -> 预期：3条数据
参数：1,2 -> 预期：2条数据
参数：3,5 -> 预期：1条数据
参数：6 -> 预期：0条数据

select * from demo where find_in_set('1',type);
1

select * from demo 
where find_in_set('1',type) 
and find_in_set('2',type);
1
2
3

select * from demo
where find_in_set('3', type)
and find_in_set('5', type);
1
2
3

select * from demo
where find_in_set('6', type);
1
2

2.3.其他数据格式

除了逗号隔开以外，插入其他的数据格式：

INSERT INTO demo (type) VALUES ('1-2-3-4-5');
INSERT INTO demo (type) VALUES ('1_2_3_4_5');
1
2

通过同样的方式可以查出来吗？

select * from demo where find_in_set('1', type);
1

得到的结果当中并没有新增的两行数据，通过find_in_set的语义来看，MySQL认为逗号隔开的数据是一个集合，而其他符号隔开的数据并不是集合，所以不能匹配这个查询条件。

那如果要查询这样的数据应该怎么办呢？
一种方式是通过replace函数将其他符号替换为逗号，就可以查询出来了：

select * from demo where find_in_set('1', replace(type,'-',','));
1

但是这里我们都使用到两层函数做匹配了，那不如直接使用like。

select * from demo where type like '%1%' and type like '%3%';
1

那么问题来了，为什么逗号隔开的数据不用like来查询呢？
我查到的说法是find_in_set专门针对逗号隔开的数据做了优化，查询性能上会比like好一点，但实际上我在50万数据的表中通过两种方式来做查询，没有感受到性能有太大的差别，不过既然find_in_set是专门针对逗号隔开的场景的，那就直接使用吧。

3.在Mybatis-Plus中的使用find_in_set

直接写sql的方式这里不提，如果是通过QueryWrapper来编写查询条件，可以在使用apply方法来传入sql。
加入请求参数是一个列表的情况下，可以这么编写：

// 模拟传入的参数列表
List<String> params = new ArrayList<>();
params.add("1");
params.add("2");
// 列表不为空，则拼接sql
if (params != null && !params.isEmpty()) {
    for (String param : params) {
        queryWrapper.apply("FIND_IN_SET('" + param + "', type)");
    }
}
1
2
3
4
5
6
7
8
9
10

这里直接循环apply就可以了，框架会自动生成and条件。

4.总结

对于逗号隔开的数据，可以通过find_in_set函数来进行匹配，其他符号隔开的数据可以通过like函数来进行拼接。