您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL 刪除資料表資料三大指令差異：DROP vs TRUNCATE vs DELETE 完整對照

編輯：MySQL 資料庫相關知識

三個指令皆可「移除資料」但行為、效能、可回復性截然不同。選錯指令可能導致資料遺失、鎖爭用或無法回滾。

核心差異對照表

特性	DROP TABLE	TRUNCATE TABLE	DELETE FROM
操作對象	整張表 (結構+資料+索引+觸發器+權限)	表內所有資料 (保留結構、索引、觸發器、權限)	符合 WHERE 條件的資料列 (可指定條件)
DDL/DML	DDL	DDL	DML
執行速度	極快 (僅元數據操作)	極快 (僅釋放資料頁、重置高水位線)	較慢 (逐行刪除、記錄交易日誌、觸發 Trigger)
交易日誌	最少 (僅記錄物件刪除)	最少 (記錄頁釋放)	大量 (每行一筆日誌)
可 ROLLBACK	否 (隱式提交)	否 (隱式提交)	是 (在交易中)
觸發 Trigger	不觸發	不觸發	觸發 AFTER DELETE / INSTEAD OF
Identity/自增重置	表不存在	重置為種子值	不重置 (保留下一值)
外鍵約束	需先刪除參考表 FK	被參考表不可 TRUNCATE	檢查 FK、可級聯刪除
空間釋放	完全釋放	釋放資料頁、重置高水位線	不釋放 (標記為可重用，需 REBUILD/SHRINK)
權限需求	DROP 權限	ALTER + DROP (實際需 ALTER)	DELETE 權限

選擇指南

要整張表消失 → DROP TABLE (謹慎，無法回滾)。
清空全表資料、保留結構、要最快速度、不需觸發器 → TRUNCATE TABLE。
條件刪除、需觸發器、需交易回滾、大表分批刪除 → DELETE FROM ... WHERE ...。

大表分批刪除範本 (避免長鎖、日誌暴漲)

DECLARE @rows INT = 1;
WHILE @rows > 0
BEGIN
  DELETE TOP (5000) FROM big_table WHERE create_time < '2022-01-01';
  SET @rows = @@ROWCOUNT;
  CHECKPOINT;  -- 簡單復原模式下截斷日誌
  WAITFOR DELAY '00:00:00.500';  -- 降低鎖衝突
END

TRUNCATE 受限場景變通

被外鍵參考 → 暫時停用 FK：ALTER TABLE child NOCHECK CONSTRAINT fk_name; → TRUNCATE parent; → ALTER TABLE child CHECK CONSTRAINT fk_name;。
需保留部分資料 → DELETE 或 INSERT INTO new_table SELECT ... FROM old_table WHERE keep_condition; TRUNCATE old_table; INSERT INTO old_table SELECT * FROM new_table;。