MySQL數據刪除與更新全攻略：DELETE、TRUNCATE、UPDATE深度解析

數據變更操作是業務系統的日常高頻動作。UPDATE修改現有數據，DELETE與TRUNCATE清理數據，三者在語法、行為、性能、事務、觸發器、自增ID、復制等方面存在關鍵差異。本文構建完整知識體系，指導工程選型。

UPDATE：精準修改的藝術

核心語法

UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET col1 = val1, col2 = val2, ...
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];

關鍵特性

• WHERE缺省=全表更新：生產環境嚴禁無WHERE UPDATE，必加WHERE 1=0測試或工具攔截
• 類型隱式轉換：字符串賦值給數值列→截取前導數字；超範圍→截斷為邊界值；過長字符串→截斷
• NULL處理：非空列設NULL→轉為默認值（數值0、字符串''、時間'0000-00-00'）
• TIMESTAMP自動更新：定義ON UPDATE CURRENT_TIMESTAMP的列，任意列變更自動刷新
• 返回值：affected_rows = 實際修改行數（值未變不計入），非匹配行數

ORDER BY + LIMIT：精準控制更新範圍

-- 僅更新最早的10條待處理訂單
UPDATE orders SET status=2 WHERE status=1 ORDER BY created_at ASC LIMIT 10;

• 配合索引實現高效「分頁處理」模式
• 避免鎖升級為表鎖

DELETE：靈活但沉重的刪除

核心語法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];

關鍵特性

• 逐行處理：掃描匹配行 → 加鎖 → 寫Undo Log → 標記刪除 → 釋放鎖 → 後台Purge物理清理
• 返回值：實際刪除行數
• 自增ID保留：DELETE FROM t不重置AUTO_INCREMENT；DELETE FROM t WHERE 1掃全表同樣保留
• 支持ORDER BY/LIMIT：分批刪除大表利器
• 觸發器：激活BEFORE/AFTER DELETE
• 外鍵：受ON DELETE RESTRICT/CASCADE/SET NULL約束

大表分批刪除最佳實踐

-- 存儲過程/應用層循環
REPEAT
    DELETE FROM huge_table 
    WHERE created_at < '2023-01-01' 
    ORDER BY id LIMIT 10000;
    -- 間隔微睡避免阻塞
    SELECT SLEEP(0.1);
UNTIL ROW_COUNT() = 0 END REPEAT;

TRUNCATE TABLE：極速清空的DDL本質

核心機制

1. 本質是DDL而非DML：隱式提交當前事務
2. 操作：Drop表 → 按原結構重建空表
3. 不掃描行，不產生Undo Log，不激活DELETE觸發器
4. 重置AUTO_INCREMENT計數器歸1
5. 返回值：0（不統計行數）
6. 無WHERE、無ORDER BY、無LIMIT

適用場景 vs 禁用場景

UPDATE vs DELETE vs REPLACE 三方對決

工程選型決策樹

需要修改現有數據？ → 是 → UPDATE（加WHERE、加LIMIT、分批）
                           ↓ 否
需要刪除部分數據？ → 是 → DELETE（加WHERE、分批、注意FK）
                           ↓ 否
需要清空整表？ 
    ├─ 生產核心表/有FK/需觸發器/需回滾 → DELETE WHERE 1 分批
    └─ 測試/日誌/臨時/無FK/無觸發器 → TRUNCATE TABLE
需要「不存在則插入，存在則更新」？ → INSERT ... ON DUPLICATE KEY UPDATE
需要「全行替換、可接受ID變更、無FK」？ → REPLACE（罕見）

性能優化清單

• WHERE條件列建立索引，避免全表掃描鎖升級
• 大批量操作分塊（1000-5000行/批），間隔SLEEP(0.05-0.1)
• 避開業務高峰期，配合LOW_PRIORITY降低優先級
• 監控Innodb_row_lock_waits、Innodb_row_lock_time指標
• 刪除大量數據後考慮OPTIMIZE TABLE回收空間（鎖表，維護窗口執行）