LIKE 模糊查询介绍

一、什么是 LIKE 模糊查询

LIKE 是 SQL 中用于字符串模式匹配的操作符，通常被称为“模糊查询”。它不要求两个字符串完全相等，而是判断目标字符串是否符合指定的模式。在 MySQL 中，LIKE 广泛用于用户搜索、日志过滤、商品名称匹配、邮箱域名筛选等场景。

基本语法如下：

expr LIKE pattern [ESCAPE 'escape_char']

其中 expr 通常是列名或字符串表达式，pattern 是匹配模式。LIKE 返回 1 表示匹配成功，0 表示不匹配；如果任一操作数为 NULL，结果为 NULL。

二、通配符

LIKE 的核心是两个通配符：

• %：匹配任意长度的字符串，包括空字符串。
• _：匹配任意单个字符。
• 其他字符：按字面值匹配。

示例：

SELECT * FROM users WHERE name LIKE '张%';      -- 以“张”开头
SELECT * FROM users WHERE name LIKE '%张%';     -- 包含“张”
SELECT * FROM users WHERE name LIKE '张_';      -- “张”后跟一个任意字符
SELECT * FROM users WHERE email LIKE '%@qq.com';-- 以 @qq.com 结尾

如果要匹配 % 或 _ 本身，需要转义。MySQL 默认转义字符是反斜杠 \，也可以使用 ESCAPE 自定义：

SELECT * FROM t WHERE code LIKE 'A\_%' ESCAPE '\';
SELECT * FROM t WHERE code LIKE '100\%';

若想禁用转义，可以使用 ESCAPE ''。

三、大小写与排序规则

LIKE 是否区分大小写，取决于列的字符集和排序规则（collation）。对于非二进制字符串，通常使用不区分大小写的排序规则，例如 utf8mb4_general_ci 中 'A' LIKE 'a' 为真。对于二进制字符串或 _bin 排序规则，则区分大小写。必要时可用 BINARY 强制区分：

SELECT * FROM users WHERE name LIKE BINARY 'Tom%';

此外，CHAR 和 VARCHAR 的尾随空格处理也受 collation 的 PAD SPACE 属性影响。在 MySQL 8.0 的 utf8mb4_0900_ai_ci 等 NO PAD 排序规则下，尾随空格不会被忽略。

四、性能与索引

LIKE 最大的问题是可能导致索引失效，进而引发全表扫描。InnoDB 使用 B+ 树索引，索引列按前缀有序存储。因此：

• LIKE 'abc%'：可以使用索引进行范围扫描。
• LIKE 'abc'：等价于等值查询，通常可以使用索引。
• LIKE '%abc'：无法使用索引快速定位，因为不知道前缀。
• LIKE '%abc%'：同样无法使用索引，通常全表扫描。
• LIKE 'abc%def'：可以利用前缀 abc 做范围扫描，再过滤剩余条件。

例如：

-- 可能走 range 扫描
SELECT * FROM users WHERE name LIKE '张%';

-- 通常全表扫描
SELECT * FROM users WHERE name LIKE '%张%';

如果查询列被索引覆盖，LIKE '%abc%' 可能走覆盖索引全扫描，比全表扫描略好，但仍然是扫描大量索引项。MySQL 5.6 以后支持索引条件下推（ICP），可以将部分 WHERE 条件下推到存储引擎层过滤，减少回表，但无法改变“前导百分号无法范围定位”的本质。

另外，如果对列使用函数、隐式类型转换，或列类型与查询值类型不一致，也可能导致索引失效。例如手机号若存为数字类型，再用 LIKE '138%' 查询，通常无法有效使用索引，因此手机号、编号等应存为字符串。

五、优化策略

1. 尽量使用前缀匹配：能用 LIKE 'abc%' 就不要用 LIKE '%abc%'。
2. 使用覆盖索引：让查询只访问索引，减少回表成本。
3. 全文索引：对文本搜索场景，使用 FULLTEXT 索引，支持自然语言模式和布尔模式。MySQL InnoDB 从 5.6 起支持全文索引，中文可使用 ngram 解析器。
4. 反向存储 + 索引：如果频繁按后缀查询，可额外存储反转后的字符串，并对其建索引。例如查询 email LIKE '%@qq.com'，可存 email_rev，查询 email_rev LIKE 'moc.qq@%'。
5. 生成列 + 索引：将需要匹配的前缀或规范化后的值存入生成列并建索引。
6. 外部搜索引擎：复杂全文搜索、模糊匹配、分词搜索可交给 Elasticsearch、OpenSearch、Sphinx 等。
7. 限制数据量与分页：无法避免前导 % 时，尽量增加其他过滤条件、限制返回行数，并配合缓存。
8. 使用 EXPLAIN 分析：关注 type 是否为 ALL、range、index，以及 Extra 中的 Using where、Using index condition 等信息。

六、LIKE 与 REGEXP

LIKE 只支持 % 和 _ 两种通配符，语法简单，通常比正则更快。REGEXP 支持完整正则表达式，功能更强，但性能通常更差，且更难使用普通 B+ 树索引。因此，能用 LIKE 前缀匹配解决的，优先用 LIKE；需要复杂模式时再考虑 REGEXP。

七、注意事项

• NULL LIKE '%' 的结果是 NULL，不是真。
• LIKE '' 只匹配空字符串。
• % 可以匹配空字符串，但不匹配 NULL。
• 默认转义字符受 SQL 模式影响，必要时显式使用 ESCAPE。
• 中文字符下，_ 匹配一个字符，而不是一个字节，具体取决于字符集。
• 前导 % 的查询在数据量大时极易成为性能瓶颈。

八、总结

LIKE 是 SQL 中最常用的模糊查询方式，语法简单、直观。它的性能关键在于通配符的位置：前缀匹配可以利用索引，前导百分号通常无法利用索引。在实际开发中，应尽量设计可前缀匹配的查询，必要时使用全文索引、反向列、生成列或外部搜索引擎。理解 LIKE 的匹配规则、排序规则和索引限制，才能既满足业务搜索需求，又避免拖慢数据库性能。