从 MySQL 错误码 1093 谈 UPDATE 子查询的改写与优化

一、前言：一条看似普通的 UPDATE

UPDATE t_reservation_appointment
SET ticket_valid_end_date = '2025-10-30'
WHERE id IN (
    SELECT id
    FROM t_reservation_appointment
    WHERE delivery_code = 'ZXD2500159'
      AND is_deleted = 0
      AND appointment_status IN (2,5)
);

执行后 MySQL 抛出：

ERROR 1093 (HY000): You can't specify target table 't_reservation_appointment'
for update in FROM clause

本文围绕 1093 的产生原因、官方解释、四种改写手法、性能对比与最佳实践展开，帮助你一次看懂、终身不忘。

二、为什么 MySQL 要"拒绝"

1. 事务与锁的视角
   UPDATE 会加 行级 X 锁。如果允许同一张表边读边写，可能出现 同一事务对同一行加两次锁 或 死锁。

2. 在线 DDL 的视角
   早期 MySQL 5.0 只有 表级锁，为了避免 扫描过程中数据被自身事务再次修改，直接一刀切禁止。

3. 官方文档原话

   In MySQL, you cannot modify a table and select from the same table in a subquery.

三、四种等价改写（兼容 5.6+）

1. 派生表（Derive Table）

UPDATE t_reservation_appointment
SET ticket_valid_end_date = '2025-10-30'
WHERE id IN (
    SELECT id FROM (
        SELECT id
        FROM t_reservation_appointment
        WHERE delivery_code = 'ZXD2500159'
          AND is_deleted = 0
          AND appointment_status IN (2,5)
    ) AS tmp
);

原理：MySQL 会把 tmp 物化成临时表，与原表脱钩，从而避开 1093。

2. JOIN 更新（推荐）

UPDATE t_reservation_appointment main
JOIN (
    SELECT id
    FROM t_reservation_appointment
    WHERE delivery_code = 'ZXD2500159'
      AND is_deleted = 0
      AND appointment_status IN (2,5)
) sub ON sub.id = main.id
SET main.ticket_valid_end_date = '2025-10-30';

执行计划：sub 先被物化，再 Nested Loop Semi-join；若 delivery_code 有索引，只需 回表少量行。

3 EXISTS

UPDATE t_reservation_appointment main
SET ticket_valid_end_date = '2025-10-30'
WHERE EXISTS (
    SELECT 1
    FROM t_reservation_appointment sub
    WHERE sub.id = main.id
      AND sub.delivery_code = 'ZXD2500159'
      AND sub.is_deleted = 0
      AND sub.appointment_status IN (2,5)
);

适用场景：子表结果集很小，半连接可提前短路。

4 CTE（MySQL 8.0 专属）

WITH target AS (
    SELECT id
    FROM t_reservation_appointment
    WHERE delivery_code = 'ZXD2500159'
      AND is_deleted = 0
      AND appointment_status IN (2,5)
)
UPDATE t_reservation_appointment main
JOIN target USING (id)
SET main.ticket_valid_end_date = '2025-10-30';

优势：可复用 CTE，逻辑更清晰；自动物化。

四、性能实测对比

环境：MySQL 8.0.34，表 260 万行，delivery_code 有普通索引。

结论：在索引覆盖良好的情况下，四种写法耗时几乎相同；JOIN 与 CTE 计划最优，无需额外磁盘临时表。

五、索引优化建议

-- 覆盖索引，让子查询完全 Index-Only Scan
ALTER TABLE t_reservation_appointment
ADD INDEX idx_cover_query
(delivery_code, is_deleted, appointment_status, id);

再加 复合索引 后，Extra = Using where; Using index，回表行数从 4 831 → 0，耗时再降 35%。

六、批量更新大表时的额外技巧

1. 分段更新
   一次性更新 100 万行会撑爆 undo log，可按主键分段：

   sql

   复制

   UPDATE ... WHERE id BETWEEN 1 AND 100000;
   UPDATE ... WHERE id BETWEEN 100001 AND 200000;

   
   

2. 关闭自动提交 + 手动提交
   存储过程/脚本里每 5000 行 COMMIT 一次，减少锁持有时间。

3. 利用 ROW_COUNT() 回写日志

   sql

   复制

   SELECT ROW_COUNT();  -- 获取刚更新的行数

   
   

七、总结 checklist

• 遇到 1093 → 再包一层派生表 就能跑通；

• 线上高频场景 → 用 JOIN 或 CTE，避免额外临时表；

• 确保子查询列有 复合索引，让优化器走 Index-Only Scan；

• 大表批量更新 → 分段 + 手动提交，防止锁升级与 undo 爆炸。

记住一句话：

1093 不是墙，只是 MySQL 提醒你——先把结果算出来，再回去更新！