MySQL 基本資料 CRUD – UPDATE 更新資料

簡介

在資料庫操作的四大基本功能（Create、Read、Update、Delete）之中，UPDATE 扮演了讓資料隨時間演變、保持正確性的關鍵角色。無論是調整使用者的聯絡資訊、修正商品庫存、或是批次更新報表狀態，幾乎所有的業務系統都離不開資料的更新。

對於剛踏入 MySQL 的新手而言，UPDATE 看似簡單——只要指定「要改哪筆資料」與「改成什麼值」就好。但實務上卻常因條件不明確、缺乏交易控制或是未考慮索引效能，導致資料不一致、效能瓶頸甚至資料遺失。本文將從概念、語法、實作範例，到常見陷阱與最佳實踐，完整說明 如何安全、有效率地使用 UPDATE，幫助你在開發與維運中更加得心應手。

---

核心概念

1. UPDATE 基本語法

UPDATE 表格名稱
SET 欄位1 = 新值1,
    欄位2 = 新值2,
    ...
WHERE 條件式;

• SET：列出要修改的欄位與新值，支援多欄位同時更新。
• WHERE：篩選目標列，若省略則會更新整張表（極危險）。
• ORDER BY / LIMIT：在 MySQL 中可配合使用，限制更新的筆數與順序（常用於批次處理）。

小提醒：永遠在 UPDATE 前先確認 WHERE 條件，避免不小心把所有資料改掉。

---

2. 使用表達式與函式

MySQL 允許在 SET 中使用算術運算、字串函式、日期函式等。例如：

UPDATE product
SET stock = stock - 1,               -- 庫存減一
    last_update = NOW()              -- 更新最後修改時間
WHERE product_id = 1234;

此語句同時完成庫存扣減與時間戳記，不需要額外的 SELECT。

---

3. 多表更新（JOIN）

有時候更新的條件牽涉到另一張表的資料，這時可以使用 JOIN 直接在 UPDATE 中加入關聯：

UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'VIP',
    o.discount = 0.9
WHERE c.member_level = 'Gold' AND o.status = 'Pending';

• JOIN 讓我們在一次語句裡同時檢查兩張表的關聯條件，減少往返資料庫的次數。
• 注意：只會更新 orders 表（UPDATE 後面的第一個表格），JOIN 只作為條件過濾。

---

4. 批次更新（使用 LIMIT）

在大量資料需要逐步更新時，建議分批執行以降低鎖定衝突與資源占用：

-- 每次更新 1000 筆，直到全部處理完畢
UPDATE user_log
SET processed = 1
WHERE processed = 0
ORDER BY log_id
LIMIT 1000;

配合程式迴圈或排程工具（如 cron），可達到 漸進式批次更新 的效果。

---

5. 交易（Transaction）保護

對於多筆相關的 UPDATE，若必須保證「全部成功或全部失敗」的原子性，必須使用 交易：

START TRANSACTION;

UPDATE account SET balance = balance - 500 WHERE user_id = 1;
UPDATE account SET balance = balance + 500 WHERE user_id = 2;

-- 若任一步驟失敗，執行 ROLLBACK
COMMIT;

• START TRANSACTION → COMMIT / ROLLBACK 為 MySQL InnoDB 引擎的標準流程。
• 若使用 MyISAM，則不支援交易，務必改用 InnoDB。

---

程式碼範例

以下提供 5 個實務常見的 UPDATE 範例，每個範例均附上說明與注意事項。

範例 1：單筆資料更新（最基礎）

-- 將使用者的 Email 改為新值
UPDATE users
SET email = 'newmail@example.com'
WHERE user_id = 42;

說明：僅更新 user_id = 42 的那一筆。若 WHERE 條件寫錯，可能會更新錯誤的使用者。

---

範例 2：同時更新多個欄位

-- 同時更新使用者名稱與最後登入時間
UPDATE users
SET username = 'alice_new',
    last_login = NOW()
WHERE user_id = 42;

說明：一次語句完成多欄位變更，減少 I/O 次數。NOW() 為 MySQL 內建的日期時間函式。

---

範例 3：利用算術運算更新庫存

-- 商品出貨時扣除庫存，且避免負庫存
UPDATE inventory
SET stock = GREATEST(stock - 5, 0)   -- 若扣除後小於 0，則設為 0
WHERE product_code = 'P1001' AND stock >= 5;

說明：GREATEST 用於保證庫存不會變成負數；WHERE 條件 stock >= 5 防止不符合條件的列被更新。

---

範例 4：多表 JOIN 更新（根據條件變更訂單狀態）

UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'Special',
    o.priority = 1
WHERE c.vip_flag = 1
  AND o.status = 'Pending';

說明：只會更新 orders 表，但條件同時檢查 customers 的 VIP 標記。這類語句在 會員制度、促銷活動 中相當常見。

---

範例 5：批次更新 + 交易保護（資料遷移範例）

START TRANSACTION;

-- 將舊系統的使用者等級搬移到新欄位
UPDATE users
SET level_new = CASE
    WHEN level_old = 'A' THEN 'Gold'
    WHEN level_old = 'B' THEN 'Silver'
    ELSE 'Bronze'
END
WHERE migrated = 0
LIMIT 5000;   -- 每次處理 5000 筆

COMMIT;

說明：使用 CASE 進行條件映射，LIMIT 控制單次批次大小，搭配交易確保每批次要麼全部成功，要麼全部失敗。

---

常見陷阱與最佳實踐

陷阱	可能的後果	解決方案 / 最佳實踐
忘記 WHERE	整表資料被覆寫，資料災難	養成寫完 UPDATE 後先執行 SELECT 檢查條件是否正確
WHERE 條件過於寬鬆	更新過多筆，效能下降、鎖表時間過長	使用 索引、LIMIT、ORDER BY 來縮小範圍
更新同時觸發大量觸發器 (Trigger)	觸發器內部執行慢，導致整體交易卡住	評估是否真的需要觸發器，或改為 批次處理
未考慮交易 (Transaction)	多筆 UPDATE 只成功部份，資料不一致	對於 關聯性強 的多表更新，務必使用 START TRANSACTION
更新欄位類型不匹配	MySQL 會隱式轉型，可能產生錯誤或資料截斷	確認 資料型別，必要時使用 CAST() 或 CONVERT()
大量更新導致鎖表	其他查詢被阻塞，系統效能下降	使用 小批次 (LIMIT)、非高峰時間 執行，或 分區表
忽略自動遞增 (AUTO_INCREMENT) 欄位	誤把自增欄位寫入新值，導致衝突	永遠不要在 UPDATE 中修改 AUTO_INCREMENT 欄位

其他最佳實踐

1. 先 SELECT 後 UPDATE：在開發階段，可先寫出 SELECT 條件，確認回傳的列正是要更新的目標。
2. 使用參數化查詢：在程式碼中避免直接拼接字串，使用預備語句（prepared statement）防止 SQL Injection。
3. 適度加索引：WHERE 常用欄位、JOIN 條件欄位都應建立索引，提升搜尋與更新效率。
4. 記錄變更日誌：若業務需要追蹤歷史，建議在 UPDATE 前先把舊資料寫入審計表（audit table）。
5. 測試環境驗證：大規模更新前，先在測試或備份資料庫執行，確保語法與效能符合預期。

---

實際應用場景

1. 電子商務：庫存同步

當顧客下單時，需要同時扣減 inventory.stock、寫入 orders、更新 sales_summary。若其中任一步失敗，整筆交易必須回滾，以避免 超賣 或 庫存不符。這裡會結合 多表 UPDATE、交易控制 與 觸發器（記錄庫存變更）來完成。

2. 社交平台：使用者等級升級

平台根據使用者活躍度自動升級等級。每天夜間跑批次腳本，使用 CASE WHEN 依照活躍分數更新 users.level，並在同一語句內寫入 level_changed_at。透過 LIMIT 分批執行，避免高峰時段影響使用者操作。

3. 金融系統：帳戶轉帳

轉帳流程必須同時扣除付款者餘額、增加收款者餘額，且必須使用 InnoDB 交易 保證「雙方餘額同步」的原子性。若任意一步失敗（例如餘額不足），立即執行 ROLLBACK，避免產生負餘額或金額遺失。

4. 內容管理系統（CMS）：批次發布

編輯好多篇文章後，管理員一次點擊「批次發布」按鈕，系統會一次性把 status 從 draft 改為 published，同時更新 publish_time。這裡使用 UPDATE + WHERE IN (list) 或 JOIN 連結 authors 表過濾出符合條件的稿件。

5. 物聯網（IoT）資料清理

感測器每分鐘上傳大量資料，為降低儲存成本，需要定期把 超過一年 的資料標記為 archived = 1。使用 UPDATE 搭配 日期函式、LIMIT 逐批更新，避免一次性鎖定整張巨大的資料表。

---

總結

• UPDATE 是 CRUD 中不可或缺的資料變更指令，掌握其語法與進階用法（算術運算、函式、JOIN、LIMIT、交易）才能在實務開發中寫出安全、效能佳的程式。
• 永遠檢查 WHERE 條件，避免整表更新；使用 索引、批次、交易 來控制鎖定範圍與資料一致性。
• 在多表或大量資料的情境下，JOIN 更新、CASE 條件映射、批次 LIMIT 都是提升效能與降低風險的關鍵技巧。
• 透過 審計日誌、參數化查詢、測試驗證，可以進一步保護資料安全，並在系統升級或維護時快速定位問題。

掌握以上概念與實作範例，你就能在 MySQL 中自信地使用 UPDATE 進行資料變更，為各種業務需求提供穩定、可靠的後端支援。祝開發順利，資料永遠保持最新與正確！