MySQL模糊查询/模式匹配（Pattern Match）

      使用SQL查询数据时，时常会遇到这种情况，我们并不需要精确的匹配，而是要查找具有某类特点的数据。这种场景我们就要用到模糊查询。MySQL中常用的模糊查询方法有2种：

• like语句模糊查询
• regexp正则表达式模式匹配

目录

一、使用like模糊匹配

二、使用正则表达式模式匹配

2.1 普通匹配

2.2 位置匹配

2.3 特定次数匹配

2.4 特定模式匹配

2.5 修改匹配的数据

---

一、使用like模糊匹配

当只需要进行简单模糊查询时，可以利用like语句完成，like可以用来模糊匹配字符串。

like语句的语法是：expr [not] like 'pattern'。expr可以是我们的数据列或者某种表达式，pattern就是我们想要查询的数据具有的模式。在'pattern'中，可以使用'_'来匹配单个字符或'%'来匹配任意字符串（包含空字符，但不会匹配null）。

下面用一个示例来演示like用法，先建立一张测试表：

create table test(
id int not null primary key,
value varchar(32));

insert into test values(1,'abc'),(2,'abcd'),(3,'abc123'),(4,'ab123xyz');

查询和abc有关的数据：

select * from test;

select * from test where value like 'abc';

• 当模式中没有_或%时，like会只用精确匹配（相当于=），这里只有abc了被匹配出来。

使用_匹配单个字符（包含空字符，但不会匹配null）：

select * from test where value like 'abc_';

这里的模式是'abc_'，使用_匹配单个字符，只有abcd被匹配了出来。

使用%匹配任意字符:

select * from test where value like 'abc%';

当使用%，匹配包含空字符在内的所有字符串。

使用not like反向匹配，即所有符合模式的都不查询出来：

select * from test where value not like 'abc%';

这里所有以abc开头的字符串都不会显示出来。

上面的例子都是以查找以abc开头的示例，_和%可以出现在模式的任何地方。需要注意的一点是，如果匹配的字段上有索引，如果遇到'%str'这种将%放在模式开头，那么将会导致索引失效，使用中需要斟酌一下性能。

二、使用正则表达式模式匹配

like 匹配的方式可以完成一些简单的模糊查询，例如你可以用%abc%来匹配任意包含abc的数据，但是如果问题换成：包含a或b或c，那么你就要写3次匹配，如果问题更复杂一点（例如匹配特定次数），like可能就无法完成了，此时你就需要采用正则表达式匹配。

正则表达式是一个包含文本和特殊字符的字符串，利用它可以识别各种复杂模式的字符串。MySQL可以通过regexp或rlike（这两个是同义词）操作符来完成正则表达式的匹配，语法和like一样 expr [not] regexp 'pattern'。

正则表达式的匹配结果，如果成功则返回1，失败返回0，用在where条件中分别对应真/假：

select 'abcd' regexp 'abc';

select 'abcd' regexp '123';

下面演示几类最常用的正则表示式匹配场景，我们先将测试数据换成更复杂内容：

truncate table test;

insert into test values(1,'abc'),(2,'abcd'),(3,'abc 1'),(4,'abc1,xkz'),(5,'abc13'),(6,'xyzABC123'),(7,'xyzabc1223'),(8,'123456'),(9,'1212123xyzabc');

commit;

测试数据如下：

select * from test;

2.1 普通匹配

普通匹配和like类似，直接输入字符串，则会匹配出包含改字符串的值:

select * from test where value regexp 'abc';

• regexp 'abc' 相当于like '%abc%'，所有包含abc的都匹配的出来，除了id为8的纯数字。

反向匹配，可以直接在regexp 前加上not，或者整体取反:

select * from test where value not regexp 'abc';

select * from test where not(value regexp 'abc');

• expr not regexp … 和 not (epxr regexp …)两种写法是等效的

2.2 位置匹配

某些时候我们需要从特定的位置开始匹配，正则表达式可以通过^和$来指定匹配的位置：

• ^ 代表从开头进行匹配
• $ 代表从结尾进行匹配

匹配以'xyz'开头的数据

select * from test where value regexp '^xyz';

匹配以'23'结尾的数据

• （23）的小括号表示这是一个整体

2.3 特定次数匹配

有些时候我们想知道某种模式出现的次数，这时我们就需要用到次数匹配了，常用的次数匹配模式如下：

• […]   匹配方括号出现的任意字符
• .       匹配任意单个字符
• *      匹配前面模式0次或多次
• +     匹配前面模式1次或多次
• ？    匹配前面模式0次或1次
• {m}  匹配前面模式m次
• {m,n} 匹配前面模式m到n次

匹配包含字符串'k'或者'5'的数据：

select * from test where value regexp '[k5]';

匹配x和z中间包含一个字符的模式：

select * from test where value regexp 'x.z';

匹配以abc开头，后面跟任意字符

select * from test where value regexp '^abc.*';

• .* 代表任意字符出现任意次数，可以匹配任何字符，这里去掉效果也是一样的

匹配abc1之后，出现1次或多次2的数据：

1. select * from test where value regexp 'abc12+';

• abc12+，+代表前面的2出现1次或多次

匹配abc1之后，出现0次或1次2的数据：

select * from test where value regexp 'abc12?';

• 注意和上个例子区别，？代表0次或1次，因此只要含abc1就会匹配出来
• id为7的数据，结尾1223，满足了出现1次2，所以也被查了出来

查询abc1之后，2出现2次的数据：

select * from test where value regexp 'abc12{2}';

• 只要2出现2次即视为满足条件，如果出现更多次数的2，依然是满足的（并不管后续数据，只要匹配到2次就判定成功）。

查询包含6个数据的数据：

select * from test where value regexp '[0-9]{6}';

• [0-9]代表数字1到9任意数字，{6}代表重复6次，只要有6个连续的数字即满足条件。

查询仅包含6个数字的数据：

select * from test where value regexp '^[0-9]{6}$';

• 我们把上个模式用^和$限制起来，代表从开头到结尾，只有6数字，id为9的数据（数字后包含字符串）就不匹配了。

查询'12'重复1到2次的数据：

select * from test where value regexp 'abc(12){1,2}';

• (12)用括号括起来代表一个整体
• {1,2} 代表匹配1次或2次，只要满足即可以。

2.4 特定模式匹配

MySQL还提供了一些特殊字符串来表示某一类字符,其格式是[:character_class:]，常用的类别有：

• [:digit:] 表示所有数字
• [:alpha:] 表示所有字符
• [:alnum:] 表示所有数字和字符
• [:blank:] 表示空白字符
• [:punct:] 表示标点符号
• [:upper:] 表示大写字符
• [:lower:] 表示小写字符

查询所有以数字开头的记录：

select * from test where value regexp '^[:digit:]';

• [:digit:] 代表所有数字，前面的^代表从开头开始匹配

查询所有以字母结尾的记录：

select * from test where value regexp '[:alpha:]$';

查询所有包含数字或字母的记录：

select * from test where value regexp '[:alnum:]';

查询所有包含空格的记录：

select * from test where value regexp '[:blank:]';

查询所有包含标点符号的记录：

select * from test where value regexp '[:punct:]';

查询包含大写字母的记录：

select * from test where value regexp '[:upper:]' COLLATE utf8mb4_0900_as_cs;

• 这里多了一个子句COLLATE utf8mb4_0900_as_cs，指定排序规则区分大小写。MySQL8.0中字符集utf8mb4的默认排序规则是utf8mb4_0900_ai_ci，它是不区分大小写的（排序规则结尾的ci代表Case Insensitive 大小写不敏感，cs代表Case Sensitive 大小写敏感）。

如果不加这个排序规则子句，默认是不区分大小写的，纯小写字符记录也会被查询出来，与我们想查大写字符的意愿不同：

select * from test where value regexp '[:upper:]';

你也可以使用函数regexp_like函数中的参数来指定大小写是否敏感（这个函数是regexp的另一种写法）：

select * from test where regexp_like(value, '[:upper:]', 'c')=1;

• 前两个参数是expr和pattern，第三个参数可以指定大小写是否敏感：'c' 大小写敏感，'i' 大小写不敏感

2.5 修改匹配的数据

很多时候，我们查找出某类数据后，就是为了修改它，利用regexp_replace函数可以修改正则表达式的匹配结果。

语法：regexp_replace(expr, pattern, replacement [, pos[, occurrence[, match_type]]])

参数解释：

• expr: 要搜索的内容
• pattern: 匹配的模式
• replacement: 替换的内容
• pos: 起始搜索的位置，默认是1，即从头开始匹配
• occurrence: 匹配发生的次数，默认是0，即替换所有匹配项
• match_type: 匹配模式，和regexp_like相同，常用的是：'c' 大小写敏感，'i' 大小写不敏感

下面通过几个示例来理解，将字符串'ababab'中的a替换为x:

select regexp_replace('ababab','a','x');

将字符串'ababab'中的a替换为x，但是从第2位开始搜索：

select regexp_replace('ababab','a','x',2);

• 这里使用了一个位置参数2，表示从第二位开始搜索，所以开头的a没有被替换

将字符串'ababab'中的a替换为x，但是从第2位开始搜索，并将第2次匹配的位置替换为x：

select regexp_replace('abababab','a','x',2,2);

• 这里使用了位置参数2，和发生的次数2，即从第2位开始，只有第二个匹配的a会被替换为x

将test表中的首次匹配2大写字符替换为字符串'这里有两个大写字符'：

select value, regexp_replace(value,'[:upper:]{2}','这里有两个大写字符',1,1,'c') after_replace from test where value regexp '[:upper:]' COLLATE utf8mb4_0900_as_cs;

• 模式[:upper:]{2}，代表任意大写字符重复2次
• 后面的1,1分别代表从头开始搜索，只匹配第一次
• 'c' 代表大小写敏感

修改前要先查询一下元数据和修改后的数据（最好备份一下），一是确认修改范围是不是我们想要匹配的数据，二是修改结果对不对，没有问题时再执行update：

update test set value=regexp_replace(value,'[:upper:]{2}','这里有两个大写字符',1,1,'c') where value regexp '[:upper:]' COLLATE utf8mb4_0900_as_cs;

select * from test;

可以看到，我们通过正则表达式匹配并更新成功了。

注意：在MySQL8.0.17版本前，这个函数返回的结果字符集是UTF-16（这是一个BUG），这可能导致你查询的结果和最后的更新结果不同，因此采用此函数批量匹配更新前一定要做好备份。

以上便是MySQL中常用的模糊匹配方法，可以满足大部分场景的模糊匹配。但是如果数据量非常大，例如需要在富文本中匹配想要的数据，上面的匹配方法有可能会比较慢，此时可以考虑采用使用另一种匹配技术：全文索引（Full-Text Index）