PostgreSQL用WITH RECURSIVE查树形路径最直接，因其原生支持递归查询，通过非递归初始查询和递归查询两部分实现，需确保字段类型一致且有终止条件；MySQL 8.0+才支持，5.7需替代方案；SQL Server需显式指定OPTION (MAXRECURSION)；跨库兼容可采用冗余path字段方案。
PostgreSQL用WITH RECURSIVE查树形路径最直接
PostgreSQL原生支持递归查询，WITH RECURSIVE是处理多级目录（如文件系统、组织架构）嵌套结构的标准解法。它比自连接或应用层拼接更可靠，也避免了硬编码层级上限的问题。
关键在于定义两个部分：非递归初始查询（找根节点），和递归查询（基于上一层结果找子节点）。必须确保递归字段类型一致、有明确终止条件（比如parent_id IS NOT NULL），否则会报recursive query "xxx" does not have a recursive term或无限循环。
示例：查某个目录的完整路径（从根到当前）：
WITH RECURSIVE dir_path AS ( SELECT id, name, parent_id, ARRAY[name] AS path, 1 AS level FROM directories WHERE id = 123 -- 目标目录ID UNION ALL SELECT d.id, d.name, d.parent_id, ARRAY[d.name] || dp.path, dp.level + 1 FROM directories d INNER JOIN dir_path dp ON d.id = dp.parent_id ) SELECT * FROM dir_path WHERE parent_id IS NULL;
MySQL 8.0+才支持WITH RECURSIVE，5.7只能靠存储过程或应用层拼接
MySQL在8.0之前完全不支持递归CTE，强行用自连接写3层以上目录查询极易出错——比如漏掉某级、重复连接、性能骤降。升级到8.0+后语法和PostgreSQL基本一致，但要注意MAX_RECURSION_DEPTH默认是100，深层目录（如200级分类）需临时调大：SET SESSION cte_max_recursion_depth = 500;
常见坑：
-
UNION必须写成UNION ALL，否则去重开销大且可能误删合法重复名称的节点 - 递归引用必须放在
FROM右侧，不能出现在WHERE子句里作为条件（会报错Recursive reference in non-recursive term） - 若用
ORDER BY控制路径顺序，必须加在最终SELECT，不能在CTE内部加
SQL Server用CTE递归时，容易忽略OPTION (MAXRECURSION)
SQL Server的WITH递归CTE语法类似，但默认只允许100层递归。查一个深度为150的目录树时，会直接报错：The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
必须显式加OPTION (MAXRECURSION n)结尾，比如：
WITH DirTree AS ( SELECT id, name, parent_id, CAST(name AS VARCHAR(1000)) AS full_path FROM directories WHERE id = 456 UNION ALL SELECT d.id, d.name, d.parent_id, dt.full_path + ' > ' + d.name FROM directories d INNER JOIN DirTree dt ON d.id = dt.parent_id ) SELECT * FROM DirTree OPTION (MAXRECURSION 0); -- 0表示无限制
OPTION (MAXRECURSION 0)虽方便，但存在风险：若数据有环（A→B→A），就会真死循环。生产环境建议设为略高于业务最大深度的值，比如MAXRECURSION 500。
跨数据库兼容方案：用path字段（如'1/5/23/123'）替代递归查询
如果无法依赖数据库递归能力（比如用SQLite、旧版MySQL，或ORM强制要求简单SQL），最稳妥的方式是在表中冗余一个path字段，每次插入/移动节点时由应用层维护。例如存为'/1/5/23/123/'，查某节点所有后代就只需WHERE path LIKE '/1/5/23/123/%'；查祖先则用WHERE '/1/5/23/123/' LIKE path || '%'（注意末尾斜杠防误匹配）。
这个方案快、可索引、无版本限制，但代价是写操作变重，且必须保证事务一致性——更新path时不能只改一行，要批量更新整个子树。
容易被忽略的一点：路径分隔符选/还是.不重要，重要的是它必须是业务数据中**绝不会出现的字符**。曾见过用-作分隔符，结果目录名含短横线，导致LIKE误匹配。
