递归CTE是唯一能可靠处理任意深度树形路径的方案，需用UNION ALL连接锚点（根节点）与递归部分（逐层拼接path），并确保字段类型一致、显式处理NULL、parent_id建索引。
用递归 CTE 展开无限层级的树形路径（PostgreSQL / SQL Server / Oracle）
标准 SQL 不支持直接“一次性展开”树形路径，但现代主流数据库都支持递归 WITH RECURSIVE，这是唯一能可靠处理任意深度树的方案。别试图用多层 JOIN 或固定层数子查询——只要层级超过 3，维护性就崩了。
核心思路是：先选根节点（level = 1），再用 UNION ALL 自连接自身，逐层拼接父级 path 字段。
WITH RECURSIVE tree_path AS ( -- 锚点：根节点（假设 parent_id IS NULL 表示根） SELECT id, name, parent_id, CAST(name AS VARCHAR(1000)) AS path, 1 AS level FROM categories WHERE parent_id IS NULL <p>UNION ALL</p><p>-- 递归：拼接当前节点到父路径后 SELECT c.id, c.name, c.parent_id, CAST(tp.path || ' > ' || c.name AS VARCHAR(1000)), tp.level + 1 FROM categories c INNER JOIN tree_path tp ON c.parent_id = tp.id ) SELECT * FROM tree_path ORDER BY path;
-
CAST(... AS VARCHAR)是必须的——递归 CTE 要求所有分支的列类型严格一致，否则报错recursive column "path" has inconsistent type - PostgreSQL 中
||是字符串拼接；SQL Server 用+，且需用CONCAT()或显式CAST避免NULL导致整条路径变NULL - Oracle 要求递归部分必须在
FROM子句中显式引用 CTE 名（FROM tree_path），不能省略
MySQL 8.0+ 也能用递归 CTE，但路径拼接要防 NULL
MySQL 对 NULL 拼接更敏感：只要 tp.path 或 c.name 任一为 NULL，整个 CONCAT() 结果就是 NULL，导致路径断裂。
WITH RECURSIVE tree_path AS ( SELECT id, name, parent_id, name AS path, 1 AS level FROM categories WHERE parent_id IS NULL <p>UNION ALL</p><p>SELECT c.id, c.name, c.parent_id, CONCAT(tp.path, ' > ', IFNULL(c.name, '')), -- 必须 IFNULL tp.level + 1 FROM categories c INNER JOIN tree_path tp ON c.parent_id = tp.id ) SELECT * FROM tree_path;
- 永远对可能为
NULL的字段套IFNULL(col, '')，尤其当name允许为空时 - MySQL 不支持
CAST在递归列中自动推导长度，VARCHAR(1000)可省略，但路径过长会截断——建议显式限定CONCAT(...)总长不超过 2000 - 若用 MySQL 5.7 或更早，递归 CTE 不可用，只能靠应用层循环查或临时表模拟，不推荐
为什么不用关联子查询（SELECT ... FROM t1 WHERE id IN (SELECT ... )）？
关联子查询本质是“找后代”，不是“生成路径”。它最多返回某节点的所有子孙 ID 列表，但无法构造出 Root > Parent > Current 这种带顺序、带分隔符的完整路径字符串。
常见误用：
SELECT id, name, (SELECT GROUP_CONCAT(name ORDER BY ??? SEPARATOR ' > ') FROM categories c2 WHERE c2.id IN (/* 找出从根到当前的所有祖先ID */)) AS path FROM categories;
- 子查询里根本没法按“从根到叶”的顺序排序——你连祖先链都拿不到，
ORDER BY无从谈起 - 即使硬写自连接找三代祖先，遇到四层深就失效，且 SQL 膨胀到难以维护
- 性能极差：每行都触发一次子查询，N 行就是 N 次全表扫描（或索引扫描）
路径字段建索引没意义，但 parent_id 必须有索引
path 是计算字段，每次查询都重新拼接，建索引毫无作用。真正影响递归效率的是 parent_id 字段——递归 CTE 的每次自连接都依赖它查找下一层子节点。
- 务必确保
parent_id有 B-Tree 索引（CREATE INDEX idx_parent_id ON categories(parent_id);） - 如果树特别宽（单节点几百个子节点），考虑加
WHERE parent_id = ?条件限制递归起点，避免展开整棵树 - 路径过长（比如 20 层以上）可能导致内存溢出，PostgreSQL 可设
WITH RECURSIVE ... LIMIT 100防死循环，MySQL 用cte_max_recursion_depth系统变量控制
递归 CTE 不是银弹：它把树遍历逻辑从应用层移到数据库层，可读性提升，但调试困难——一旦路径拼错，很难定位是哪一层 concat 出了问题。上线前一定用真实数据测最深层级。
