mysql 8.0中cte必须以with（递归需with recursive）为最外层前缀，仅支持select/insert/update/delete前直接使用；禁止嵌套子查询、漏写recursive、用union distinct或在left join右表引用递归cte。
WITH 是 MySQL 8.0 引入 CTE 的唯一入口，不支持低版本的模拟写法。如果你在 MySQL 5.7 或更早版本里试图用 WITH，会直接报错 ERROR 1064 (42000) —— 这不是语法写错了，是版本不支持。
普通 CTE 怎么写才不报错
最常踩的坑是把 WITH 放在 SELECT 后面、或者嵌套在子查询里。CTE 必须作为整个语句的**最外层前缀**，且只能紧挨着第一个 SELECT / INSERT / UPDATE / DELETE。
正确写法示例：
WITH recent_sales AS ( SELECT order_id, customer_id, amount FROM orders WHERE order_date >= '2025-01-01' ) SELECT customer_id, SUM(amount) AS total FROM recent_sales GROUP BY customer_id;
错误写法（常见）：
-
SELECT * FROM (WITH t AS (...) SELECT ...) s;—— CTE 不能出现在子查询中 -
SELECT ..., (WITH t AS (...) SELECT ...) FROM ...;—— 不支持标量子查询里用WITH - 漏写列别名但子查询字段含表达式，如
WITH t AS (SELECT id+1) SELECT * FROM t;—— MySQL 要求显式命名列或依赖子查询自动推导（有风险）
递归 CTE 必须加 RECURSIVE 关键字
MySQL 对递归 CTE 的语法比标准 SQL 更严格：WITH RECURSIVE 是强制的，漏掉 RECURSIVE 就算逻辑再对也会报错 ERROR 1223 (HY000): Can't execute the query because it contains recursion。
锚定部分（非递归）和递归部分必须用 UNION ALL 连接，UNION DISTINCT 在递归 CTE 中被禁止（即使语法上允许，执行时会失败）。
递归部分只能引用一次 CTE 名，并且**只能出现在 FROM 或 JOIN 子句中**；不能出现在 WHERE 的子查询里，也不能在 LEFT JOIN 的右表位置 —— 否则触发 ERROR 3641 (HY000): Recursive reference to CTE 'xxx' is not allowed in this context。
示例（查某员工的所有下属）：
WITH RECURSIVE subordinates AS ( SELECT emp_id, name, manager_id FROM employees WHERE manager_id = 333 -- 锚定：直属下属 UNION ALL SELECT e.emp_id, e.name, e.manager_id FROM employees e INNER JOIN subordinates s ON e.manager_id = s.emp_id -- 仅此处可引用 ) SELECT * FROM subordinates;
CTE 能不能代替临时表或视图
能，但要看场景。CTE 是**语句级临时结果集**，只在当前查询生命周期内存在，不生成物理对象，也不缓存结果 —— 所以多次引用同一个 CTE（比如在 JOIN 里用了两次），MySQL 默认会重复执行其定义子查询（除非优化器判定可复用）。
对比：
- 临时表（
CREATE TEMPORARY TABLE）：显式物化，可加索引，适合多步操作或大数据量中间结果 - 视图（
CREATE VIEW）：持久化定义，无数据存储，每次调用都重执行底层查询 - CTE：轻量、不可索引、作用域最小，适合提升单个复杂查询的可读性与逻辑分层
性能提示：如果 CTE 子查询本身很重（如全表扫描 + 多表 JOIN），又在主查询中被多次引用，不如先写进临时表。MySQL 8.0 不支持 CTE 物化提示（如 MATERIALIZED），也暂无等价于 PostgreSQL 的 NOT MATERIALIZED 控制开关。
递归深度超限怎么办
MySQL 默认递归上限是 1000 层（由系统变量 cte_max_recursion_depth 控制）。如果树太深（比如组织架构超过千级），会报错 ERROR 3636 (HY000): Recursive query aborted after 1001 iterations。
临时调高方法（需有 SUPER 权限）：
SET SESSION cte_max_recursion_depth = 2000;
但要注意：这不是万能解。过深递归会导致栈膨胀、内存占用陡增，甚至触发 OOM。真正该做的，是检查是否真的需要遍历全部层级 —— 很多业务场景其实只需 3~5 层（如“我的直属、我的下属的下属”），这时可以在递归 WHERE 条件里加层数控制：
WITH RECURSIVE tree AS ( SELECT id, name, manager_id, 1 AS level FROM employees WHERE id = 333 UNION ALL SELECT e.id, e.name, e.manager_id, t.level + 1 FROM employees e INNER JOIN tree t ON e.manager_id = t.id WHERE t.level <p>递归 CTE 的真实难点不在语法，而在如何避免无限循环和合理剪枝 —— 锚定查询必须确保不返回空集，递归条件必须保证最终收敛，否则查询会卡死或超时。</p>
