mysql如何实现递归查询组织架构_MySQL8.0版本WITH RECURSIVE-mysql教程-PHP中文网

mysql 8.0+向下查所有下属的典型递归查询：锚点选where manager_id = ?获取直接下属，递归步用join employees e on e.manager_id = s.id向下延伸，并加where s.depth

MySQL 8.0+ 能用 WITH RECURSIVE 查组织架构，但必须明确方向（向上查领导 or 向下查下属）、结构无环、且手动加深度限制，否则容易死循环或查出重复路径。

怎么写向下查所有下属的递归查询

典型场景：给定一个 manager_id，查他直接+间接的所有下属。关键在锚点选“直接下属”，递归步用 manager_id = s.id 关联下一层：

锚点部分用 WHERE manager_id = ? 拿到第一层下属

递归部分必须 JOIN employees e ON e.manager_id = s.id（注意是 e.manager_id 匹配上层 s.id，方向不能反）

必须加 WHERE s.id IS NOT NULL 或显式 depth 防止环导致无限递归

不建议用 UNION（去重开销大），UNION ALL 更快也更可控
WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id = 4  -- 锚点：查ID为4的人的所有下属
  UNION ALL
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  INNER JOIN subordinates s ON e.manager_id = s.id
  WHERE s.depth <h3>怎么查某员工到CEO的完整上级路径</h3><p>这是 <code>WITH RECURSIVE</code> 最稳妥的用法：单向、无环、固定终点（根节点 <code>manager_id IS NULL</code>）。锚点是员工自己，递归步向上找 <code>manager_id</code> 对应的上级：</p><div class="aritcle_card flexRow artxards">
											<div class="artcardd flexRow">
												<a class="aritcle_card_img" rel="nofollow" href="/xiazai/skill2334" title="MySQL"><img
														src="https://img.php.cn/upload/skill/000/000/081/178900927846657.jpg" alt="MySQL" onerror="this.onerror='';this.src='/static/lhimages/moren/morentu.png'" ></a>
												<div class="aritcle_card_info flexColumn">
													<a rel="nofollow" href="/xiazai/skill2334" title="MySQL" class="overflowclass">MySQL</a>
													<p class="overflowclass">编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。</p>
												</div>
												<a rel="nofollow" href="/xiazai/skill2334" title="MySQL" class="aritcle_card_btn flexRow flexcenter"><b></b><span>下载</span>
												</a>
											</div>
										</div>
锚点选 WHERE id = ?，不是 manager_id = ?

递归部分必须 JOIN employees e ON e.id = p.manager_id（当前行的 manager_id 去匹配上级的 id）

终止条件靠 WHERE p.manager_id IS NOT NULL 控制，避免查到根后还继续

用 CONCAT 或 CAST 构建路径时，初始值要和后续拼接类型一致（比如都转 CHAR）
WITH RECURSIVE path_to_top AS (
  SELECT id, name, manager_id, 0 AS depth, CAST(name AS CHAR(200)) AS path
  FROM employees
  WHERE id = 6  -- 锚点：从员工ID6开始
  UNION ALL
  SELECT e.id, e.name, e.manager_id, p.depth + 1, CONCAT(e.name, ' → ', p.path)
  FROM employees e
  INNER JOIN path_to_top p ON e.id = p.manager_id
  WHERE p.manager_id IS NOT NULL
)
SELECT * FROM path_to_top ORDER BY depth DESC;
为什么查出来有重复ID或深度错乱

常见现象：同一人出现多次、depth 值跳跃、path 出现分叉。根本原因是 MySQL 的 WITH RECURSIVE 不做去重、不排序、不剪枝，纯靠 SQL 层逻辑约束：

UNION ALL 不去重 —— 如果数据本身存在多条路径（比如双汇报线），就会重复输出；业务上必须保证 (id, manager_id) 唯一，或建唯一索引

递归部分不能写 ORDER BY 或 LIMIT —— 这会导致语法错误，深度控制只能靠 WHERE 条件（如 depth ）

没加 depth 字段就无法稳定排序 —— 结果顺序不可控，看起来像乱序；务必在 CTE 中显式计算并暴露该字段

默认递归上限是 1000 层，超限报错 ERROR 3636 —— 可临时调大：SET SESSION cte_max_recursion_depth = 2000;

性能和索引怎么优化

递归查询性能差，往往不是语法问题，而是缺少支撑索引：

必须为关联字段建联合索引：比如查下属，建 INDEX idx_mgr_id (manager_id, id)；查上级，建 INDEX idx_id_mgr (id, manager_id)

避免在递归部分对 CTE 别名做函数操作（如 WHERE UPPER(s.name) = 'X'），会阻止索引使用

大数据量时，先用非递归方式预过滤（如加 WHERE status = 'active' 到锚点和递归部分）

cte_max_recursion_depth 调太高可能耗尽内存，线上建议保持默认或略增，别设成 0（不限制）

真正难的不是写出递归语句，而是确认你的组织表有没有隐式环、是否允许多父节点、以及能否接受全量穷举而非最短路径——这些决定了你到底该不该用 WITH RECURSIVE，而不是换 Neo4j 或应用层处理。