CTE递归删除不能直接在WITH中执行DELETE,必须先用WITH RECURSIVE查出待删ID,再通过DELETE FROM t WHERE id IN (SELECT id FROM cte)或JOIN方式显式删除目标表;MySQL 8.0+不支持DELETE ... USING cte,且需防范外键约束、环形引用及IN子查询性能问题。

CTE递归删除必须配合UPDATE或DELETE语句,不能直接在WITH中删
CTE本身只是定义临时结果集,WITH块里写完递归查询后,必须用DELETE或UPDATE显式作用于目标表。常见错误是以为WITH RECURSIVE ... DELETE FROM cte能运行——这在所有主流SQL引擎(PostgreSQL、SQL Server、MySQL 8.0+)里都会报错,比如 PostgreSQL 报 ERROR: relation "cte" does not exist。
正确做法是把CTE当作“标记器”:先递归找出要删的节点ID,再用IN或JOIN驱动主表删除。
- PostgreSQL 和 SQL Server 支持在
DELETE语句中直接USING或FROM引用CTE别名 - MySQL 8.0+ 不允许
DELETE ... FROM cte,必须用DELETE FROM t WHERE id IN (SELECT id FROM cte) - CTE必须带
RECURSIVE关键字(PostgreSQL/SQL Server),MySQL可省略但建议显式写出
指定层级删除的关键是记录递归深度
只靠parent_id和id无法知道当前节点在哪一层,必须在CTE中显式维护一个level字段。起始行设为1(根节点),每递归一次+1;若从子节点向上查祖先,则反向累加(但层级含义需自行约定)。
示例:删除以id = 5为根、向下**恰好2层**的子树(含第2层所有叶子,不含第3层):
WITH RECURSIVE tree AS ( SELECT id, parent_id, 1 AS level FROM categories WHERE id = 5 UNION ALL SELECT c.id, c.parent_id, t.level + 1 FROM categories c INNER JOIN tree t ON c.parent_id = t.id WHERE t.level < 2 -- 控制只展开到第2层 ) DELETE FROM categories WHERE id IN (SELECT id FROM tree);
WHERE t.level 是关键:它让递归在生成第2层子节点后停止,不继续深入第3层- 如果想删“第3层及以下全部”,就去掉该条件,改用外层
WHERE level >= 3 - 注意
level初始值设为1还是0会影响判断逻辑,保持全程一致
MySQL 8.0+ 的IN子查询性能隐患
MySQL不支持DELETE ... USING cte,只能走IN (SELECT ...),当CTE结果集较大时(比如几千行),可能触发IN子查询的全表扫描或临时表膨胀,比PostgreSQL/SQL Server的USING方案慢数倍。
- 验证方式:执行
EXPLAIN FORMAT=TREE看是否出现materialized或dependent subquery - 缓解办法:给
categories(id)加主键(必须),并在CTE内层尽早加WHERE过滤(如c.parent_id IS NOT NULL) - 极端情况可先
CREATE TEMPORARY TABLE tmp_del AS SELECT id FROM cte,再DELETE JOIN,但要注意事务安全
删除前务必检查环形引用和外键约束
递归CTE遇到自环(如id=3的parent_id也等于3)会无限循环,PostgreSQL默认max_recursion_depth=100报错,SQL Server抛MAXRECURSION异常,MySQL可能卡死。更隐蔽的问题是外键:若子表(如products)引用categories.id且无ON DELETE CASCADE,删除会直接失败,报错类似ERROR 1451 (HY000): Cannot delete or update a parent row。
- 查环:在CTE中加
ARRAY[cte.id] AS path(PG)或CONCAT(',', id, ',') AS path(MySQL),再用path LIKE '%,id,%'检测重复 - 查外键依赖:
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'categories' - 生产环境删树前,先用
SELECT跑一遍CTE确认ID范围,别跳过这步
层级删除真正麻烦的从来不是语法,而是递归路径的边界控制和关联数据的一致性——CTE只是工具,删错一行ID可能连带清空整个商品类目。

















