MySQL 多表 UPDATE/DELETE 實戰語法：JOIN 更新、多表刪除、LIMIT 控制、版本演進與避坑指南

前言：跨表寫入是業務邏輯常見需求

MySQL 4.0+ 支援多表 UPDATE/DELETE，透過 JOIN 語法實現關聯寫入。本文系統整理語法變體、LIMIT/ORDER BY 限制、版本門檻、實戰範例與常見錯誤。

多表 UPDATE 語法 (4.0.4+)

-- 標準 JOIN 寫法
UPDATE items i
JOIN month m ON i.id = m.id
SET i.price = m.price;

-- 逗號舊寫法 (相容)
UPDATE items, month
SET items.price = month.price
WHERE items.id = month.id;

• 不支援 ORDER BY、LIMIT
• 可跨多張表：UPDATE t1 JOIN t2 ON ... JOIN t3 ON ... SET ...
• 僅更新 SET 子句指定的表/欄位

多表 DELETE 語法 (4.0.0+/4.0.2+)

格式 1：DELETE ... FROM ... (4.0.0+)

DELETE t1, t2
FROM t1
JOIN t2 ON t1.id = t2.id
JOIN t3 ON t2.id = t3.id
WHERE t1.status = 1;

• DELETE 後列出**實際要刪除行的表** (t1, t2)
• FROM 後列出**所有參與 JOIN 的表** (t1, t2, t3)，t3 僅用於條件過濾，不刪除

格式 2：DELETE FROM ... USING ... (4.0.2+)

DELETE FROM t1, t2
USING t1
JOIN t2 ON t1.id = t2.id
JOIN t3 ON t2.id = t3.id
WHERE t1.status = 1;

• 語義與格式 1 完全一致，USING 關鍵字更直觀區分「刪除目標」與「關聯來源」

單表 DELETE 的 LIMIT 與 ORDER BY (4.0.0+)

-- 刪除最早的一筆錯誤日誌
DELETE FROM error_log
WHERE user = 'jcole'
ORDER BY timestamp ASC
LIMIT 1;

• ORDER BY 決定刪除順序，LIMIT 限制刪除行數
• 用於分批刪除大量歷史數據，避免長事務鎖表

多表 DELETE 的限制

• 不支援 ORDER BY、LIMIT
• 需分批刪除時，應用層循環執行帶 LIMIT 的單表刪除，或用 WHERE id IN (SELECT id FROM ... LIMIT n) 子查詢 (5.7+ 支援)

實戰範例：訂單-明細級聯軟刪除

-- 軟刪除訂單及其明細
UPDATE orders o
JOIN order_items i ON o.id = i.order_id
SET o.deleted = 1, i.deleted = 1
WHERE o.id = 12345
  AND o.deleted = 0;

-- 硬刪除 (僅刪除明細表，保留訂單審計)
DELETE i
FROM order_items i
JOIN orders o ON i.order_id = o.id
WHERE o.id = 12345
  AND o.status = 'CANCELLED';

避坑指南

1. 別名衝突：UPDATE t1 JOIN t2 ON ... SET t1.col = ... 中 SET 必須使用別名，不可用表名
2. WHERE 條件位置：放在 JOIN ... ON 之後，SET 之前
3. 觸發器觸發：多表 UPDATE/DELETE 會正確觸發各表 BEFORE/AFTER UPDATE/DELETE 觸發器
4. 主從複製：基於行模式 (RBR) 複製安全；語句模式 (SBR) 可能因從庫數據差異導致不一致，建議強制 RBR
5. 外鍵約束：若有 ON DELETE RESTRICT，多表 DELETE 會報錯，需先處理子表或暫時關閉檢查

結語

MySQL 多表 UPDATE/DELETE 以 JOIN 為核心，語法簡潔強大。**關鍵記住：DELETE 後列刪除目標、FROM/USING 列關聯來源、多表不支援 LIMIT/ORDER BY、單表支援**。掌握這兩條語句，即可優雅處理跨表寫入業務。