递归 CTE

做的事：一张真实的组织架构表，从底层员工递归向上找汇报链

代码CREATE TABLE org (id INT PRIMARY KEY, name VARCHAR(20), parent_id INT); INSERT INTO org VALUES (1,'CEO',NULL),(2,'VP-Eng',1),(3,'VP-Sales',1),(4,'Eng-Mgr',2),(5,'Engineer',4); WITH RECURSIVE chain AS ( SELECT id, name, parent_id, 0 AS depth FROM org WHERE id=5 UNION ALL SELECT o.id, o.name, o.parent_id, c.depth+1 FROM org o JOIN chain c ON o.id = c.parent_id ) SELECT * FROM chain;

验证：一条查询真实递归着走完了整条汇报链

id  name       parent_id  depth
5   Engineer   4          0
4   Eng-Mgr    2          1
2   VP-Eng     1          2
1   CEO        NULL       3

这条SQL真实只写了一次JOIN逻辑，但MySQL在执行时真实反复把上一轮的结果(chain)喂回去和org表再JOIN一次，直到某一轮真实查不出新行为止(CEO的parent_id是NULL，找不到下一个parent，递归真实自然终止)。不需要在应用层写循环代码，一条SQL真实就完成了"从叶子节点走到根节点"这类树形结构的遍历。

验证：真实把递归深度限制调低，看它是不是真的会拦截

SET SESSION cte_max_recursion_depth = 100;
WITH RECURSIVE seq AS (
  SELECT 1 AS n UNION ALL SELECT n+1 FROM seq WHERE n < 1000
)
SELECT COUNT(*) FROM seq;

→ ERROR 3636: Recursive query aborted after 101 iterations.
  Try increasing @@cte_max_recursion_depth to a larger value.

分步演示：从真实的树形遍历到真实的深度限制

每一步都是本文上面用真实递归CTE跑出来的原始结果。