SQL递归CTE删除树形层级分类数据的方法

先讲清楚一个问题：为什么不能直接用 DELETE FROM categories WHERE id = 1 删除根节点？

核心原因在于树形结构的特殊依赖关系——子分类的 parent_id 指向父分类，直接砍掉根节点，子节点立马变成“孤儿”，数据完整性当场崩溃。更麻烦的是，大多数数据库（PostgreSQL、SQL Server 都在其列）会因外键约束直接拒绝执行；就算没有外键，手动逐层删也极易出错：漏节点、顺序搞反、事务一致性崩盘。递归 CTE 的价值不是“炫技”，而是让数据库自己乖乖算出整棵子树的 id 列表，然后一次性清理干净。

PostgreSQL 中用 WITH RECURSIVE 安全删除子树

关键点在于：CTE 必须先查出所有待删节点，再在主 DELETE 中引用它。注意，不能把 DELETE 写进 CTE 里（语法不认），也不能在 CTE 外部用 IN (SELECT ...) 嵌套——对大子树性能差，还可能触发 planner 优化错误。

假设表结构为：categories(id, name, parent_id)，其中 parent_id 可为 NULL。要删 ID 为 5 的分类及其全部子孙，写法如下：

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

要点：PostgreSQL 要求递归查询必须有 UNION ALL，且锚点（anchor）和递归部分字段数、类型必须严格一致；parent_id 字段最好建索引，否则递归深度一上去就慢得可怕。

MySQL 8.0+ 的等效写法及坑点

MySQL 语法类似，但行为细节不太一样：默认递归深度限制为 1000，超限会报错 ERROR 3636 (HY000): Recursive query aborted after 1000 iterations。必须显式调高：

• 运行前执行：SET SESSION cte_max_recursion_depth = 5000;
• CTE 中锚点必须写成 SELECT 5 AS id（显式别名），否则 MySQL 可能报 Column 'id' not found
• 删除语句不能直接 DELETE FROM categories WHERE id IN (WITH ...)，必须拆成两步或用派生表

推荐写法：

WITH RECURSIVE subtree AS (
  SELECT 5 AS id
  UNION ALL
  SELECT c.id
  FROM categories c
  INNER JOIN subtree s ON c.parent_id = s.id
)
DELETE c FROM categories c
INNER JOIN subtree s ON c.id = s.id;

这里用 JOIN 替代 IN，可以绕开 MySQL 对子查询的临时表限制，也更容易利用索引。

SQL Server 的注意事项：循环引用与终止条件

如果数据存在脏数据（比如 A → B → A 这种环），SQL Server 默认会报错 Msg 530, Level 16, State 1: The statement terminated. The maximum recursion 100 has been exhausted。必须加 OPTION (MAXRECURSION n) 控制深度，并用 EXCEPT 或路径标记防环：

• 简单场景下，加 OPTION (MAXRECURSION 1000) 到 DELETE 语句末尾即可
• 严谨的做法是在 CTE 中维护路径字符串（如 '/5/12/45/'），用 NOT LIKE '%/'+CAST(c.id)+'/%' 检查是否已出现过——但这会明显拖慢性能
• 务必确认 parent_id 字段有索引，否则每次递归都全表扫描

不过老实说，真正线上环境操作，建议先用 SELECT 版本跑一遍 CTE，确认返回的 ID 数量和范围符合预期，再执行 DELETE。误删树形结构，几乎没有后悔药。