MySQL 8.0 WITH子句详解：从基础语法到递归CTE优化

你回想一下，早几年写稍微复杂一点的分析SQL，是不是每次都得先建临时表、再join几层子查询，到了第三层嵌套自己都看不下去？MySQL 8.0的通用表达式（WITH）出来后，这个问题基本算是翻篇了。我最早接触CTE其实是在业务报表里，当时一段长达两百行的嵌套子查询被逐步改写成多个WITH模块，不但执行流程清爽了，连后续加指标都省了大半力气。这篇文章想从实际使用的角度，把MySQL 8.0里WITH子句的基本语法、普通CTE的用法、递归CTE的实现原理以及性能优化注意点系统梳理一遍，重点侧重可复现的操作细节和踩坑记录，适合正在学习MySQL进阶特性、或者准备把老项目迁移到8.0的同学参考。

1. 从“能跑就行”到“优雅查询”：为什么值得下功夫学 WITH

1.1 一个改变我写SQL习惯的示例

最开始用CTE，是因为接手了一个报表模块，那段核心SQL长这样：一个大查询里套了两层临时子查询，每个子查询又各自引用上一层的聚合结果，结构几乎是三层嵌套。改动需求加个同比字段，我在里面调了两小时，改完都觉得只是碰巧能跑。后来我尝试用WITH重写，每个中间结果集独立成一块，再拼回主查询，意外的收获是：整体逻辑变得像搭积木，每一块命名清楚，后面维护的人不用再沿着括号往里钻。

这里放个简化版对比。假设要统计每个分类下销量Top3的商品：

-- 旧方案：嵌套子查询 SELECT t1.category_name, t1.goods_name, t1.sales_amount FROM ( SELECT c.name AS category_name, g.name AS goods_name, SUM(g.amount) AS sales_amount FROM goods_sales g JOIN category c ON g.category_id = c.id GROUP BY c.id, g.id ) t1 WHERE ( SELECT COUNT(*) FROM ( SELECT c2.id, g2.id, SUM(g2.amount) AS s2 FROM goods_sales g2 JOIN category c2 ON g2.category_id = c2.id GROUP BY c2.id, g2.id ) t2 WHERE t2.id = t1.category_id AND t2.s2 > t1.sales_amount LIMIT 1 ) = 0;

再换成CTE版本：

WITH sales_summary AS ( SELECT c.id AS category_id, c.name AS category_name, g.id AS goods_id, g.name AS goods_name, SUM(g.amount) AS sales_amount FROM goods_sales g JOIN category c ON g.category_id = c.id GROUP BY c.id, g.id ) SELECT s.category_name, s.goods_name, s.sales_amount FROM sales_summary s LEFT JOIN sales_summary s2 ON s2.category_id = s.category_id AND s2.sales_amount > s.sales_amount WHERE s2.category_id IS NULL;

是不是一眼就能看懂？这就是CTE最大的价值：它把SQL拆成逻辑清晰的模块，命名即文档，阅读顺序就是业务处理顺序，大括号地狱从此不存在了。

1.2 它解决的痛点和适用人群

通用表达式本质上是给SQL增加了一个命名的临时结果集概念。但临时结果集并不只存在于执行期——WITH在生成执行计划时就已经被优化器识别，可以被当作可复用的标准子结构进行优化。这一点和“直接创建临时表”完全不是一个思路。

这个特性尤其适合几类场景：

• 复杂报表：多指标需要基于同一份明细多次聚合，用WITH定义一次底层数据，后面所有分组都引用它。
• 层级数据：组织架构、菜单树、物料BOM清单，这些天然带上下级关系的数据，配合递归写法能一条SQL解决。
• 数据清洗：ETL过程中多步处理，比如先过滤、再去重、再算指标，每一步都用WITH表达，逻辑全部留在最终SQL里，审计起来也清楚。
• 老SQL重构：旧项目里大量嵌套子查询和临时表，逐步替换为CTE后，可维护性提升非常明显。

2. WITH 子句的核心语法拆解，照着写就行

2.1 基础语法与执行特点

MySQL 8.0的WITH语法，官方描述为：

WITH cte_name [(column_name_list)] AS ( SELECT ... ) SELECT ...

CTE可以单独存在，也可以多个连续定义，中间用逗号分隔。后面的CTE可以使用前面已经定义的CTE，但是前面的CTE不能反向引用后面的CTE，这是逻辑上的顺序依赖。

一个容易忽略的点：CTE列名可以在主体内显式指定，别名并不总是必须写在SQL子句内部。比如：

WITH revenue_cte (dept_id, total_revenue) AS ( SELECT dept_id, SUM(revenue) FROM sales_data GROUP BY dept_id ) SELECT * FROM revenue_cte;

这种做法在主体查询列名不清晰、或者聚合函数列名过长时特别好用，还能顺便统一下游引用的字段名。

多个CTE定义连写时，可以这样组织：

WITH base_data AS ( SELECT id, product_id, sale_amount, sale_time FROM order_detail WHERE sale_time >= '2024-01-01' ), dept_summary AS ( SELECT d.manager_id, SUM(b.sale_amount) AS total FROM base_data b JOIN dept d ON b.dept_id = d.id GROUP BY d.manager_id ) SELECT * FROM dept_summary;

这里第二CTE直接引用了第一个CTE，而且两个CTE模块的命名都表达了数据阶段，执行时像流水线一样一层层传递数据，整个链条很干净。

2.2 派生表和 WITH 的差异，别搞混

派生表就是from后面的括号子查询，它也能实现类似数据集的效果，但二者有三个关键差别：

• 可读性：派生表必须把结果内联在主查询括号里，无法单独命名被多次引用；CTE可以多次引用同一逻辑，且模块可以重复出现在多个后续位置。
• 可复用性：CTE在同一个语句里可以引用多次。派生表如果被反复引用，意味着那段子查询要重复写，语句膨胀且容易出错。
• 执行计划差异：CTE在优化器中的处理与物化、合并策略相关，MySQL8.0的优化器会评估CTE是否需要物化；而派生表虽然也会被优化，但代码层没法直接控制复用的粒度。

我遇到过这样一个案例：统计各部门销售额以及全公司销售额占比。用派生表写，公司总额那个子查询要重复写两遍，不仅冗长，还必须保持两边同步。用CTE就不一样了：

WITH dept_total AS ( SELECT dept_id, SUM(sale_amount) AS total FROM sales_detail GROUP BY dept_id ), grand_total AS ( SELECT SUM(total) AS all_total FROM dept_total ) SELECT d.dept_id, d.total, ROUND(d.total / g.all_total * 100, 2) AS pct FROM dept_total d CROSS JOIN grand_total g ORDER BY pct DESC;

这里dept_total被多次引用，CTE做到了“一次定义，多处使用”，维护成本直线降低。

提示：如果CTE只在主查询里引用一次，那它在语义上其实和派生表没太大差别。这时选择哪个，主要就看阅读是否方便。追求极致性能时，简单场景下不必为了“用特性而用特性”强上CTE。

3. 递归 CTE 的进阶玩法：让层级查询不再痛苦

3.1 递归是怎么工作的：三个关键部件

MySQL 8.0支持WITH RECURSIVE，也就是递归CTE。理解了它，很多树形结构查询都能用一个SQL搞定。

递归CTE的标准结构，包含三个部分：

WITH RECURSIVE cte AS ( -- 初始查询：anchor member，产生递归起点 SELECT id, name, parent_id FROM organization WHERE parent_id IS NULL UNION ALL -- 递归查询：recursive member，逐层向下扩展 SELECT o.id, o.name, o.parent_id FROM cte JOIN organization o ON o.parent_id = cte.id ) SELECT * FROM cte;

初始查询先取根节点，递归查询负责把当前层级的子节点找到，然后不断重复，直到JOIN不到任何新数据为止。整个递归过程类似“广度优先展开”。

UNION和UNION ALL的选择要特别注意：UNION会去重，避免出现重复行，逻辑上更安全；UNION ALL性能更好，但如果数据本身有环（比如父节点指向孩子再指向祖先），不加控制就会无限递归。实际业务数据里环并不常见，但自引用表的历史数据很容易出现脏数据，所以生产环境要权衡。

3.2 实操案例：组织架构与层级路径

假设有员工表：

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), manager_id INT NULL );

现在需要输出每个员工的完整汇报链路，比如“王总 > 李经理 > 张三”，并且要求同时算出层级深度。递归CTE可以这样写：

WITH RECURSIVE emp_path AS ( SELECT id, name, manager_id, name AS path_str, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, CONCAT(ep.path_str, ' > ', e.name), ep.depth + 1 FROM employees e JOIN emp_path ep ON e.manager_id = ep.id ) SELECT id, name, depth, path_str FROM emp_path ORDER BY depth, id;

运行结果会得到从顶层到每个节点的完整路径。上面这个SQL在生产项目里可以直接用于导出组织架构树、汇报线展示、甚至权限继承的模拟。

如果你要反向查询某个节点的所有子节点，也一样简单，只要把递归方向调换，从指定节点往下找即可。这里不放代码了，核心思路就是把锚点换成指定id，递归部分把父和子关系反过来。

3.3 防止无限递归：常见限制手段

MySQL的递归CTE默认有个循环次数限制，cte_max_recursion_depth，默认值1000，用于防止异常情况下无限执行。这个参数对很多表不够用，比如深层级组织架构可能超过1000层，那就需要按实际情况调高。

调整方式：

SET SESSION cte_max_recursion_depth = 5000;

这个参数是会话级的，不会立刻影响其他事务，安全性较好。如果出现报错“Recursive query aborted after 1001 iterations”，第一反应先看是不是真是层级深导致，第二反应再看是否有环路数据。如果是脏数据形成环，调大参数只能延长超时，正确做法是修数据或加访止条件。

注意：递归中如果发现某条路径反复出现相同节点，基本就是数据环了。可以在递归查询里加一个node_path字段，用FIND_IN_SET判断当前节点是否已经出现过，若出现则终止扩展。这是一个省事的防御性写法。

4. 实操过程：用 WITH 优化复杂查询的全流程记录

4.1 典型场景：商品类目汇总与环比分析

竞赛环节里，我们用一个模拟场景完整走一遍优化流程。业务需求：统计每个一级类目下各子类目的销售额，以及该子类目在上月同期的销售额，最终只输出当月排名前三的子类目。

原始SQL结构：

SELECT c1.id AS cat1_id, c1.name AS cat1_name, c2.id AS cat2_id, c2.name AS cat2_name, SUM(o.amount) AS cur_month_amount FROM orders o JOIN category c2 ON o.cat2_id = c2.id JOIN category c1 ON c2.parent_id = c1.id WHERE o.create_time BETWEEN '2025-03-01' AND '2025-03-31' GROUP BY c1.id, c2.id ORDER BY cur_month_amount DESC LIMIT 3;

这是只算当月数据的情况。如果加上上月同期的销售额，查找对应的上月订单明细，势必造成大量重复扫描。我当时的改造方法是，先用WITH把订单明细拆成“当月”和“上月”两个模块，再分别聚合，最后join。

首先定义两个CTE：

WITH cur_month_orders AS ( SELECT cat2_id, SUM(amount) AS cur_amount FROM orders WHERE create_time >= '2025-03-01' AND create_time < '2025-04-01' GROUP BY cat2_id ), last_month_orders AS ( SELECT cat2_id, SUM(amount) AS last_amount FROM orders WHERE create_time >= '2025-02-01' AND create_time < '2025-03-01' GROUP BY cat2_id ) SELECT c1.id AS cat1_id, c1.name AS cat1_name, c2.id AS cat2_id, c2.name AS cat2_name, COALESCE(cur.cur_amount, 0) AS cur_month_amount, COALESCE(last.last_amount, 0) AS last_month_amount FROM category c2 JOIN category c1 ON c2.parent_id = c1.id LEFT JOIN cur_month_orders cur ON cur.cat2_id = c2.id LEFT JOIN last_month_orders last ON last.cat2_id = c2.id ORDER BY cur_month_amount DESC LIMIT 3;

这样重构后，订单表被扫描的次数从原来可能需要多次扫描，变成两次按不同时间段扫描聚合，目的明确、逻辑清晰，也给了优化器更明确的“二段式聚合”路径。

4.2 用执行计划验证优化效果

改造完成后，我用EXPLAIN FORMAT=TREE看了一眼执行计划，CTE部分被标记为具体的子节点，能直观看到优化器是把它当作物化临时结果还是作为视图合并处理。

MySQL 8.0的优化器对CTE有“合并”和“物化”两种策略。如果CTE简单，优化器多半会把它合并到外层查询；如果CTE结构复杂、被多次引用，优化器则可能将其物化为内部临时表。

实际测试中，我发现一个坑：如果CTE被多次引用且数据量很小时，物化成本反而更高。比如一个只有十几行结果的CTE被JOIN了5次，物化之后再扫描代价也不高；但如果是几百MB数据量的CTE被引用两次，物化带来的临时表IO开销就很明显。

这种情况下我的处理方式是，减少CTE引用次数，或者提前在下层过滤条件做更彻底的裁剪。也就是说，CTE内部能过滤就过滤，别把全量明细带上来——这点和普通SQL优化思路完全一致。

4.3 参数层面的可调空间

递归CTE的迭代深度限制需要考虑，普通CTE同样需要关注tmp_table_size和max_heap_table_size。CTE物化产生的临时表如果没有足够的内存空间，就会落到磁盘，性能断崖下降。

我遇到过一张只有300万行的订单明细CTE，物化后临时表落盘，整个查询从0.3秒变成9秒。当时排查时看了状态变量Created_tmp_disk_tables，发现数量猛增。后来优化方案是给SQL增加前置过滤条件，让CTE内数据量从300万降到40万，临时表内存问题迎刃而解。

如果你在线上环境遇到CTE突然变慢，先查这两个状态变量：

SHOW SESSION STATUS LIKE 'Created_tmp_tables'; SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';

根据落盘临时表数量的变化，能快速判断是不是物化了。

5. 常见问题与限制核对，看完少走弯路

5.1 频繁踩到的错误和排查思路

我特别提一下“The column count of recursive query is not the same”这个错误。递归CTE要求锚点查询和递归查询的列数量严格一致，同时列类型也要兼容。比如锚点是INT，递归部分拼接出了一个字符串，立刻报错。这是我在写组织架构路径时遇到的，解决方案是让锚点也输出相同类型的路径字段，保持结构一致。

5.2 递归 CTE 的生产使用建议

递归在数据量大的树状结构里执行效率并非总能如人意。每层迭代都需要进行一次表扫描加JOIN递归结果集，当树的深度到几十层时，即使每层数据量不大，总的开销也不小。

我之前测过一张约10万员工的BOM层级表，递归CTE查单条物料链路大约耗时几十毫秒，但一次性查询全量树的路径，代价就很高。生产环境数据画像如下：物料编码表约8万行，父子关系约15万条。全树展开路径的递归SQL执行时间约4秒，主要是迭代次数多，且每层都需要扫描整张关系表。

因此，线上使用递归CTE时，我有几个习惯：

• 先确认业务数据的真实深度，并设置合理的cte_max_recursion_depth，而不是盲目调大。
• 锚点尽量先做条件过滤，让递归的起点数据量尽量小，这样每一层JOIN规模都会小很多。
• 如果只是统计层级数量和深度，考虑在应用层用程序遍历一次，再批量写入一张冗余表，比每次SQL递归要稳。
• 涉及多级BOM的实时查询，建议用物化路径方案替代递归，别让数据库每条查询都做递归展开。

5.3 WITH 子句在 MySQL 8.0 的适用边界

通用表达式不是优化银弹，它有明确的适用边界。首先，MySQL 8.0中的WITH不是永久视图，每条语句内的CTE生命周期只属于那条语句，不能跨语句复用。其次，CTE内部的索引使用逻辑和正常表一样，如果CTE内部没有可用的索引，外层JOIN时的性能依然不好。第三，同一个查询里如果存在多个大CTE，且它们之间没有相互依赖，优化器可能会并行物化，这在资源紧张时会放大IO压力。

我还遇到过一个问题：CTE内LIKE查询无法利用索引的情况。后来在CTE内部加全文索引的思路也被执行计划否决了。最后我换了个做法，先建一张临时表，添加索引，再执行查询，反而更快。

这提醒我一点：CTE清晰表达的是逻辑关系，但物理优化层面，临时表的显式索引在某些场景比CTE贪图方便更值得依赖。CTE适合用来替代嵌套子查询、减少重复计算、增强查询可读性；而不适合无脑把所有业务都写成巨型WITH块，让优化器承担巨大压力。

6. 个人实操体会与一个小建议

如果只让我说一条最重要经验，那就是：CTE不是用来炫技的，它最大的价值是让复杂SQL具有可读性和可维护性。我见过有人把一个简单的两表JOIN也套上三个WITH，反而比直接JOIN更难读。做事要权衡。

另外一个非常值得推荐的小练习方式：从你自己的业务库里找一条最痛的三层子查询，把它改写成WITH版本，然后对比改写前后的执行时间和表扫描次数。不用刻意去建复杂的树形数据，普通报表数据就能体会它带来的结构便利。你之前觉得无从下手的SQL，改着改着就会有一种“原来还能这么写”的通透感。

MySQL 8.0的通用表达式确实给日常开发带来了不少便利，但真正用好它，还是要把注意力放在业务逻辑和数据特性上。SQL写得好不好，归根结底是逻辑是否清晰、数据是否被合理利用，工具特性只是辅助。希望这篇文章能帮你少踩一点坑，把WITH子句用到真正该用的地方。