模式匹配

导学

精确匹配和范围查询无法满足所有需求。当你需要"查找姓白的员工"或"查找产品名以 Pro 结尾的商品"时，就需要模式匹配。MySQL 提供了 LIKE 和 REGEXP 两种方式。

定义

模式匹配：通过特殊通配符或正则表达式，对字符串进行模糊匹配的技术。LIKE 使用简单的通配符，REGEXP 使用正则表达式，功能更强但性能更差。

演示数据准备

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前 employees 表中的完整数据如下：

emp_idemp_namedeptscore
1大翔技术部100
2白歌技术部NULL

LIKE 运算符

LIKE 使用两个通配符：

  • %：匹配任意长度的任意字符（包括空字符）
  • _：匹配单个任意字符

场景一：前缀匹配（最常用，可利用索引）

当前数据状态：见上文 employees 表完整数据。

执行语句：

SELECT emp_name, dept FROM employees WHERE emp_name LIKE '白%';

操作后结果：

emp_namedept
白歌技术部

结果解读：白% 匹配以"白"开头的任何字符串。前缀匹配在某些情况下可以利用索引（如 utf8mb4_general_ci 排序规则），是 LIKE 中性能最好的形式。

场景二：后缀匹配（无法利用普通索引）

当前数据状态：见上文 employees 表完整数据。

执行语句：

SELECT emp_name, dept FROM employees WHERE emp_name LIKE '%蓝';

操作后结果：

emp_namedept

结果解读：%蓝 匹配以"蓝"结尾的姓名。% 在开头时，MySQL 无法使用普通 B-Tree 索引，必须全表扫描。大数据量时极慢。

场景三：包含匹配（无法利用普通索引）

当前数据状态：见上文 employees 表完整数据。

执行语句：

SELECT emp_name, dept FROM employees WHERE emp_name LIKE '%歌%';

操作后结果：

emp_namedept
白歌技术部

结果解读：%歌% 匹配包含"歌"字的任何姓名。这是业务中最常见的模糊搜索需求，但前后都有 % 时完全无法使用 B-Tree 索引。

再看一个部门包含匹配的示例：

SELECT emp_name, dept FROM employees WHERE dept LIKE '%术%';

操作后结果：

emp_namedept
大翔技术部
白歌技术部

结果解读：%术% 匹配包含"术"字的任何部门。技术部包含"术"字，因此大翔和白歌被返回。

场景四：单字符匹配

当前数据状态：见上文 employees 表完整数据。

执行语句：

-- 匹配"白某"（白 + 恰好一个字符）
SELECT emp_name FROM employees WHERE emp_name LIKE '白_';

操作后结果：

emp_name
白歌

结果解读：白_ 匹配"白"后面跟恰好一个字符的字符串。"白歌"符合（白+歌），而如果存在"白歌儿"则不符合。

再看匹配恰好两个字符的姓名：

-- 匹配恰好两个字符的姓名
SELECT emp_name FROM employees WHERE emp_name LIKE '__';

操作后结果：

emp_name
大翔
白歌

结果解读：__（两个下划线）匹配恰好两个字符的字符串。表中所有员工姓名均为两个字，因此全部返回。

场景五：匹配通配符本身

执行语句：

-- 查找包含百分号的字符串（需转义）
SELECT '限时折扣50% off' AS description WHERE '限时折扣50% off' LIKE '%\%%' ESCAPE '\\';

操作后结果：

description
限时折扣50% off

结果解读：ESCAPE '\\' 指定反斜杠为转义字符，\% 表示匹配字面意义的百分号，而非通配符。如果不转义，LIKE '%%%' 会匹配所有包含任意字符的字符串，失去筛选意义。

REGEXP 运算符

REGEXP（或 RLIKE）支持完整的正则表达式匹配，功能远强于 LIKE。

场景六：正则匹配姓名格式

当前数据状态：见上文 employees 表完整数据。

执行语句：

SELECT emp_name, dept FROM employees
WHERE emp_name REGEXP '^..$';

操作后结果：

emp_namedept
大翔技术部
白歌技术部

结果解读：

  • ^ 表示字符串开头
  • .. 表示任意两个字符
  • $ 表示字符串结尾

表中所有员工姓名都是两个字，因此全部返回。如需匹配特定长度的字符串，正则表达式非常直观。

场景七：匹配多个模式

当前数据状态：见上文 employees 表完整数据。

执行语句：

-- 查找部门为技术部或产品部的员工
SELECT emp_name, dept FROM employees
WHERE dept REGEXP '技术部|产品部';

操作后结果：

emp_namedept
大翔技术部
白歌技术部

结果解读：| 在正则中表示"或"，因此 '技术部|产品部' 匹配包含任一模式的部门。这比 LIKE 更强大，因为 LIKE 一次只能写一个模式（除非用 OR 连接多个 LIKE 条件）。

场景八：匹配中文字符

当前数据状态：见上文 employees 表完整数据。

执行语句：

SELECT emp_name, dept FROM employees
WHERE emp_name REGEXP '[一-龥]';

操作后结果：

emp_namedept
大翔技术部
白歌技术部

结果解读：[一-龥] 匹配任意一个中文字符。所有员工姓名都包含中文字符，因此全部返回。这展示了正则表达式字符类的强大能力。

LIKE vs REGEXP 对比

特性LIKEREGEXP
语法复杂度简单（%, _）复杂（正则语法）
性能相对较好较慢
索引利用前缀匹配可用完全不能用索引
功能基础模糊匹配复杂模式匹配
大小写敏感取决于排序规则默认不敏感，可用 BINARY

常见误区

误区正解
LIKE '%xxx%' 可以用索引不能。含前导 % 的 LIKE 无法使用 B-Tree 索引。
LIKE 是大小写敏感的默认不敏感，取决于排序规则。如需敏感匹配用 LIKE BINARY。
REGEXP 比 LIKE 快恰恰相反，REGEXP 更慢且完全无法使用索引。
LIKE '白%' 和 = 一样快前缀 LIKE 可以用索引，但通常比 = 稍慢（范围扫描 vs 精确查找）。

面试考点

Q：LIKE '%abc' 为什么慢？如何优化？

前导 % 导致无法使用 B-Tree 索引，必须全表扫描。优化方案：1. 业务上改为前缀匹配；2. 使用全文索引（FULLTEXT）；3. 使用倒排表或搜索引擎（Elasticsearch）。

Q：LIKE 和 REGEXP 的区别？

LIKE 使用 % 和 _ 通配符，语法简单，前缀匹配可利用索引；REGEXP 使用正则表达式，功能强大但性能差，完全无法利用索引。

Q：MySQL 5.7 有全文索引吗？

有。InnoDB 和 MyISAM 都支持 FULLTEXT 索引，用于高效的全文检索，可以替代 %keyword% 的慢查询。但中文全文检索需要额外配置 ngram 解析器（MySQL 5.7.6+）。

Q：如何让 LIKE 区分大小写？

使用 LIKE BINARY 或确保列的排序规则是 _bin（如 utf8mb4_bin）。

小结

  • LIKE 是日常模糊匹配的首选，% 匹配任意字符，_ 匹配单个字符
  • 只有前缀匹配（'xxx%'）可能利用索引，其他形式会全表扫描
  • REGEXP 功能更强但性能更差，大数据量慎用
  • 匹配通配符本身需要用 ESCAPE 指定转义字符
  • 全文检索需求应考虑 FULLTEXT 索引或外部搜索引擎

下一章引子：学会了筛选单行数据，接下来学习如何对多行数据进行汇总统计——聚合函数。