mysql 8.0 使用 with recursive cte 查父子关系，需定义锚点和递归成员并设置终止条件；5.7 则依赖多层 left join 模拟，但深度固定、易漏数据且难维护。
MySQL 8.0 怎么用递归 CTE 查父子关系
MySQL 8.0+ 原生支持递归 WITH RECURSIVE，查树形结构变得直接。关键在于写对锚点（anchor）和递归成员（recursive term），且必须有终止条件，否则会报 Recursive query aborted after 1000 iterations 错误。
假设有一张 categories 表，含 id、name、parent_id 字段，要查 ID=5 的所有祖先（向上追溯）：
WITH RECURSIVE cte AS ( SELECT id, name, parent_id, 0 AS level FROM categories WHERE id = 5 UNION ALL SELECT c.id, c.name, c.parent_id, level + 1 FROM categories c INNER JOIN cte ON c.id = cte.parent_id ) SELECT * FROM cte;
- 锚点部分必须是单条记录或明确结果集（不能是多行无限制的
SELECT *） - 递归 JOIN 必须是
INNER JOIN，且连接条件中一边必须来自上一层的 CTE（如cte.parent_id） -
level不是必需，但加了能防无限递归，也方便后续排序或截断 - 默认最大递归深度为 1000，可通过
SET SESSION cte_max_recursion_depth = 2000临时调高
MySQL 5.7 没有 WITH RECURSIVE，怎么模拟查父子
5.7 只能靠自连接 + 固定层数展开，或用存储过程拼接路径。最常用的是“左连接 N 层”法，但只适用于深度可控的场景（比如最多 4 级类目）。
查 ID=5 的所有祖先（最多向上 3 层）：
SELECT t1.id AS lev0_id, t1.name AS lev0_name, t2.id AS lev1_id, t2.name AS lev1_name, t3.id AS lev2_id, t3.name AS lev2_name, t4.id AS lev3_id, t4.name AS lev3_name FROM categories t1 LEFT JOIN categories t2 ON t2.id = t1.parent_id LEFT JOIN categories t3 ON t3.id = t2.parent_id LEFT JOIN categories t4 ON t4.id = t3.parent_id WHERE t1.id = 5;
- 每多一层就要多一个
LEFT JOIN，SQL 长度和执行计划复杂度线性增长 - 结果是宽表形式，不是扁平列表；想转成单列需用
UNION ALL拼接，但要去重且难控制顺序 - 如果实际深度超过预设层数（比如第 4 层才有根节点），就会漏数据 —— 这是最容易被忽略的逻辑缺陷
- 索引仍有效（
parent_id上建索引即可），但 JOIN 多时 optimizer 容易选错驱动表，建议用STRAIGHT_JOIN强制顺序
递归 CTE 和关联查询在性能与可维护性上的真实差异
别只看语法简洁性：CTE 在 8.0 中是真正按需迭代，而 5.7 的多层 JOIN 是一次性全连接后过滤，内存和临时表压力完全不同。
- 数据量小时（
- 当树深 > 5 或节点数 > 10 万，CTE 通常更省内存，因为每次只处理一层；多层 JOIN 可能生成巨大中间结果集（比如 1000 × 1000 × 1000）
- CTE 支持
ORDER BY和LIMIT在外层生效，但不能在递归分支里加LIMIT—— 加了会报错Recursive reference in a subquery is not allowed - 5.7 方案一旦业务要求“查所有子节点”（向下展开），就得反向写 JOIN（
t2.parent_id = t1.id），极易写错方向，且无法自然表达任意深度
迁移或兼容时最容易踩的坑
从 5.7 升级到 8.0 后直接套用旧 SQL 不会报错，但行为可能变 —— 尤其是隐式类型转换和 JOIN 顺序。
- CTE 中若引用了未定义的列（比如拼错
cte.parent_id成cte.parenet_id），错误发生在执行期而非解析期，调试更难 - 5.7 的多层 JOIN 若用了
USING(parent_id)，升级后可能因字段名冲突报错，得全改成ON显式条件 - 应用层如果依赖“返回固定列数”的结果（比如 Java 的
ResultSetMetaData.getColumnCount()），CTE 返回行数不确定，而多层 JOIN 列数固定 —— 这个兼容性问题常被测试遗漏 - 生产环境开启
cte_max_recursion_depth要谨慎：设太小查不全，设太大可能拖垮实例；建议按业务最大树深 +20% 设置，并监控Aborted_clients和慢日志中的递归超限记录
树形查询看着简单，但深度、方向、空值、环路（比如 A→B→A）、权限隔离这几个点，任何一个没兜住，线上就容易出静默错误或超时熔断。
