MySQL UPDATE 語法完整詳解：單表、多表、修飾符、限制條件與最佳實踐

MySQL UPDATE 支援單表與多表更新，提供 LOW_PRIORITY、IGNORE、ORDER BY、LIMIT 等修飾符。本文系統整理語法細節、行為特性與實戰避坑指南。

一、標準語法結構

單表 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]

二、核心修飾符行為

三、關鍵行為特性

1. 賦值順序： 從左到右評估

   UPDATE t SET a=a*2, a=a+1; -- 先乘 2 再加 1

2. 無變更不更新： 設定值等於當前值不觸發寫入
3. NOT NULL 欄位設 NULL： 自動轉為型別預設值 (數值 0、字串 ''、日期 '0000-00-00') 並產生警告
4. 回傳值： 實際改變的行數 (非匹配行數)
5. C API： mysql_info() 可獲取匹配/更新/警告行數

四、多表更新實戰範例

內部連接更新 (逗號語法)

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

LEFT JOIN 更新 (支援所有 JOIN 類型)

UPDATE t1
LEFT JOIN t2 ON t1.id = t2.t1_id
SET t1.status = COALESCE(t2.new_status, 'pending');

五、重要限制與避坑指南

• ❌ 多表 UPDATE 不支援 ORDER BY / LIMIT
• ❌ 外鍵約束表：優化器處理順序可能違反父子關係導致回滾，建議單表更新 + ON UPDATE CASCADE
• ❌ 子查詢中更新同表並查詢同表：MySQL 禁止 (ERROR 1093)
• ✅ 權限分離： 被更新欄位需 UPDATE 權限，僅讀取欄位需 SELECT 權限

六、大量資料更新最佳實踐

-- 分批更新模板 (避免長鎖表、複製日誌暴增)
REPEAT
  UPDATE large_table
  SET status = 'processed'
  WHERE status = 'pending'
    AND id > last_id
  ORDER BY id
  LIMIT 1000;
  SET last_id = (SELECT MAX(id) FROM large_table WHERE status = 'processed');
UNTIL ROW_COUNT() = 0 END REPEAT;