递归CTE是跨数据库通用的层级遍历最优解，支持PostgreSQL、SQL Server、Oracle 12c+、MySQL 8.0+；需严格遵循锚点+递归成员+UNION ALL结构，注意各库深度限制、环路处理及语法差异。
用递归CTE实现跨数据库的通用层级遍历
几乎所有主流SQL数据库（PostgreSQL、SQL Server、Oracle 12c+、MySQL 8.0+）都支持递归 WITH 查询，这是写通用树遍历逻辑最可靠的方式。硬编码循环或自连接只适用于固定深度，而真实业务中层级常动态变化，递归CTE能自然处理任意深度。
关键点在于：必须显式声明锚点（root）和递归成员，并用 UNION ALL 连接；递归引用自身时，别名必须与外层一致；多数数据库要求递归查询中不能出现聚合、DISTINCT 或窗口函数——否则直接报错 ORA-32044（Oracle）或 Recursive common table expression "xxx" does not contain a recursive reference（SQL Server）。
WITH RECURSIVE tree_path AS ( SELECT id, parent_id, name, 1 AS level FROM categories WHERE parent_id IS NULL -- 锚点：根节点 UNION ALL SELECT c.id, c.parent_id, c.name, tp.level + 1 FROM categories c INNER JOIN tree_path tp ON c.parent_id = tp.id ) SELECT * FROM tree_path ORDER BY level, id;
避免MySQL 8.0+的max_recursive_depth陷阱
MySQL默认限制递归深度为100，一旦树实际深度超限，查询会静默截断结果，不报错也不警告——这是最隐蔽的坑。你查出来的“最后一层”可能根本不是叶子节点，而是被砍掉的中间层。
- 运行前务必检查并临时调高：
SET SESSION cte_max_recursion_depth = 2000; - 这个值不能超过全局
max_recursive_depth，且仅对当前会话生效 - 若需长期使用，应在启动配置中设置
cte_max_recursion_depth，而非依赖每次手动 SET - PostgreSQL 用
WITH RECURSIVE ... CYCLE可自动检测环路，但 MySQL 不支持CYCLE，必须靠业务逻辑确保无环
SQL Server里用OPTION (MAXRECURSION n)控制终止条件
SQL Server 的递归默认上限是100，但它的报错更明确：The statement terminated. The maximum recursion 100 has been exhausted before statement completion.。问题在于，很多人只改 MAXRECURSION 0（表示无限制），却忽略了栈溢出风险——深层递归可能耗尽内存或触发查询超时。
- 推荐显式设为合理值，比如：
OPTION (MAXRECURSION 500) - 若不确定最大深度，先用
SELECT MAX(LEVEL) FROM (...递归CTE...) t估算，再设值 - 在存储过程中，
OPTION必须放在语句末尾，不能写在 CTE 定义里，否则语法错误 - 注意：该选项只影响单条语句，对整个存储过程无效
PostgreSQL中处理环路和性能优化
PostgreSQL 支持 CYCLE 子句，这是唯一能安全应对脏数据（如 A→B→C→A）的原生方案。不加它，遇到环路会无限循环直到超时或内存爆掉。
WITH RECURSIVE tree_path(id, parent_id, name, path, cycle) AS ( SELECT id, parent_id, name, ARRAY[id], FALSE FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name, tp.path || c.id, c.id = ANY(tp.path) FROM categories c, tree_path tp WHERE c.parent_id = tp.id AND NOT tp.cycle ) CYCLE id SET cycle TO TRUE DEFAULT FALSE SELECT * FROM tree_path;
另外，path 字段用 ARRAY 而非字符串拼接，既避免了分隔符冲突（比如名字含逗号），又便于后续用 @> 操作符做路径包含查询。但要注意：数组字段无法走普通 B-tree 索引，高频路径查询建议额外建 GIN 索引。
真正难的是把「通用」落到存储过程里——参数化 root_id、排序字段、是否展开子树等，意味着 CTE 体要动态拼接。这时候不如退一步：把核心递归逻辑封装成视图或内联表值函数，存储过程只负责传参和后处理。强行在存储过程中拼 SQL 字符串，不仅难调试，还绕不开权限和注入风险。
