意想不到的数据库查询模糊匹配

程序员冈刀，目前就职于美团，java开发工程师，研究生。2022年，毕业于北京邮电大学电子工程学院、电子与通信工程专业。个人公众号《代码废柴》欢迎关注。

---

一、引言

数据库查询的模糊匹配的目的是根据用户输入的条件，找到相似或者类似的查询结果。平时在工作中经常地会使用到数据库查询的模糊匹配。例如最常见的模糊匹配的语句就是like语句。

在本文中，主要介绍采用模糊匹配的另外的一种形式：正则表达式的数据库的模糊匹配。

本文中涉及到的表如下定义：

课程表

create table `course`
(
    `id`   bigint auto_increment comment '主键ID',
    `name` varchar(20) comment '课程名字',
    `note` varchar(50) comment '课程笔记',
    primary key (`id`),
    index  `idx_name` (`name`)
) engine = "InnoDB" default character set='utf8';

初始的测试数据

insert into `course`(`name`, `note`)
values ('数学', '数学笔记'),
       ('英语', '英语笔记'),
       ('化学', '化学笔记');

二、实操

2.1 使用“%”进行模糊匹配

select * from course where note like '英%';

2.2 使用“_”匹配

上面的匹配没有返回结果，是因为，“_”仅仅能匹配一个字符。所以，如果想要匹配多个字符，还是需要使用'%'匹配。

我们给note字段加上索引，一起分析一下上面通配符搜索的效率：

create index idx_note on course (note);

上面的4张图片，分别描述了采用“%”、“_”通配符进行模糊匹配的时候，是否采用了索引。验证发现，我们虽然在NOTE字段上建立了索引，但是当我们的通配符在前面的时候，还是会进行全表的查询。如果放在后面，就会走索引查询，这也是索引失效的一个点。

2.3 正则表达式模糊匹配

简单的模糊匹配已经足够的满足了大部分的需求，但是如果需要查询忽略大小英语字母的查询应该怎么办呢？这里首先先增加一些数据。

insert into `course`(`name`, `note`)
values ('math', 'this is a math's note!!'),
       ('english', 'this is a english's note!!'),
       ('chemistry', 'this is a chemistry's note!!');

如果是查询，可以忽略大小写的结果，可以采用正则表达式（exgexp） 查询。

select * from course where note regexp '[A|a].*[t|T]';

上面的查询，可以返回带有大A、小A和大T和小T的内容的结果。但是，如果如果查询以“this is a m”开头的怎么写呢？很简单，前面加上‘^’这里是正则而已。同理，以什么结尾加上‘$’就可以了。

但是正则表达式模糊匹配的查询采用的是全表查询，性能不是很好，如果没有特殊的需求，请不要轻易的进行使用。详细的执行结果如下图：

三、结论

普通的模糊匹配，如果通配符在后面，模糊查询使用了索引，但是如果通配符在前面，就无法使用索引了。正则的模糊匹配查询，无论何时，都是采用的全表查询，不过正则查询具有天然的灵活性，可以实现更加高级的模糊匹配。所以在使用的时候，一定要想好，是采用简单的通配符查询还是正则查询，如果所有的查询都无法满足客户端响应的需求，这时，就要考虑其他方式进行实现了，比如采用ES实现模糊查询等等。

---

程序员冈刀，目前就职于美团，java开发工程师，研究生。2022年，毕业于北京邮电大学电子工程学院、电子与通信工程专业。个人公众号《代码废柴》欢迎关注。