SQL 刪除數據三大命令詳解：DROP vs TRUNCATE vs DELETE 核心差異與選型指南

SQL 刪除數據三大命令詳解：DROP vs TRUNCATE vs DELETE 核心差異與選型指南

DROP、TRUNCATE、DELETE 均可刪除表數據，但機制、效能、可恢復性、觸發器行為截然不同。本文通過圖解與實測對比，助你精準選型。

一、核心差異總覽

二、圖解與實測現象

1. DROP：暴力拆遷

DROP TABLE class;
-- 表結構、數據、索引、權限一次性消失
-- 如需再用，必須 CREATE TABLE 重建

2. TRUNCATE：精準清空

TRUNCATE TABLE teacher;
-- 數據清空，結構保留
-- 自增 ID 歸零：新插入 ID 從 1 開始
-- 高水位線重置，空間歸還 OS

實測對比：

• DELETE 後插入：ID 跳躍 (如 100→101)，空間未釋放
• TRUNCATE 後插入：ID 連續 (1,2,3)，空間已釋放

3. DELETE：外科手術

DELETE FROM teacher WHERE id > 10;
-- 逐行刪除，寫入 Undo Log + Redo Log
-- 可 ROLLBACK
-- 觸發 DELETE Trigger
-- 高水位線不變，空間標記可復用

三、TRUNCATE 進階特性

• MySQL 5.5+ 對 InnoDB 採用 Fast Truncate：直接丟棄並重建表空間檔案，極快
• 無法對有外鍵引用的父表執行 (需先 SET FOREIGN_KEY_CHECKS=0)
• 不觸發 ON DELETE 觸發器
• 二進制日誌記錄為 DROP TABLE + CREATE TABLE (STATEMENT 格式) 或表映射事件 (ROW 格式)

四、選型決策樹

需要刪除表結構嗎？
  ├─ 是 → DROP TABLE
  └─ 否 → 需要保留部分數據 (WHERE 條件)？
          ├─ 是 → DELETE FROM ... WHERE ...
          └─ 否 → 需要觸發 Trigger / 可回滾 / 受 FK 約束？
                  ├─ 是 → DELETE FROM table (無 WHERE)
                  └─ 否 → TRUNCATE TABLE (最快、空間全釋放、自增歸零)

五、生產環境避坑指南

1. 誤執行 TRUNCATE 無法回滾：操作前務必確認、備份、雙人複核
2. 大表 DELETE 導致主從延遲：分批刪除 (每批 1000-5000 行)、間隔 SLEEP(0.5)、或用 pt-archiver
3. 外鍵阻塞 TRUNCATE：SET FOREIGN_KEY_CHECKS=0; TRUNCATE ...; SET FOREIGN_KEY_CHECKS=1; (極謹慎)
4. 自增 ID 衝突：TRUNCATE 重置後，若應用依賴 ID 連續性需評估影響
5. 權限最小化：應用帳號僅授 DELETE，禁止 DROP/TRUNCATE

「刪除」看似簡單，實則關係到數據完整性、恢復能力、主從一致性與效能。根據業務語義選擇正確命令，並建立嚴格的變更管控流程，是 DBA 的基本功。