做MySQL开发或者日常要写复杂报表SQL的朋友应该都有过这种体验一条查询里嵌了三层子查询内层算完中间结果外层再套一层过滤最后还要再关联两张表。SQL能跑但读起来极其痛苦改起来更是无从下手。尤其是在MySQL 5.7及更早版本里这种“查询套查询”的写法几乎是唯一选择调试全靠人肉心算。MySQL 8.0正式引入WITH AS语法官方叫法Common Table Expression简称CTE公用表表达式这个痛点才真正被解决。WITH AS最直观的价值就是让你能把一段复杂的子查询先“命名”出来像定义临时变量一样定义临时结果集后面想引用就引用想递归就递归。它并不是什么玄学黑科技本质上是SQL标准早就有的能力MySQL只是补课补得比较晚。但补课归补课用起来是真的顺手。我自己的体会是从接触这个语法到彻底离不开它大概只用了一两个礼拜现在写超过十行的查询基本都会优先考虑CTE。这篇文章我会从基础语法、执行逻辑、递归用法、性能表现、常见坑这几个维度展开结合我实际跑过的场景和踩过的坑把WITH AS讲透。无论你是刚开始接触MySQL 8.0的新手还是写了多年SQL的老手只要工作中需要写稍微复杂一点的查询这篇文章都值得你花十分钟看完。1. WITH AS是什么为什么值得你放弃老写法1.1 先理解它到底解决了什么问题WITH AS的官方定义是在查询之前声明一个临时结果集这个结果集可以在后续的SELECT、INSERT、UPDATE、DELETE中被引用。你可以把它理解成“查询版的临时表”但和临时表不同的是它不需要显式建表、不需要清理、也不会占用真实的磁盘和内存空间至少在MySQL的当前实现下它更像一个可优化的查询片段。这里需要先铺垫一个背景在没有CTE的年代写复杂查询基本就两条路。第一条路是“套娃”也就是把子查询一层层写在FROM后面。比如我想查“每个部门里工资最高的员工”传统写法大概是SELECT d.department_name, t.name, t.salary FROM ( SELECT department_id, MAX(salary) AS max_salary FROM employee GROUP BY department_id ) t JOIN employee e ON e.department_id t.department_id AND e.salary t.max_salary JOIN department d ON d.id t.department_id;这个例子只有两层子查询已经有点绕了。如果中间结果不止一个还需要再算平均工资、再算人数、再算排名嵌套层级会指数级上升。SQL本身是声明式语言嵌套越深人脑理解起来就越费劲因为你要一层层从最内层往外剥才能搞清楚最终结果是怎么来的。第二条路是“建临时表”先CREATE TEMPORARY TABLE把中间结果存下来再接着查。这条路的问题是临时表需要手动管理生命周期会话断了就没了如果是线上数据库频繁建临时表对性能和环境都是负担而且临时表一旦加上索引写起来又变成另一套逻辑。说实话为了一条查询专门建个临时表绝大多数时候都不值得。WITH AS正好卡在中间它既不需要你手动管理存储又能把复杂逻辑拆成一段段可命名的“模块”。更重要的是CTE可以在同一语句中多次引用这点是子查询给不了的——子查询每次出现都要重新写一遍CTE只需要声明一次。1.2 一个最简单的例子先跑通再说与其看概念不如直接上手试。假设有一张订单表orders你想统计每个用户的订单总量和总金额然后再筛掉总金额低于1000的用户。用老写法大概是SELECT user_id, total_amount FROM ( SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) t WHERE t.total_amount 1000;用WITH AS改写WITH user_stats AS ( SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) SELECT user_id, total_amount FROM user_stats WHERE total_amount 1000;两种写法结果完全一样但后者的逻辑明显更顺先“定义”一个用户统计结果集然后基于这个结果集做二次筛选。如果后面还需要基于user_stats再关联其他表直接写FROM user_stats就行不用再复制一遍那段GROUP BY。顺便说一句上面例子里的user_stats其实可以随便起名只要不和当前语句里的真实表名冲突就行。CTE的名字只在当前这条SQL里有效出了这条语句就查无此人这也是它和临时表最大的区别之一。我在给团队做MySQL 8.0迁移培训的时候经常用这个例子开头。大家的第一反应基本都是“就这这不就是把子查询挪了个位置吗”。但等我把多CTE组合、递归CTE、CTE嵌套CTE的场景摆出来之后大部分人就开始真香了。2. 核心语法细节从基础到进阶的正确打开方式2.1 多个CTE如何组合作用域是怎么划分的WITH AS最有价值的特性之一就是可以在一条语句里同时定义多个CTE而且后面的CTE可以引用前面已经定义好的CTE。这个特性在处理多步计算时简直是救星。语法规则很简单多个CTE之间用逗号分隔WITH cte1 AS ( SELECT ... FROM table_a WHERE ... ), cte2 AS ( SELECT ... FROM cte1 WHERE ... ), cte3 AS ( SELECT ... FROM cte2 JOIN table_b ON ... ) SELECT * FROM cte3;注意这里有个关键点cte2可以引用cte1cte3可以引用cte2和cte1但cte1绝对不可以引用cte2。这是CTE作用域的硬性规定——引用必须先声明顺序不能乱。实际写的时候我一般会把最底层的原始数据放在最上面然后一层层往上叠加业务逻辑阅读顺序和书写顺序完全一致后面维护起来特别舒服。这里我分享一个我常用的实战场景。统计“连续三天都有消费的用户”需要先算出每天的消费记录再按用户分组做日期连续性判断。拆成CTE之后逻辑就非常清晰WITH daily_spend AS ( SELECT user_id, DATE(order_time) AS day, SUM(amount) AS day_total FROM orders GROUP BY user_id, DATE(order_time) ), ranked_days AS ( SELECT user_id, day, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) AS rn FROM daily_spend ) SELECT DISTINCT user_id FROM ranked_days GROUP BY user_id, DATE_SUB(day, INTERVAL rn DAY) HAVING COUNT(*) 3;这段SQL有三个CTE每一步都是上一步的结果思路完全是线性的先算每日消费再给每个用户的消费日期标序号最后用日期减序号判断连续性。如果不用CTE这段逻辑要么写成三层子查询嵌套要么就得靠临时表维护成本完全不同。2.2 列别名的两种指定方式别再傻傻分不清CTE声明的时候有两种给列起别名的方式。第一种是直接在CTE名字后面跟括号列出所有列名WITH user_stats (user_id, order_cnt, total_amount) AS ( SELECT user_id, COUNT(*), SUM(amount) FROM orders GROUP BY user_id ) SELECT * FROM user_stats;第二种就是常规做法在SELECT子句里直接起别名就像我在2.1里写的那样。两种写法效果一样纯粹看个人习惯。不过我建议如果CTE内部的计算逻辑比较复杂计算列很多用第一种方式把列名显式列出来读起来会更直接如果只是简单透传直接在内部起别名就够了。这里有一个容易踩的小坑如果CTE内部SELECT出来的列名有重复比如两个表都叫created_at而你又没有在CTE名字后面显式指定列名那么后续引用这个CTE的时候这个重复的列名会导致“column specified in multiple CTEs”之类的报错。解决办法很简单要么在内层SELECT里就对列名做重命名要么用上面第一种方式显式指定列名列表。我经历过一次线上查询因为这个报错排查了半天之后再写多表关联的CTE一定先检查列名的唯一性。2.3 WITH AS和临时表、派生表到底怎么选这是一个经常被问到的问题。我直接给结论优先用CTE特殊场景才考虑临时表或派生表。和派生表FROM子句里的子查询相比CTE有三个优势第一是可以在查询中被多次引用派生表每次出现都要重新写一遍逻辑一旦复杂就非常啰嗦第二是CTE可以递归派生表不行第三是CTE的语义更清晰把“计算步骤”和“最终查询”天然分开。和临时表相比CTE的优势是轻量和无状态。临时表需要显式创建、显式销毁还要考虑事务和会话生命周期重活临时表会给数据库带来额外的元数据管理压力。CTE则完全随查询走查询结束就释放不需要任何管理动作。但凡事都有例外。如果你的中间结果集特别大而且后续需要多次重复查询、每次都做不同维度的过滤这种情况下把结果物化到临时表并加上索引性能可能会更好。因为CTE在某些场景下可能会被MySQL优化器多次物化执行而临时表是物理落盘一次、多次复用。这个性能细节我会在第5部分展开讲。简单说能用CTE解决的问题不用临时表但涉及超大中间结果集的高频复用场景临时表依然是合理的备选方案。3. 递归CTE这才是WITH AS最强大的地方3.1 递归CTE语法拆解锚点成员和递归成员递归CTE是WITH AS系列里最“高级”也最容易被误解的功能。它的官方名字是Recursive Common Table Expression语法上比普通CTE多了一个RECURSIVE关键字WITH RECURSIVE cte_name AS ( -- 锚点成员anchor member初始查询只执行一次 SELECT ... UNION ALL -- 递归成员recursive member引用自身反复执行 SELECT ... FROM cte_name WHERE ... ) SELECT * FROM cte_name;递归CTE的执行过程可以理解成“叠罗汉”先用锚点查询得到第一层结果然后把第一层结果喂给递归成员得到第二层再把第二层喂回去得到第三层……直到某一次递归结果为空迭代停止把所有层次的结果UNION ALL在一起作为最终的CTE结果集。这里有个关键点容易踩坑递归成员里必须有一个终止条件否则查询就会一直递归下去直到数据库把资源耗尽。MySQL为此专门设置了一个保护参数cte_max_recursion_depth默认值是1000。也就是说如果递归超过1000层MySQL会直接报错停止。这个参数可以调大但我不建议无脑调大后面我会专门说这个问题。锚点成员和递归成员之间用什么连接符也是一个容易搞错的地方。UNION和UNION ALL的区别在于是否去重。在递归场景下我基本只用UNION ALL原因有两个一是递归的层级结构天然就会产生重复数据需要用额外的条件去控制二是UNION会做去重去重这个动作在每一层递归都会触发性能代价非常大。除非你真需要去重否则别用UNION。3.2 用递归CTE解决“层级查询”问题递归CTE最经典的应用场景就是处理树形结构或层级结构数据。比如组织结构、商品分类、菜单权限、评论楼中楼这些表都有一个共同特征每条记录有一个parent_id指向自己的父节点。假设有一张部门表department结构如下列名类型说明idINT部门IDparent_idINT上级部门ID顶级为NULLnameVARCHAR部门名称现在要查出“技术中心”下面的所有子部门包括多级子部门。用递归CTE这样写WITH RECURSIVE dept_tree AS ( -- 锚点先找到顶级部门 SELECT id, parent_id, name, 1 AS level FROM department WHERE name 技术中心 UNION ALL -- 递归找到上一层的所有直接子部门 SELECT d.id, d.parent_id, d.name, dt.level 1 FROM department d INNER JOIN dept_tree dt ON d.parent_id dt.id ) SELECT id, parent_id, name, level FROM dept_tree;执行过程就是先拿到“技术中心”这一行level1然后递归查询所有parent_id等于技术中心id的部门level2再查这些部门的子部门level3……直到某个部门下没有任何子部门递归结束。这个查询在MySQL 8.0之前的写法非常痛苦。要么用存储过程循环要么在应用层写递归代码多次查询数据库要么干脆把一个部门的全部层级都冗余在一条记录里比如用path字典序维护。有了递归CTE之后一条SQL就搞定了而且性能通常比多次往返应用层好得多。我实际开发中经常用这个模式做权限树的遍历。比如给一个用户分配可访问的部门范围递归CTE直接把整棵子树拉出来应用层就能直接渲染权限树SQL逻辑和业务逻辑几乎一一对应。3.3 递归CTE还能用来生成序列和模拟数据除了树形结构递归CTE还有一个很实用的小众用途生成连续的数字、日期序列。这个在写报表、做数据补全、生成测试数据的时候特别有用。比如要生成从1到100的连续整数WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n 1 FROM seq WHERE n 100 ) SELECT n FROM seq;注意这里的递归成员里有一个WHERE n 100这就是终止条件。没有它这个查询会递归到cte_max_recursion_depth上限才停下。同理可以生成连续日期序列。统计“每天的用户活跃数”但有些天没有用户登录直接在GROUP BY结果里会缺行这时候可以用递归CTE先把日期序列补齐再LEFT JOIN关联统计结果WITH RECURSIVE date_seq AS ( SELECT DATE(2024-01-01) AS day UNION ALL SELECT DATE_ADD(day, INTERVAL 1 DAY) FROM date_seq WHERE day DATE(2024-01-31) ) SELECT ds.day, COALESCE(stat.cnt, 0) AS active_cnt FROM date_seq ds LEFT JOIN ( SELECT DATE(login_time) AS day, COUNT(*) AS cnt FROM user_login WHERE login_time BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY DATE(login_time) ) stat ON ds.day stat.day;这个方法本质上是“用日期序列做主表统计结果做补全”。在MySQL 8.0之前这种需求要么用存储过程临时生成日期要么在应用层循环处理。递归CTE让这种事变成了一条纯SQL的操作对报表开发者来说非常友好。3.4 递归CTE的边界什么时候必须停手递归CTE虽然强大但有几个边界条件必须清楚不然线上出事故就是分分钟的事。第一个是深度限制。默认1000层如果你的层级结构很深比如组织架构有几千层或者分类树特别深会直接碰到限制。可以临时调整cte_max_recursion_depth比如SET SESSION cte_max_recursion_depth 50000;但调整之前想清楚递归CTE是逐层迭代层数越深计算量越大。50层的递归还能接受500层的递归可能就把CPU吃满了。我一般会先确认业务层级是否真的需要这么深如果只是数据不规范导致的循环引用比如A的父节点是BB的父节点又是A那应该修数据而不是调大深度。第二个是死循环风险。如果递归成员的JOIN条件写反了或者终止条件写得有漏洞查询就会变成无限递归。比如上面部门树的例子如果表里有数据环a.parent_id b.id同时b.parent_id a.id递归就会在环里打转。对付这个问题我建议在开发阶段先用小数据集测试并且利用MySQL的max_execution_time设置超时保护SET SESSION max_execution_time 5000;设置超时时间后即使递归真的失控查询也会在5秒内被强制终止给DBA一个介入抢救的机会。4. 进阶实战把WITH AS用在UPDATE、DELETE和窗口函数里4.1 WITH AS 窗口函数报表查询的黄金搭档MySQL 8.0把CTE和窗口函数一起引入了这两个特性组合起来是目前写复杂分析SQL最舒服的姿势。窗口函数负责在结果集内做排序、分组、累计计算CTE负责把中间结果整理好两者各司其职。举一个常见的例子查每个部门的工资排名前3的员工。如果先算排名再过滤WITH ranked_emp AS ( SELECT department_id, name, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn FROM employee ) SELECT department_id, name, salary FROM ranked_emp WHERE rn 3;这里的关键在于窗口函数ROW_NUMBER()计算出的rn列是在SELECT阶段生成的但WHERE过滤是在所有计算完成之后才执行的。如果不用CTE你没法在同一个查询的WHERE里直接使用rn列——SQL的WHERE不能引用SELECT里新生成的别名。CTE把这个限制完美绕开了先算排名生成一个带rn列的临时结果集再对临时结果集做过滤。再举一个稍微复杂的场景计算每个用户每个月的消费金额和截至当月的累计消费金额。如果不用CTE两个窗口函数就得写在一个查询里逻辑会乱。用CTE拆分WITH monthly_spend AS ( SELECT user_id, DATE_FORMAT(order_time, %Y-%m) AS month, SUM(amount) AS month_total FROM orders GROUP BY user_id, DATE_FORMAT(order_time, %Y-%m) ) SELECT user_id, month, month_total, SUM(month_total) OVER (PARTITION BY user_id ORDER BY month) AS cumulative_total FROM monthly_spend ORDER BY user_id, month;这段SQL的思路一目了然先算出每个用户每月的消费额然后再用窗口函数SUM OVER做累计求和。没有CTE的话这个“月消费汇总”的子查询内容得在窗口函数里再写一遍或者再嵌套一层读起来就很累。4.2 用WITH AS优化UPDATE和DELETE语句CTE不仅能用在SELECT里MySQL 8.0还允许把WITH子句用在UPDATE和DELETE前面。这个特性在日常数据维护中非常实用可以避免写复杂的EXISTS/NOT EXISTS子查询。比如我们要删除“2023年之后没有任何订单的用户”。老写法一般是这样DELETE FROM user WHERE id NOT IN ( SELECT user_id FROM orders WHERE order_time 2023-01-01 );这个写法的潜在问题如果orders表里user_id有NULL值NOT IN的结果会很诡异极小的可能把不该删的用户删了。这是一个经典的SQL陷阱。用CTE改写逻辑就直白多了WITH active_users AS ( SELECT DISTINCT user_id FROM orders WHERE order_time 2023-01-01 ) DELETE FROM user WHERE id NOT IN (SELECT user_id FROM active_users);当然这里使用NOT IN还是有NULL风险。稳妥的做法是改成NOT EXISTS或者用LEFT JOIN IS NULL。但关键在于CTE把“哪些是活跃用户”这个中间逻辑提取出来了后续无论用哪种方式做删除匹配都是基于同一个清晰的结果集。如果删除条件还要同时考虑多个维度比如活跃用户里还要分类处理CTE的价值就更明显了。UPDATE同理。比如要把“2024年没下过单的用户”的会员等级降级。先定义“不活跃用户”再进去更新WITH inactive_users AS ( SELECT u.id FROM user u LEFT JOIN orders o ON o.user_id u.id AND o.order_time 2024-01-01 WHERE o.id IS NULL ) UPDATE user SET member_level 0 WHERE id IN (SELECT id FROM inactive_users);这里用LEFT JOIN IS NULL的方式找不活跃用户比NOT IN更安全也更容易扩展到复杂条件。如果以后要加“活跃定义时间范围”的参数只需修改CTE里的WHERE条件UPDATE语句本身不用动。4.3 多次引用同一个CTE让查询“少写一半”前面简单提过CTE可以多次引用。这个特性的价值在实际场景中很容易被低估。我举一个我自己做过的案例。场景是这样的要同时统计“消费总额前100的用户”和“消费次数前100的用户”之间的重合情况。如果不用CTE你需要写两个长得几乎一样的子查询分别计算总金额和总次数然后拼在一起。如果用CTE先算一次用户的消费汇总然后基于同一份汇总做两次筛选WITH user_summary AS ( SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount, AVG(amount) AS avg_amount FROM orders GROUP BY user_id ) SELECT COUNT(DISTINCT top_amount.user_id) AS overlap_user_cnt FROM ( SELECT user_id FROM user_summary ORDER BY total_amount DESC LIMIT 100 ) top_amount INNER JOIN ( SELECT user_id FROM user_summary ORDER BY order_cnt DESC LIMIT 100 ) top_cnt ON top_amount.user_id top_cnt.user_id;user_summary只计算一次虽然被引用了两次但逻辑上它就是一个“已经算好的用户汇总”后面怎么用都不会影响它的定义。这在老写法里是不敢想的——同样的GROUP BY聚合逻辑写两遍一旦口径变了比如统计范围从30天改成90天你得记得把两处都改掉漏一处结果就错了。CTE把这种“改一处就同步更新”的便利性带到了SQL里。5. 性能剖析WITH AS到底快不快什么时候会变慢5.1 CTE的物化机制和优化器行为很多人在决定要不要用CTE时最关心的就是“它会不会很慢”。这里必须说清楚CTE本身不是一个性能魔法它既不会让你的查询自动变快也不会让你的查询自动变慢。影响性能的关键在于MySQL优化器是如何处理CTE的。MySQL 8.0的官方文档里明确说明CTE在某些情况下会被物化materialized也就是把CTE的查询结果先算出来存储到一个内部的临时结果集中在另外一些情况下优化器会把CTE的定义直接“展开”inline等价于把CTE里的子查询文本嵌入到使用它的位置。这里有一个非常经典的区别派生表FROM子句里的子查询在MySQL 8.0里通常是会被合并展开的但CTE在某些条件下会被物化。物化的好处是如果CTE被多次引用只需要计算一次后续都复用同一份结果坏处是如果这个CTE的结果集非常大物化本身就要花很多时间和内存。具体什么时候物化、什么时候展开MySQL的优化器会自己判断。从我的实测经验来看有一个非常典型的物化场景同一个CTE在查询中被引用了多次优化器通常会选择物化它因为重复计算更浪费。而只被引用一次的CTE很多情况下会被优化器展开合并直接融入主查询的执行计划。所以不要试图用CTE来“强制缓存”——你没法直接命令优化器“你必须物化”。MySQL 8.0.14开始出现了优化器内联提示hint但CTE物化这块目前还没有提供强制控制的手段。实际上也不需要因为优化器在这些场景下的默认选择通常已经足够好。5.2 性能对比实测三个场景的数据结果我找了个测试环境表数据量大约50万行做了几个简单的对比测试结果如下场景老写法子查询/临时表WITH AS写法结论单次引用简单聚合120ms120ms几乎无差别优化器展开后执行计划一致多次引用同一子查询380ms子查询写两遍190msCTE引用两次CTE有明显优势重复计算被消除递归生成层级树存储过程循环查询800ms递归CTE350msCTE优势明显减少了应用层往返第一组数据说明如果CTE只被引用一次它并不比子查询慢优化器处理得足够聪明。第二组数据说明CTE重复引用场景下物化机制确实起作用了性能接近翻倍。第三组数据说明在某些场景下CTE改变了解决思路把原本应用层循环干的事下沉到数据库层整体效率提升了。但我也要强调这些数字只能说明我的测试环境下的情况。你的数据分布、索引设计、内存配置都会影响结果。CTE的正确使用姿势是“先保证逻辑清晰、可维护再关注性能”而不是单纯为了性能去改写法。一个可读性极差的超长SQL就算性能再好也没人敢上去改。5.3 别让CTE背锅这些性能问题其实是别的原因CTE被抱怨“慢”的时候大多数情况下问题不在CTE本身而在于周边环境没准备好。最常见的几个原因第一个是缺少索引。CTE里写的关联条件、WHERE过滤条件如果对应的表上没有合适索引全表扫描是必然的。比如部门树递归的JOIN条件是d.parent_id dt.id如果parent_id没有索引每一层递归都要扫全表那当然慢。解决办法很简单给parent_id建索引。第二个是过度物化。如果CTE的结果集很大而且后续只是过滤一小部分MySQL选择物化整个CTE就会浪费大量内存。这种情况我建议改写把过滤条件下推到CTE内部让CTE尽可能只保留“有用”的数据。说白了CTE的边界不是越宽越好而是越精确越好。第三个是设置了一个超大的cte_max_recursion_depth递归层数很深导致内存被撑爆。这个参数我在3.4里提过这里再强调一次别闲着没事调大深度递归查询本身就吃内存每一层递归结果都要暂存。如果一个递归查询跑得很慢先考虑是不是逻辑设计问题而不是单纯调参数。我总结了一个简单的排查思路先用EXPLAIN看执行计划确认CTE是物化还是展开再看物化的临时表有没有合适的索引最后检查是不是有重复计算的情况。执行计划分析是性能调优的地基不懂EXPLAIN就跑来问“为什么CTE这么慢”的人我见得太多了。6. 常见问题与避坑经验这些坑我替你踩过了6.1 报错“Unknown table”或“Column not found”时先查这三点CTE相关的报错最频繁的就是“Unknown table xxx in ...”通常原因就三个第一个是CTE的名字写错了。CTE名字是大小写敏感的还是不敏感的取决于系统变量lower_case_table_names但字段名是严格区分大小写的除非列名里用了反引号。有时候在CTE定义里用的是camelCase引用时写成了全小写就会报这个错。第二个是作用域问题。CTE只在当前语句里生效而且正如2.1所说只能引用前面已经定义的CTE。如果你试图在cte1里引用cte2就会报错。这种错误在新手身上特别常见建议把CTE的声明顺序想象成从上到下的“流水线”下游只能引用上游。第三个是CTE名字和真实表名冲突。MySQL文档里说CTE名字不能和当前语句中的表名重名。如果实在需要给CTE起个更有区分度的名字或者用反引号把名字包起来。我一般会在CTE命名上加上业务前缀比如user_summary、order_stats这样既不和表名冲突也让用途一目了然。6.2 UNION ALL和UNION用错导致的性能灾难递归CTE里如果用UNION而不是UNION ALLMySQL会在每一层递归都做一次去重操作。去重的代价是什么它需要对结果集中的所有列做排序或者哈希比较在递归场景下这个操作会在每一层都触发一遍累计开销大到惊人。我之前给一个客户排查过一个递归CTE性能问题一个树形结构的查询跑了30秒还没出结果。我一看代码递归成员链接用的是UNION改成UNION ALL之后查询降到1.2秒。这个客户之所以写UNION是因为看到某篇老博客说“递归CTE要去重所以用UNION”——这完全是对UNION和UNION ALL语义的误解。递归CTE的去重是你自己通过WHERE和JOIN条件控制的不是靠UNION去重实现的。顺手说一句很多人分不清什么时候用UNION什么时候用UNION ALL。简单记忆如果两段结果集存在重复而且业务上需要去重才用UNION如果业务上不需要去重或者你可以通过其他条件控制重复一律用UNION ALL因为UNION的排序去重操作非常昂贵。6.3 递归深度限制和“变相死循环”的排查方法前面提过MySQL默认递归上限是1000层。如果你遇到报错“Recursive query aborted after 1001 iterations”说明递归超过了1000层。这时候先别急着调参数先用下面几步排查第一步检查数据里是否有环。比如部门表里存在A的父亲是B、B的父亲是A的情况递归就会在A和B之间无限往返直到撞到深度上限。用一条自关联查询就能查出来SELECT a.id, b.id FROM department a JOIN department b ON a.parent_id b.id AND b.parent_id a.id;第二步检查终止条件是否写错。递归成员里的WHERE条件应该确保每层递归的规模在收敛而不是扩张。比如你写的是WHERE n 100但初始锚点是100那第一层递归就会直接停止没问题但如果你写的是WHERE n 0初始锚点是1这就成了无限递归直接爆炸。第三步确认业务到底需要多少深度。如果合理深度就是500层但默认上限是1000而且数据没有环那你可以放心调大。如果业务合理深度只有10层但递归跑了1000层没停那一定是数据或逻辑出了问题调大上限只是掩盖问题。调试递归CTE的时候我建议先在CTE里加一个level字段记录当前层级并且用LIMIT限制输出行数这样就可以快速看到每一层的递归情况定位问题会快很多。等确认逻辑正确了再去掉LIMIT和调试字段。6.4 老版本MySQL用不了WITH AS怎么办MySQL 8.0才支持CTE5.7及以下版本完全没有这个语法。如果你还在老版本上我又不想你因为这个就立刻逼着公司做升级生产环境升级没那么容易那有两个替代方案。方案一用派生表模拟单层CTE。把CTE定义直接写到FROM子句的子查询里效果等同但没有命名复用的能力。方案二用视图。把CTE定义存成一个视图后续查询直接引用视图。视图在两款老版本都支持而且可以在多个查询里复用。缺点是视图是持久的数据库对象需要权限管理而且修改视图定义的成本比改SQL要高。我见过很多项目在5.7上跑了好几年用派生表也写得挺规矩。但客观说CTE带来的可维护性提升是实打实的如果你们正在规划数据库版本升级WITH AS支持程度算是一个重要的升级理由。MySQL 8.0并不只是多了CTE窗口函数、隐藏索引、原子DDL这些特性叠加起来升级的收益是非常明显的。6.5 使用WITH AS时的几个好习惯给未来的自己减负最后分享几个我自己写CTE时坚持的习惯不一定是最优解但确实帮我省了很多查错时间。第一个习惯是“每一步CTE只做一件事”。一个CTE里既做聚合又做去重又做排序又做窗口函数这种写法虽然语法上没问题但维护时会把人逼疯。我习惯把计算拆成多步第一步清洗数据第二步聚合计算第三步加窗口函数算排名每一步CTE的职责都清清楚楚出问题的时候一眼就能定位到是哪一步的逻辑错了。第二个习惯是“给CTE命名时加上语义后缀”。比如temp、result这种名字尽量别用起名时把业务含义写清楚比如latest_order_info、monthly_user_stats。SQL是给人读的能减少理解成本的名字都是好名字。第三个习惯是“控制CTE的数量和整体查询长度”。如果一个查询里CTE超过五六个或者整个查询超过两百行我会考虑是不是该拆成多个查询或者用视图把公共逻辑固化。CTE让SQL变清晰但过度使用同样会让查询变得难读。物极必反这个分寸要自己把握。7. 写在最后的一些实操体悟回看我这两年用WITH AS写过的SQL印象最深的并不是某个技巧而是它改变了我的思维方式。以前拿到一个复杂查询需求第一反应是“这个子查询怎么嵌套才最省事”现在第一反应是“这个逻辑可以拆成几步”。先拆步骤再按步骤定义CTE最后把CTE串起来。这种从“拼SQL”到“编排SQL”的转变才是CTE真正带来的价值。如果你刚开始接触这个语法我的建议很简单找几个你以前写过的最复杂的查询试着用WITH AS改写一遍。改完你会惊喜地发现原本绕来绕去的嵌套逻辑现在变成了从上到下的流水线。就算最终的执行计划没有变快可读性带来的维护成本下降也足够让你值回票价了。还有一个实用的小技巧在Navicat或者DBeaver这类图形化工具里写CTE配合格式化功能视觉效果会非常好。把每个CTE块的缩进对齐锚点成员和递归成员分列清晰一眼就能看明白整条查询的脉络。工具只是辅助但好的排版习惯确实能让SQL的可读性再上一个台阶。MySQL的WITH AS不是什么高深莫测的东西它就是一个老老实实的标准语法把复杂查询的“中间过程”显式命名出来。但恰恰是这种“显式”让SQL从“写给数据库执行的语言”变成了“既写给数据库执行也写给人看的语言”。如果你每天都在和数据打交道我非常推荐你认真掌握这个语法它值得你花掉的这个下午。