如何在SQL中使用递归CTE删除具有树形层级结构的节点？-SQL-PHP中文网

递归CTE不能直接用于DELETE语句，通用方案是先用递归CTE查出待删ID集合再通过IN或JOIN驱动删除；PostgreSQL 14+支持WITH...DELETE语法但本质仍是先查后删；需防范循环引用并确保parent_id有索引。

如何在sql中使用递归cte删除具有树形层级结构的节点？

递归CTE本身不能直接用于DELETE语句

SQL标准（包括PostgreSQL、SQL Server、SQLite）明确禁止在DELETE中直接嵌套递归CTE。你写不出DELETE FROM t WHERE id IN (WITH RECURSIVE ...)这种语法，会报错ERROR: invalid reference to FROM-clause entry或类似提示。

真正可行的路径只有两条：先用递归CTE查出待删ID集合，再用它驱动删除；或借助支持修改的CTE变体（仅PostgreSQL 14+提供WITH ... DELETE语法，但有严格限制）。

通用方案：用递归CTE生成ID列表后JOIN删除

这是跨数据库兼容性最好的做法，核心是把递归结果物化为临时ID集，再与原表关联。注意必须包含根节点自身（否则只删子树不删根）。

假设表名为categories，主键id，父级外键parent_id

要删除ID为5的节点及其所有后代，先运行递归查询确认范围：

WITH RECURSIVE tree AS (
  SELECT id
  FROM categories
  WHERE id = 5
  UNION ALL
  SELECT c.id
  FROM categories c
  INNER JOIN tree t ON c.parent_id = t.id
)
SELECT id FROM tree;

确认无误后，用该结果驱动删除：

DELETE FROM categories
WHERE id IN (
  WITH RECURSIVE tree AS (
    SELECT id
    FROM categories
    WHERE id = 5
    UNION ALL
    SELECT c.id
    FROM categories c
    INNER JOIN tree t ON c.parent_id = t.id
  )
  SELECT id FROM tree
);

MySQL 8.0+需额外注意：子查询中递归CTE必须有别名（如AS tree），且IN子查询不能有重复ID（递归本身不会产生重复，但若业务逻辑允许环状引用，需加DISTINCT）

PostgreSQL特例：用WITH执行带删除的递归操作

PostgreSQL 14起支持WITH ... DELETE语法，但要求递归部分必须是SELECT，删除动作放在主语句，且无法直接在递归分支中触发删除——本质仍是“先查后删”的封装，只是语法更紧凑。

以下写法可行，但和通用方案性能/行为一致：

WITH RECURSIVE tree AS (
  SELECT id
  FROM categories
  WHERE id = 5
  UNION ALL
  SELECT c.id
  FROM categories c
  INNER JOIN tree t ON c.parent_id = t.id
)
DELETE FROM categories
WHERE id IN (SELECT id FROM tree);

注意：PostgreSQL 13及更早版本不支持此语法，必须用两步法；另外，如果表有ON DELETE CASCADE外键，递归CTE查出的ID顺序不保证从叶子到根，可能触发外键约束失败，此时应手动控制删除顺序（先删子节点再删父节点）。

容易被忽略的循环引用与性能陷阱

真实业务中，树结构常存在脏数据：比如A的父级是B，B的父级又指回A。递归CTE默认会无限循环直至超时或达到max_recursion_depth（MySQL）/statement_timeout（PostgreSQL）。必须主动防御。

MySQL：在递归SELECT中加入LEVEL 限制深度（需配合cte_max_recursion_depth系统变量）
PostgreSQL：使用SEARCH DEPTH FIRST BY id SET ordercol并配合CYCLE子句标记环路，例如CYCLE id SET is_cycle USING path
无论哪种数据库，对parent_id字段加索引是刚需，否则每次JOIN都全表扫描，树深每增1层，性能下降一个数量级

递归深度超过200层时，多数数据库会开始明显变慢，这时该考虑是否真需要物理删除——有时软删除（加is_deleted字段）或归档到历史表更实际。