語法與行為對比表
| 指令 | 語法 | 數據 | 結構 | 事務/日誌 | 自增重置 | 速度 | 可回滾 | 觸發器 |
|---|
| DELETE | DELETE FROM tbl [WHERE ...] | 可按條件刪除 | 保留 | 記錄 Binlog (ROW 每行)，InnoDB 寫 Undo Log，可回滾 | 不重置 | 慢 (逐行) | ✅ (事務內) | ✅ 逐行觸發 |
| TRUNCATE | TRUNCATE TABLE tbl | 清空全表 | 保留 | **DDL 隱式提交**，不寫 Binlog 行，極快（直接釋放頁/重建 .ibd） | **重置為 1** | 極快 | ❌ (隱式提交) | ❌ 不觸發 |
| DROP | DROP TABLE tbl | 連同結構刪除 | **刪除** | DDL 隱式提交 | N/A | 極快 | ❌ | ❌ |
深度解析
DELETE
- 支持
WHERE、LIMIT、JOIN 精確刪除。
- InnoDB 標記刪除標記，後台 Purge 線程真正回收空間，**不釋放磁碟空間**給 OS（需
OPTIMIZE TABLE 或重建表）。
- 大量刪除建議分批：
DELETE ... LIMIT 10000 循環，避免長事務、鎖升級、Binlog 膨脹。
TRUNCATE
- 本質是
DROP + CREATE（5.0+ 優化為直接重建 .ibd 文件）。
- **不觸發 ON DELETE 觸發器**，外鍵約束下無法執行（報錯
ERROR 1701）。
- 適用場景：測試數據清理、日誌表輪轉、臨時表重置。
DROP
- 徹底移除表定義、數據、索引、觸發器、權限、分區定義。
- 不可恢復（除非有備份或 Binlog 基於時間點恢復）。
選型決策樹
需要保留表結構？
├─ 否 → DROP TABLE
└─ 是 → 需要條件刪除/觸發器/事務回滾？
├─ 是 → DELETE FROM ... WHERE ...
└─ 否 → 整表清空、重置自增、極速？
├─ 是 → TRUNCATE TABLE (確認無外鍵引用)
└─ 否 → DELETE FROM tbl (無 WHERE 等同全表刪，但保留自增、觸發器)
實戰避坑
| 場景 | 錯誤操作 | 後果 | 正確做法 |
|---|
| 大表清理 1 億行 | DELETE FROM big_table | 長事務、Binlog 爆盤、主從延遲、鎖表久 | 分批 DELETE ... LIMIT 5000 + 間隔睡眠，或 pt-archiver 歸檔 |
| 被外鍵引用的表清空 | TRUNCATE TABLE parent | 報錯 Cannot truncate a table referenced in a foreign key constraint | 先刪子表數據，或暫時禁用外鍵 SET FOREIGN_KEY_CHECKS=0 (謹慎) |
| 誤以為 TRUNCATE 可回滾 | 事務中 TRUNCATE; ROLLBACK; | 數據已不可恢復 | 用 DELETE 或先備份 |
| 生產環境直接 DROP | 無備份 DROP TABLE orders | 數據永久丟失 | 先 RENAME TABLE orders TO orders_bak_20240115 觀察一周再刪 |
總結口訣
DELETE 靈活慢、可回滾、觸發器；
TRUNCATE 全清快、重置自增、DDL不回滾；
DROP 連結構沒、謹慎用、備份先行。
