postgresql 根据文件夹id，一条sql语句获取到它本身及所有子文件、孙文件

PostgreSQL 是最早支持递归公用表表达式 (Recursive CTE)的主流数据库之一从 8.4 版本开始2009 年发布。因此在 PostgreSQL 中实现树形结构查询是其强项语法稳定且功能强大。以下是两种方案标准递归 SQL通用和ltree 扩展PostgreSQL 特有性能更强。方案一标准递归 CTE推荐通用性强这是最标准的 SQL 写法适用于所有现代 PostgreSQL 版本9.1 均完美支持。假设表结构CREATE TABLE folders ( id INT PRIMARY KEY, parent_id INT, -- 根节点为 NULL name VARCHAR(255) ); -- 重要务必给 parent_id 加索引 CREATE INDEX idx_folders_parent_id ON folders(parent_id);SQL 语句WITH RECURSIVE FolderTree AS ( -- 1. 锚点成员查询起始文件夹本身 SELECT id, parent_id, name, 0 AS level, ARRAY[id] AS path -- 可选记录路径用于防止循环 FROM folders WHERE id 100 -- 替换为你的文件夹 ID UNION ALL -- 2. 递归成员查询子节点 SELECT f.id, f.parent_id, f.name, ft.level 1, ft.path || f.id -- 将当前 ID 加入路径数组 FROM folders f INNER JOIN FolderTree ft ON f.parent_id ft.id WHERE NOT f.id ANY(ft.path) -- 可选防止死循环环状引用 ) SELECT * FROM FolderTree;PostgreSQL 的优势防止死循环如上例所示PG 支持数组类型可以轻松维护一个path数组来检测是否出现了环状引用A-B-A避免无限递归。版本要求低几乎不需要考虑版本兼容性问题10 年前的 PG 版本都支持。性能配合parent_id索引性能非常好。方案二使用ltree扩展PostgreSQL 特有性能最优如果你频繁进行树形查询PostgreSQL 提供了一个专门的贡献模块ltree用于存储标签树结构。这是 PG 区别于 MySQL 的一大杀手锏。1. 启用扩展只需执行一次CREATE EXTENSION IF NOT EXISTS ltree;2. 修改表结构增加一个path字段类型为ltree。ALTER TABLE folders ADD COLUMN path ltree; CREATE INDEX idx_folders_path ON folders USING GIST (path);3. 数据示例根节点1子节点1.5孙节点1.5.1004. 查询语句一条 SQL查询 ID 为 100 的节点及其所有子孙假设 100 的路径是1.5.100-- 方法直接匹配路径前缀 SELECT * FROM folders WHERE path 1.5.100.*::ltree; -- 或者更简单的写法先查出该节点路径 SELECT * FROM folders WHERE path (SELECT path FROM folders WHERE id 100) || .*::ltree;操作符表示“是...的后代”。性能利用 GiST 索引查询速度极快远超递归 CTE适合百万级数据量的树形结构。方案三PostgreSQL 14 的CYCLE子句如果你使用的是PostgreSQL 14 或更高版本语法可以更简洁原生支持循环检测WITH RECURSIVE FolderTree AS ( SELECT id, parent_id, name FROM folders WHERE id 100 UNION ALL SELECT f.id, f.parent_id, f.name FROM folders f JOIN FolderTree ft ON f.parent_id ft.id ) SELECT * FROM FolderTree CYCLE id SET is_cycle USING path; -- 自动检测循环标记 is_cycle总结对比特性MySQL 8.0Doris 2.0PostgreSQL递归支持支持 (WITH RECURSIVE)支持 (WITH RECURSIVE)完美支持 (8.4)防死循环较麻烦 (需手动判断)较麻烦简单 (支持数组/原生 CYCLE)专用扩展无无有 (ltree模块)推荐方案递归 CTE路径枚举 (path 字段)递归 CTE 或 ltree建议如果是普通业务直接使用方案一递归 CTE简单可靠无需改表结构。如果是复杂树形结构如分类目录、组织架构且查询频繁强烈建议使用方案二ltree这是 PostgreSQL 的独家优势性能最好。