MySQL UPDATE 語法詳解：從單表到多表、從基礎到進階

語法結構總覽

單表 UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col1=expr1 [, col2=expr2 ...]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

多表 UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col1=expr1 [, col2=expr2 ...]
[WHERE where_condition]

注意：多表 UPDATE 不支持 ORDER BY 和 LIMIT。

核心修飾符

賦值求值順序與陷阱

從左到右依次求值，後續表達式可見前面的修改：

-- age 先 ×2 再 +1，最終 age = 原值×2+1
UPDATE persondata SET age = age * 2, age = age + 1;

若設置為當前值，MySQL 識別後跳過更新（無實際寫入）。

NULL 處理與默認值

將 NOT NULL 列更新為 NULL 時：

• 數值類型 → 0
• 字符串類型 → 空串 ''
• 日期時間類型 → 'zero' 值（如 '0000-00-00 00:00:00'）

並產生警告（可通過 SHOW WARNINGS 查看）。

返回值與信息函數

• 返回實際改變的行數（而非匹配行數）
• mysql_info() / C API：獲取匹配行數、更新行數、警告數

LIMIT 與 ORDER BY 實戰

-- 僅更新前 10 條符合條件的記錄
UPDATE t1 SET status = 1 WHERE type = 2 ORDER BY create_time LIMIT 10;

按 create_time 排序後，更新前 10 行。常用於分批處理大量數據避免長鎖表。

多表 UPDATE 實戰

內連接更新

UPDATE items, month
SET items.price = month.price
WHERE items.id = month.id;

LEFT JOIN 更新

UPDATE t1
LEFT JOIN t2 ON t1.id = t2.t1_id
SET t1.name = COALESCE(t2.new_name, t1.name)
WHERE t1.status = 0;

權限要求

• 被修改列：需要 UPDATE 權限
• 僅被讀取列（WHERE/JOIN 條件）：需要 SELECT 權限

InnoDB 外鍵陷阱

多表 UPDATE 涉及帶外鍵的 InnoDB 表時，MySQL 優化器處理順序可能與父子層級不一致，導致語句失敗回滾。建議：單表更新 + 依賴 ON UPDATE CASCADE 自動級聯。

子查詢限制

不可在同一語句中更新表並從該表查詢：

-- 錯誤示例
UPDATE t1 SET col = (SELECT MAX(col) FROM t1);

解決方案：用連接或變量中轉。

經典使用場景示例

A. 簡單更新

-- 全表更新（謹慎使用！）
UPDATE publishers SET city = 'Atlanta', state = 'GA';

-- 計算值更新
UPDATE titles SET price = price * 2;

B. 條件更新

UPDATE authors
SET state = 'PC', city = 'Bay City'
WHERE state = 'CA' AND city = 'Oakland';

C. 關聯表數據同步

-- 累加銷量
UPDATE titles t
JOIN sales s ON t.title_id = s.title_id
SET t.ytd_sales = t.ytd_sales + s.qty
WHERE s.ord_date = (SELECT MAX(ord_date) FROM sales);

D. 同一天多批次銷量合併

UPDATE titles t
SET ytd_sales = (
    SELECT SUM(qty) FROM sales
    WHERE title_id = t.title_id
      AND ord_date IN (SELECT MAX(ord_date) FROM sales)
);

E. 限制更新行數

UPDATE authors
SET state = 'ZZ'
WHERE au_id IN (SELECT au_id FROM authors ORDER BY au_lname LIMIT 10);

最佳實踐清單

1. ✅ 永遠先用 SELECT 驗證 WHERE 條件範圍
2. ✅ 大批量更新分批處理（LIMIT 1000 循環）
3. ✅ 多表更新優先用 JOIN 語法，可讀性更強
4. ✅ 關鍵業務先在測試庫驗證 ROW_COUNT()
5. ⚠️ 避免在觸發器中對同表進行 UPDATE（死鎖風險）
6. ⚠️ IGNORE 可能掩蓋數據質量問題，謹慎使用