UPDATE 看似簡單,實則暗藏多表聯動、分頁限制、隱式預設值、權限分離等進階特性。本文系統梳理 MySQL UPDATE 完整語義與實戰避坑指南。
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col1 = expr1, col2 = expr2, ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
-- 標準 JOIN 語法 (推薦)
UPDATE t1
INNER JOIN t2 ON t1.id = t2.t1_id
SET t1.col = t2.val, t2.flag = 1
WHERE t1.status = 0;
-- 舊式逗號語法 (同樣支援 LEFT JOIN)
UPDATE t1, t2
SET t1.col = t2.val
WHERE t1.id = t2.t1_id AND t1.status = 0;
若涉及表有外鍵關係,優化器可能按**非父子順序**處理表,導致外鍵檢查失敗並**整體回滾**。
解法:拆分為單表 UPDATE,依賴 `ON UPDATE CASCADE/SET NULL` 由引擎自動維護關聯。
| 陷阱 | 後果 | 對策 |
|---|---|---|
| WHERE 條件無索引 | 全表掃描 + 全表鎖 (RR 隔離級) | 確保 WHERE 欄位有索引 |
| 大事務長時間持有鎖 | 阻塞所有並發寫入、甚至讀取 | 拆分批次、縮短事務、避免長事務 |
| SET 子句使用子查詢 | 重複執行子查詢、效能極差 | 改用 JOIN 多表 UPDATE 或預先讀取到應用層 |
START TRANSACTION;
UPDATE `orders` o
JOIN `order_items` i ON o.id = i.order_id
SET o.status = 2, i.processed = 1
WHERE o.id = 1001 AND o.status = 1 -- 悲觀鎖樂觀鎖雙重保障
LIMIT 1;
COMMIT;