MySQL 8.0中CTE必须作为INSERT/UPDATE/DELETE的最外层前缀，紧贴语句开头且不可嵌套于子查询内；错误写法如派生表或标量子查询中使用WITH会导致语法报错。
MySQL 8.0 中 CTE 不能直接用于 INSERT、UPDATE、DELETE 的子查询内部，但可以作为 DML 语句的**最外层前缀**参与执行——这是优化复杂 DML 逻辑的合法且有效方式；误以为 CTE 可嵌套在 DML 子句中，是导致语法报错的主因。
CTE 必须紧贴 DML 语句开头，不能嵌套
CTE 不是“通用子查询替代品”，它只允许出现在 INSERT、UPDATE、DELETE 的**正前方**，且中间不能有其他语句或括号包裹。
- ✅ 正确：CTE 定义后紧跟一个 DML 操作，例如
WITH cte AS (...) INSERT INTO t SELECT * FROM cte; - ❌ 错误：
INSERT INTO t SELECT * FROM (WITH cte AS (...) SELECT ...) s;—— CTE 禁止出现在派生表中 - ❌ 错误：
UPDATE t SET x = (WITH cte AS (...) SELECT y FROM cte);—— CTE 不支持标量子查询写法 - ⚠️ 注意：CTE 后的 DML 必须是单个语句，不能跟多个分号隔开的语句（否则第二个语句会因缺少
WITH报错）
用 CTE 改写多步 UPDATE/DELETE 的典型场景
当需要基于聚合结果或跨表关联条件更新/删除数据时，传统写法需临时表或重复子查询；CTE 可把判断逻辑前置，主 DML 更聚焦行为本身。
- 场景：将「近30天订单总额超5万元的客户」状态设为
'VIP' - 写法对比：
传统方式要写两次子查询（WHERE 和 SET 中各一次），或建临时表再 JOIN；
CTE 方式只需定义一次计算逻辑：
WITH vip_customers AS ( SELECT customer_id FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY customer_id HAVING SUM(amount) > 50000 ) UPDATE customers c INNER JOIN vip_customers v ON c.id = v.customer_id SET c.status = 'VIP';
- 关键点：CTE 在
UPDATE前定义，后续用INNER JOIN引用，避免重复计算和锁表风险 - 不支持 LEFT JOIN + IS NULL 模式做反向删除？可以，但需确保 CTE 输出明确可 JOIN 的键，否则空值匹配易出错
递归 CTE 配合 DELETE 实现树形结构清理
删除某个分类及其所有子分类时，若用程序遍历再逐条删，容易漏删或死循环；用 WITH RECURSIVE 先查出完整路径，再一次性 DELETE，更安全可靠。
- 前提：分类表
categories含id和parent_id - 操作：删除 ID=123 的分类及其全部后代
- 必须加
RECURSIVE，漏写会报ERROR 1223 (HY000) - UNION ALL 是强制要求，UNION DISTINCT 会导致执行失败
WITH RECURSIVE sub_cats AS ( SELECT id FROM categories WHERE id = 123 UNION ALL SELECT c.id FROM categories c INNER JOIN sub_cats s ON c.parent_id = s.id ) DELETE FROM categories WHERE id IN (SELECT id FROM sub_cats);
- 注意：MySQL 不允许在
DELETE ... WHERE ... IN (WITH ...)这种嵌套结构中直接写 CTE，必须拆成两步（如上例用子查询包装） - 性能提示：如果子分类层级深、数量大，建议先
SELECT验证 CTE 输出结果，再执行 DELETE
真正容易被忽略的是作用域边界：CTE 只在紧随其后的那一条 DML 语句中可见，无法跨语句复用；想对同一中间结果做多次 DML，要么合并成一条（如 INSERT + ON DUPLICATE KEY UPDATE），要么改用临时表——CTE 不是万能变量，它只是“单次查询生命周期内的命名快照”。
