如何用SQL递归CTE删除具有树形层级结构的分类数据？

不能直接用 DELETE FROM categories WHERE id = 1 删除根节点，因为会违反外键约束或导致子节点变孤儿；递归CTE可安全获取整棵子树ID后批量删除。

为什么不能直接用 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 categoriesWHERE 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 cINNER 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 —— 误删树形结构几乎没有后悔药。