mysql 8.0+ 可用 with recursive 一次性查出目标节点及其所有后代，再通过 delete ... where id in (select ...) 或 join 实现整棵子树的级联删除；必须确保递归条件严格向下（如 c.parent_id = s.id）、避免自循环，并注意类型一致、深度限制及外键约束。
MySQL 8.0+ 直接用 WITH RECURSIVE 删除整棵子树
MySQL 8.0 开始原生支持递归 CTE，这是实现父子结构级联删除最干净的方式——不需要触发器、存储过程或应用层多次查询。WITH RECURSIVE 能一次性查出目标节点及其所有后代，再用 DELETE 配合 IN 或 JOIN 完成删除。
常见错误是把递归查询写成死循环：必须确保 WHERE 条件在递归分支中严格向下（比如只连 parent_id），且初始查询不能包含自身。
- 假设表为
categories(id, name, parent_id)，要删掉 ID = 5 及其全部子类： - 先用 CTE 查出所有待删 ID：
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 ) SELECT id FROM subtree;
- 再套进
DELETE：DELETE FROM categories WHERE id IN (SELECT id FROM (WITH RECURSIVE subtree AS (...)) AS t);
（注意 MySQL 要求子查询加别名） - 若遇到
ERROR 1267: Illegal mix of collations，说明id字段类型不一致（比如VARCHAR和INT混用），统一为INT或显式CAST
MySQL 5.7 及更早版本只能靠应用层或存储过程模拟递归
没有 WITH RECURSIVE 时，MySQL 无法单条语句查出任意深度的后代。硬要用纯 SQL，只能写存储过程，但维护成本高、调试困难，还容易因超深嵌套触发 max_sp_recursion_depth 限制。
更实际的做法是：在应用代码里做迭代查询。比如 Python 中用 while 循环反复查 SELECT id FROM categories WHERE parent_id IN (...)，收集全量 ID 后再执行单次 DELETE。
- 避免在循环内逐条
DELETE：每删一个节点就查一次子节点，IO 放大严重，N 层结构可能产生 O(N²) 查询 - 注意空结果集处理：某次查询没返回新 ID 就该退出，否则无限循环
- 如果表有外键约束指向其他表（如
products.category_id），必须先删子表数据或设ON DELETE CASCADE，否则DELETE会报错Cannot delete or update a parent row
更新父节点时，如何安全修改整个子树的某个字段？
和删除类似，更新整棵子树（比如把某分类下所有商品的 status 设为 0）也依赖能否一次性拿到所有后代 ID。8.0+ 仍首选 WITH RECURSIVE + UPDATE ... JOIN；老版本则需两步：先查 ID 列表，再 UPDATE ... WHERE id IN (...)。
容易被忽略的是性能陷阱：IN 列表过长（比如上万 ID）会导致解析慢、内存占用高，甚至触发 max_allowed_packet 错误。
- 替代方案：把 ID 写入临时表，再用
JOIN更新CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY); INSERT INTO tmp_ids VALUES (1),(2),(3),...; UPDATE categories c JOIN tmp_ids t ON c.id = t.id SET c.status = 0;
- 若更新涉及唯一索引字段（如
slug），要防重复值冲突，建议加WHERE NOT EXISTS或分批处理 - 事务必须包裹整个操作：先查 ID、再更新，中间不能有其他写入改变树结构，否则漏更新
为什么不要用自连接替代递归？
有人试图用多层 LEFT JOIN（如 JOIN categories c2 ON c1.id = c2.parent_id）硬“展开”树，但这只适用于已知最大深度（比如最多 4 级）。一旦层级变深，SQL 就得重写，且 JOIN 数量指数级增长，执行计划极易退化。
更隐蔽的问题是语义错误：自连接默认只查直接子节点，要查孙子得再 JOIN 一层，但这样无法表达“任意深度”，也无法用于 DELETE 的 WHERE 条件中。
- 例如：
DELETE c1 FROM categories c1 LEFT JOIN categories c2 ON c1.id = c2.parent_id WHERE c1.id = 5 OR c2.id IS NOT NULL—— 这只会删 ID=5 和它的直接子节点，漏掉孙子及更深节点 - 即使拼出 10 层
JOIN，只要某次插入第 11 层数据，逻辑就失效，且查询速度暴跌 - 这种写法在 EXPLAIN 中常出现
Using temporary; Using filesort，说明 MySQL 底层已放弃优化
递归操作的本质是图遍历，数据库只负责存状态，不负责控制流。MySQL 8.0 的 WITH RECURSIVE 是分水岭——之前靠绕路，之后可直行。但哪怕用了 CTE，也要检查 cte_max_recursion_depth 是否够用，尤其当业务树意外变深时，默认 1000 层可能不够。
