在基础查询之上，MySQL 提供了一些稍显进阶但非常实用的功能，用于处理模糊匹配、文本搜索和复杂查询的组织。正则匹配解决复杂模式匹配，全文检索解决大量文本的智能搜索，CTE 则让复杂 SQL 像写程序一样清晰。本节逐一展开。
9.7.1 正则匹配
MySQL 支持通过 REGEXP（或同义词 RLIKE）运算符进行正则表达式匹配，比 LIKE 更强大灵活。LIKE 只有 % 和 _ 两种通配符，而正则表达式可以描述更复杂的模式。
基本语法
expr REGEXP pattern如果 expr 中的任何子串匹配 pattern，则返回 1（真），否则返回 0（假）。注意正则匹配默认不区分大小写，除非使用 REGEXP BINARY 或模式中指定大小写。
常用匹配规则
.匹配任意单个字符（除换行符）*前一个字符零次或多次+前一个字符一次或多次?前一个字符零次或一次^开始位置$结束位置[abc]字符类，匹配 a、b 或 c[a-z]范围类[^...]否定字符类|或()分组{n}重复 n 次
实用示例
-- 查找邮箱字段中包含@且以.com结尾的记录
SELECT email FROM users WHERE email REGEXP '.+@.+\\.com$';
-- 查找手机号以1开头，第二位是3-9的数字
SELECT phone FROM contacts WHERE phone REGEXP '^1[3-9][0-9]{9}$';
-- 查找名字以“张”或“李”开头的用户
SELECT name FROM users WHERE name REGEXP '^(张|李)';
-- 大小写敏感匹配（binary 关键字）
SELECT code FROM products WHERE code REGEXP BINARY 'ABC';正则函数
MySQL 8.0 还提供了正则函数，如 REGEXP_LIKE()、REGEXP_INSTR()、REGEXP_SUBSTR()、REGEXP_REPLACE()，功能更加强大且可移植性更好（遵循 ICU 正则）。
-- 替换电话号码中间的4位为****
SELECT REGEXP_REPLACE(phone, '([0-9]{3})[0-9]{4}([0-9]{4})', '\1****\2') AS masked_phone
FROM contacts;注意事项
- 正则匹配无法利用普通索引，性能较差，尽量避免在大表上频繁使用。
- 如果只是简单的前缀或后缀匹配，使用
LIKE 'abc%'可以利用索引，更高效。 - 正则表达式容易写错，建议先在测试环境验证。
9.7.2 全文检索
当需要对文章、评论、日志等长文本进行搜索时，LIKE '%关键词%' 不仅无法使用索引，效率低下，而且不支持相关度排序、多词匹配等高级需求。MySQL InnoDB 引擎从 5.6 开始内置全文索引（FULLTEXT），到 8.0 已经相当成熟，支持中文分词（需ngram分词器或插件）和相关性排序。
创建全文索引
在 CHAR、VARCHAR 或 TEXT 列上创建全文索引：
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, content);使用全文搜索
- 自然语言模式（默认）：根据相关性返回结果，相关性值为 0 到 1 的浮点数。
SELECT title, content,
MATCH(title, content) AGAINST('MySQL 优化') AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 优化');- 布尔模式：可以使用
+、-、>、<、()、~、*、"运算符控制搜索逻辑。
-- 必须包含“MySQL”，不能包含“基础”，可以包含“高级”
SELECT title FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -基础 高级' IN BOOLEAN MODE);中文全文检索
默认情况下，MySQL 的分词器对中文支持不好（按空格分词）。8.0 内置了 ngram 分词器，可以通过设置 FULLTEXT INDEX 时指定分词器，或全局配置：
-- 创建表时指定ngram分词器
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_ngram (title, content) WITH PARSER ngram
) ENGINE=InnoDB;ngram 会将文本切分成指定长度的连续字符块（默认2字符），能部分解决中文分词问题。如果追求更专业的分词，可安装第三方分词插件（如 jieba 分析器）。
全文检索的实用场景与局限
- 适合网站搜索、文档索引等场景，支持多关键词排序。
- 全文索引有最小和最大词长限制（参数
innodb_ft_min_token_size默认3，innodb_ft_max_token_size默认84），太短或太长的词不会被索引。 - 停用词表会过滤常见无意义词（如 the、is 等），中文停用词需自定义。
- 对于超大规模的全文检索，还是建议使用 Elasticsearch 等专业搜索引擎。
9.7.3 CTE 公用表表达式
CTE（Common Table Expressions）在 MySQL 8.0 中被引入，通过 WITH 子句定义临时的命名结果集，只在单条 SQL 语句内有效。它让复杂查询更容易编写、阅读和维护，尤其适用于多层嵌套的子查询或递归场景。
基本语法
WITH cte_name (col1, col2, ...) AS (
-- CTE 定义，一个 SELECT 语句
SELECT ...
)
SELECT ... FROM cte_name ...;简单 CTE 示例
假设要统计各部门员工数，并找出人数大于 5 的部门：
WITH dept_count AS (
SELECT department_id, COUNT(*) AS cnt
FROM employees
GROUP BY department_id
)
SELECT d.name, dc.cnt
FROM departments d
JOIN dept_count dc ON d.id = dc.department_id
WHERE dc.cnt > 5;这里的 dept_count 就像是一个临时视图，避免了在大查询中重复写子查询。
多个 CTE
可以定义多个 CTE，用逗号分隔，后面的 CTE 可以引用前面的：
WITH
order_total AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
),
big_customer AS (
SELECT customer_id, total
FROM order_total
WHERE total > 10000
)
SELECT c.name, bc.total
FROM customers c
JOIN big_customer bc ON c.id = bc.customer_id;递归 CTE
递归 CTE 可以处理层次结构数据（如组织架构、分类树），这是 CTE 最强大的功能。递归 CTE 包含两部分：锚定成员（初始查询）和递归成员（引用 CTE 本身的查询），两者用 UNION ALL 或 UNION DISTINCT 连接。
示例：查询公司所有子部门（假设 department 表有 id, parent_id 字段）：
WITH RECURSIVE dept_tree AS (
-- 锚点：顶级部门
SELECT id, name, parent_id, 1 AS level
FROM department
WHERE parent_id IS NULL
UNION ALL
-- 递归：查找子部门
SELECT d.id, d.name, d.parent_id, dt.level + 1
FROM department d
JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;递归会一直执行，直到没有新的行产生为止。MySQL 默认最大递归深度由系统变量 cte_max_recursion_depth 控制（默认1000），防止无限循环。
CTE 的优势与应用
- 替代派生表（子查询）使 SQL 更扁平易读。
- 同一 CTE 可以在后续查询中被引用多次，避免重复编写。
- 递归查询是分层数据处理的利器，比之前用存储过程或多次连接优雅得多。
- 注意：CTE 是逻辑上的临时结果，不会创建物理临时表（除非结果集太大或查询优化器决定物化），一般不影响最终查询性能。
总结一下：正则匹配提供复杂字符串模式查找，全文检索赋予长文本智能搜索与相关度排序，CTE 让复杂 SQL 结构化并支持递归。这三项能力让 MySQL 在文本处理和查询表达上更加现代和强大，是开发者应当掌握的实用工具。