LIKE 与模式匹配 — 让查询学会“模糊搜索“

📌 一句话总结

LIKE是 SQL 中做模糊匹配的核心运算符，搭配通配符%（匹配任意长度）和_（匹配单个字符），实现"包含""开头""结尾"等文本搜索场景。

1. 基础语法

SELECT column_list FROM table_name WHERE column_name LIKE pattern;

• LIKE是区分大小写的（MySQL 默认不区分，取决于 collation）
• NOT LIKE取反
• 模式串中写普通字符就匹配该字符

2. 两个通配符

-- 查询所有姓张的员工 SELECT name, email FROM employees WHERE name LIKE '张%'; -- 查询 "张X" 两个字名字的员工 SELECT name FROM employees WHERE name LIKE '张_'; -- 查询邮箱包含 @qq.com SELECT name, email FROM employees WHERE email LIKE '%@qq.com'; -- 查询名字以 "小" 开头的用户 SELECT username FROM users WHERE username LIKE '小%';

3. 结合使用

-- 名字第二个字是 "大"：_ 占一个位置 SELECT name FROM employees WHERE name LIKE '_大%'; -- 匹配：张大伟、李大牛（❌ 大声、大壮 因为第一个字缺失） -- 邮箱以 .cn 结尾 SELECT email FROM employees WHERE email LIKE '%.cn'; -- 手机号前 3 位是 138，后 8 位任意（但只查 11 位） SELECT phone FROM users WHERE phone LIKE '138________';

4. 转义通配符

如果要搜索的内容本身包含%或_：

-- 默认方式：用 ESCAPE 指定转义符 SELECT title FROM articles WHERE title LIKE '%100%%' ESCAPE ''; -- 匹配："促销100%折扣"、"升级100%完成" -- 也可以自定义转义字符 SELECT path FROM files WHERE path LIKE '%/important_%' ESCAPE '/'; -- 此时的 _ 是普通下划线，不是通配符

实际场景：搜索文件名含_的日志、产品型号含%的数据。

5. 数据库方言差异

PostgreSQL：ILIKE + 正则

-- ILIKE：不区分大小写 SELECT name FROM users WHERE name ILIKE 'zhang%'; -- 匹配：Zhang、zhang、ZHANG -- SIMILAR TO：带正则的风格 SELECT name FROM users WHERE name SIMILAR TO '张[三四五]%'; -- 匹配：张三丰、张四海（正则风格的模式匹配） -- 正则表达式 ~ / ~*（PostgreSQL 独有利器） SELECT name FROM users WHERE name ~ '^张[^\s]{1,2}$'; -- 匹配：张飞、张三（2-3字，姓张） -- ~ 区分大小写，~* 不区分，!~ 取反

MySQL：默认不区分大小写

MySQL 的LIKE默认不区分大小写（取决于表和字段的 collation 设置）。 如果需要区分大小写，用LIKE BINARY：

SELECT name FROM users WHERE name LIKE BINARY 'Zhang%'; -- 只匹配大写 Z，不匹配 zhang

SQLite：简单的 LIKE

SQLite 的LIKE默认不区分 ASCII 字母大小写，且不支持ILIKE或正则。

6. 性能问题（重要 ⚠️）

-- ❌ 以 % 开头的 LIKE 不能走索引 SELECT * FROM logs WHERE message LIKE '%ERROR%'; -- 全表扫描！ -- ✅ 前缀匹配可以走索引 SELECT * FROM logs WHERE message LIKE 'ERROR:%'; -- 利用 B-Tree 索引快速查找 -- ✅ 尾部模糊也能走索引 SELECT * FROM logs WHERE message LIKE '2024-01-%';

为什么？

B-Tree 索引是按字典序排列的，LIKE 'ABC%'等价于查['ABC', 'ABD')区间，索引可以快速定位。 但LIKE '%ABC'不知道前面是什么，必须扫描所有行。

替代方案

7. 实战案例

案例 1：用户搜索

-- 搜索框：输入 "张三" SELECT user_id, username, email, phone FROM users WHERE username LIKE CONCAT('%', '张三', '%') OR email LIKE '%张三%'; -- ⚠️ 实际产品中应加 LIMIT，防全表爆炸

案例 2：清理含特殊字符的数据

-- 找出用户名带下划线的用户（确认下划线是普通字符而非通配符） SELECT username FROM users WHERE username LIKE '%/_%' ESCAPE '/';

案例 3：日志按模式分类

-- PostgreSQL：用正则提取日志类别 SELECT log_entry, CASE WHEN log_entry ~* 'error|fatal|critical' THEN '严重' WHEN log_entry ~* 'warn' THEN '警告' WHEN log_entry ~* 'info|debug' THEN '信息' ELSE '其他' END AS level FROM system_logs WHERE log_entry ~ '2024-01-1[5-9]'; -- 1月15-19日的日志

📝 要点总结