您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL UPDATE/DELETE 實戰指南：修改刪除數據的標準語法、事務控制與大表安全策略

編輯：MySQL 綜合教學

修改與刪除是寫操作核心，錯誤執行可能導致數據永久丟失。本文規範標準語法、事務邊界、WHERE 陷阱，並給出大表安全執行策略。

標準語法與核心範例

操作	語法	範例
UPDATE	`UPDATE table SET col1=val1, col2=val2 [WHERE condition];`	`UPDATE pet SET sex='f' WHERE name='tom';`
DELETE	`DELETE FROM table [WHERE condition];`	`DELETE FROM pet WHERE name='tom';`
全表清空	`DELETE FROM table;` / `TRUNCATE TABLE table;`	`TRUNCATE` 更快、重置自增 ID、不觸發觸發器、不可回滾

WHERE 子句：生命線與陷阱

永遠先 SELECT 驗證：SELECT * FROM pet WHERE name='tom'; 確認影響行數，再執行 UPDATE/DELETE。
主鍵/唯一鍵精確匹配：WHERE id = 123 最安全，避免誤傷。
避免隱式轉換：WHERE varchar_id = 123 → 全表掃描 + 隱式 CAST，改 WHERE varchar_id = '123'。
小心 NULL：WHERE col = NULL 永遠為假，必須 IS NULL。
限制影響行數：UPDATE ... WHERE ... LIMIT 1000;（MySQL 支援）分批執行，減少鎖持有時間。

事務控制：原子性與一致性保障


START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
-- 業務邏輯檢查
SELECT balance FROM account WHERE id IN (1,2);
COMMIT;  -- 或 ROLLBACK;

InnoDB 默認 REPEATABLE READ 隔離級，配合 FOR UPDATE 鎖定讀防併發扣款超賣。
長事務會阻塞 Purge 線程，導致 Undo Log 膨脹、主從延遲，**單事務執行時間建議 < 1 秒**。

大表批量修改/刪除安全策略

策略	適用場景	實施要點
分批次循環	更新/刪除百萬行以上	每批 500-5000 行，`LIMIT` + `WHERE id > last_id`，間隔 `SLEEP(0.1)` 讓出鎖
pt-archiver	歸檔舊數據（刪除/移表）	基於主鍵範圍分塊，支援 --no-delete 配合 INSERT..SELECT 歸檔，可暫停/限流
新表換舊表	全表大結構變更+數據清洗	建新表 → 雙寫/同步 → 切換讀流量 → 下線舊表，零鎖風險
軟刪除	業務需保留歷史/恢復	增加 `is_deleted TINYINT DEFAULT 0`，查詢加 `WHERE is_deleted=0`，定期物理清理

分批腳本模板（Bash + MySQL）


#!/bin/bash
LAST_ID=0
BATCH=1000
while true; do
  ROWS=$(mysql -N -e "UPDATE large_t SET status=2 WHERE status=1 AND id > $LAST_ID ORDER BY id LIMIT $BATCH; SELECT ROW_COUNT();")
  [[ $ROWS -eq 0 ]] && break
  LAST_ID=$(mysql -N -e "SELECT MAX(id) FROM large_t WHERE status=2 AND id > $LAST_ID LIMIT 1;")
  sleep 0.1
done

避坑清單

DELETE FROM t WHERE id IN (SELECT id FROM t WHERE ...) 子查詢鎖升級風險，改用 Join 或應用層分批。
外鍵 ON DELETE CASCADE 可能引發連鎖刪除風暴，生產建議應用層控制。
TRUNCATE 非事務操作，不可回滾，主從同步為 DDL，慎用。
備份優先：任何批量寫入前，確認最近一次全備/增備可用。

敬畏寫操作，先驗證、再分批、後備份，是 DBA 守護數據安全的鐵律。