21. 模糊查询 LIKE 与正则表达式

21.1 为什么需要模糊查询

前面的查询都是精确匹配，WHERE username = '张三' 只能查用户名恰好是”张三”的。但实际需求往往没那么精确：

• “查所有姓张的用户”
• “查邮箱以 @gmail.com 结尾的用户”
• “查用户名里包含数字的”

这些场景就需要模糊查询。MySQL 提供了两种方式：LIKE 和 REGEXP。

21.2 LIKE 与通配符

LIKE 是最简单的模糊匹配方式，配合两个通配符使用：

通配符	含义	示例
%	匹配任意数量字符（包括零个）	'张%' 匹配”张”、“张三”、“张三丰”
_	匹配单个字符	'张_' 匹配”张三”，不匹配”张”或”张三丰”

开头匹配

-- 查所有姓张的用户
SELECT * FROM users WHERE username LIKE '张%';

% 在后面，表示”张”后面跟任意字符（或没有字符）。

结尾匹配

-- 查邮箱以 @gmail.com 结尾的用户
SELECT * FROM users WHERE email LIKE '%@gmail.com';

% 在前面，表示前面可以是任意字符。

中间包含

-- 查用户名里包含"三"的用户
SELECT * FROM users WHERE username LIKE '%三%';

两端都加 %，表示”三”可以在任意位置。

下划线通配符

-- 查用户名是两个字且第一个字是"张"的用户
SELECT * FROM users WHERE username LIKE '张_';

_ 只匹配一个字符，多了少了都不行。

21.3 LIKE 的转义

如果查询的内容本身就包含 % 或 _，怎么办？比如查邮箱里包含”user_name”的用户，_ 会被当成通配符。

用 ESCAPE 关键字指定转义字符：

-- 用反斜杠转义下划线
SELECT * FROM users WHERE email LIKE '%user\_name%' ESCAPE '\\';

\_ 表示字面意义的下划线，不再当通配符。MySQL 默认的转义字符就是 \，所以 ESCAPE '\\' 可以省略不写：

SELECT * FROM users WHERE email LIKE '%user\_name%';

转义 % 同理：

-- 查包含 50% 的内容
SELECT * FROM orders WHERE status LIKE '%50\%%';

这里第一个和第三个 % 是通配符，中间 \% 是字面意义的百分号。

21.4 NOT LIKE

取反用 NOT LIKE：

-- 查邮箱不是以 @gmail.com 结尾的用户
SELECT * FROM users WHERE email NOT LIKE '%@gmail.com';

21.5 REGEXP：正则表达式

LIKE 的通配符太简单了，复杂匹配搞不定。比如”查用户名以字母开头、后面跟数字”的，LIKE 写不了。这时候用 REGEXP（或 RLIKE，两者等价）。

基本语法：

SELECT * FROM users WHERE username REGEXP '正则模式';

正则表达式常用语法

语法	含义	示例
^	匹配字符串开头	'^张' 以”张”开头
$	匹配字符串结尾	'com$' 以”com”结尾
.	匹配任意单个字符	'张.' “张”后跟任意一个字符
[abc]	匹配方括号中任意一个字符	'[张李王]' 匹配”张”或”李”或”王”
[^abc]	匹配不在方括号中的字符	'[^0-9]' 匹配非数字字符
[a-z]	匹配范围	'[a-z]' 匹配小写字母
*	前面的字符出现 0 次或多次	'ab*' 匹配”a”、“ab”、“abbb”
+	前面的字符出现 1 次或多次	'ab+' 匹配”ab”、“abbb”，不匹配”a”
?	前面的字符出现 0 次或 1 次	'ab?' 匹配”a”或”ab”
{n}	前面的字符出现 n 次	'a{3}' 匹配”aaa”
{n,m}	出现 n 到 m 次	'a{2,4}' 匹配”aa”到”aaaa”
|	或	'张|李' 匹配”张”或”李”
(abc)	分组	'(ab)+' 匹配”ab”、“abab”

实际示例

-- 查用户名以"张"开头的
SELECT * FROM users WHERE username REGEXP '^张';

-- 查用户名以"张"或"李"开头的
SELECT * FROM users WHERE username REGEXP '^[张李]';

-- 查邮箱以数字结尾的
SELECT * FROM users WHERE email REGEXP '[0-9]$';

-- 查用户名包含至少两个连续数字的
SELECT * FROM users WHERE username REGEXP '[0-9]{2}';

-- 查用户名是纯字母的
SELECT * FROM users WHERE username REGEXP '^[a-zA-Z]+$';

预定义字符类

MySQL 正则还支持一些预定义字符类，写起来更短：

类名	含义	等价于
[:digit:]	数字	[0-9]
[:alpha:]	字母	[a-zA-Z]
[:alnum:]	字母和数字	[a-zA-Z0-9]
[:space:]	空白字符	[ \t\n\r]
[:upper:]	大写字母	[A-Z]
[:lower:]	小写字母	[a-z]

-- 查用户名包含空格的
SELECT * FROM users WHERE username REGEXP '[[:space:]]';

21.6 LIKE vs REGEXP

对比项	LIKE	REGEXP
匹配能力	简单（只有 % 和 _）	强大（完整正则语法）
匹配方式	整个字符串匹配	部分匹配即可（除非用 ^ $）
性能	略快	略慢
可读性	简单直观	复杂模式可读性差
使用频率	日常查询常用	复杂匹配时用

一个重要区别：LIKE 是全字符串匹配，LIKE '张' 只匹配恰好是”张”的。而 REGEXP 是部分匹配，REGEXP '张' 匹配任何包含”张”的字符串。

-- LIKE：只匹配恰好是"张三"的
SELECT * FROM users WHERE username LIKE '张三';

-- REGEXP：匹配任何包含"张三"的
SELECT * FROM users WHERE username REGEXP '张三';

-- REGEXP 要全字符串匹配，需加锚点
SELECT * FROM users WHERE username REGEXP '^张三$';

21.7 模糊查询的性能问题

模糊查询是性能杀手，特别是 % 开头的查询。

前缀匹配可以走索引

-- 能走索引（如果 username 上有索引）
SELECT * FROM users WHERE username LIKE '张%';

因为 张% 匹配的是以”张”开头的，B+Tree 索引可以快速定位。

% 开头走不了索引

-- 走不了索引，全表扫描
SELECT * FROM users WHERE username LIKE '%三%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';

% 在开头意味着不知道从哪开始找，索引帮不上忙，只能逐行扫描。数据量大时会很慢。

REGEXP 的性能

REGEXP 的性能比 LIKE 更差，几乎总是全表扫描。只有在数据量不大或确实需要复杂匹配时才用。

21.8 小结

LIKE 适合简单的前缀、后缀、包含匹配，REGEXP 适合复杂的模式匹配。核心记住：% 匹配任意数量字符，_ 匹配单个字符，正则用 ^$ 做全字符串匹配，特殊字符用 \ 转义。性能上，前缀匹配（xxx%）能走索引，% 开头的模糊匹配会全表扫描，慎用在大表上。下一章学排序和分页。