MySQL 8.0递归CTE基本写法为：WITH RECURSIVE cte_name AS (初始查询 UNION ALL 递归查询)，其中必须显式使用RECURSIVE关键字，仅允许UNION ALL连接两部分，递归引用须在FROM或JOIN中出现，且顺序不可颠倒。
MySQL 8.0 递归 CTE 的基本写法长什么样
MySQL 8.0+ 才支持递归 WITH，语法必须包含 RECURSIVE 关键字，且递归部分只能引用自身一次、必须放在 UNION ALL 右侧。漏掉 RECURSIVE 或写成 UNION 会直接报错 ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY。
典型结构是：
WITH RECURSIVE t AS ( SELECT id, name, parent_id, 1 AS level FROM categories WHERE parent_id IS NULL -- 初始查询（锚点） UNION ALL SELECT c.id, c.name, c.parent_id, t.level + 1 FROM categories c INNER JOIN t ON c.parent_id = t.id -- 递归关联，必须用 INNER JOIN 避免 NULL 匹配 ) SELECT * FROM t;
注意：t 在递归分支里既是被查表，也是正在构建的临时结果集，不能在 WHERE 中对 t 做非关联过滤（比如 t.level 要放到外层 <code>SELECT）。
为什么递归查询总卡死或只返回一层
最常见原因是递归没有终止条件，或连接逻辑错误导致自循环。MySQL 默认递归深度上限是 1000（由系统变量 cte_max_recursion_depth 控制），超限会报错 ERROR 3636 (HY000): Recursive query aborted after 1001 iterations。
- 检查
ON条件是否可能让某行反复匹配自己（例如parent_id = id写反了） - 确保初始查询（锚点）和递归查询的数据域不重叠，否则可能无限生成相同记录
- 如果业务上树深通常不超过 5 层，可显式限制：
SET cte_max_recursion_depth = 10;（会话级生效） - 用
level字段辅助调试：在递归分支中加t.level + 1，查出来看是否出现重复 level 或突增
如何安全地查询某个节点的所有祖先（向上递归）
向下查子节点是自然的父子关联，但向上查父节点容易忽略根节点边界。错误写法是让 parent_id 关联 id 却不限制 parent_id IS NOT NULL，会导致最后一条记录重复自身。
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
正确模式：
WITH RECURSIVE ancestors AS ( SELECT id, name, parent_id, 0 AS distance FROM categories WHERE id = 123 -- 目标叶子节点 UNION ALL SELECT p.id, p.name, p.parent_id, a.distance + 1 FROM categories p INNER JOIN ancestors a ON p.id = a.parent_id WHERE p.parent_id IS NOT NULL -- 关键！终止于根（根的 parent_id 为 NULL） ) SELECT * FROM ancestors;
这里 WHERE p.parent_id IS NOT NULL 必须写在递归分支内，而不是外层 SELECT —— 否则最后一轮仍会尝试用 NULL 去关联，触发空结果但不报错，逻辑就断了。
性能差？可能是索引没覆盖递归路径字段
递归 CTE 本身不走索引优化，MySQL 是把每次递归结果当临时表再做 JOIN。如果 categories 表很大，parent_id 字段没索引，每次 INNER JOIN 都是全表扫描。
必须确认：
-
parent_id有单独索引，或作为联合索引的前导列（如INDEX(parent_id, id)） - 避免在递归分支的
SELECT中用函数包裹关联字段（如COALESCE(c.parent_id, 0)），这会让索引失效 - 如果只查固定深度（如最多 3 级子类），用 3 层
LEFT JOIN可能比 CTE 更快，CTE 优势在深度不确定时
递归的本质是迭代生成中间结果，不是魔法 —— 它的开销和你手动写 N 层 JOIN 是同一量级，只是写法更紧凑。别指望它在百万级节点树上秒出结果，该加索引、该限深度，一个都不能少。
