MySQL 刪除表資料三大方式對比：DELETE vs TRUNCATE vs DROP

快速對比表

語法範例

-- DELETE：可帶條件，可回滾
DELETE FROM `orders` WHERE `created_at` < '2023-01-01';
DELETE FROM `logs`;  -- 刪全表，慢

-- TRUNCATE：極速清空，重置自增
TRUNCATE TABLE `logs`;

-- DROP：徹底刪除表
DROP TABLE `temp_table`;

深度解析

DELETE

• 逐行掃描、加鎖、寫入 Undo Log、Binlog（ROW 格式下每行一條）。
• 大量刪除建議分批次（如每次 1000 行），避免長事務、鎖升級、Binlog 膨脹。
• 刪除後空間標記為可重用，不歸還 OS。需 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 回收。

TRUNCATE

• 本質是 DROP TABLE + CREATE TABLE（保留結構）。
• 不觸發 ON DELETE 觸發器，不寫入 Binlog（STATEMENT 格式記錄一條 Truncate 事件）。
• InnoDB 5.5+ 支援 TRUNCATE 線上執行（MDL 鎖），不阻塞並發查詢。
• 外鍵約束存在時無法 TRUNCATE，需先刪除 FK 或改用 DELETE。

DROP

• 移除表定義、索引、約束、權限、觸發器、分區資訊。
• 不可回滾，極度危險。生產環境嚴禁手動執行，需走變更流程。

選擇指南

需要保留表結構、有 WHERE 條件、需觸發器、需事務回滾

→ DELETE（大量時分批）

需要清空全表、重置自增、極速、無外鍵、不需觸發器

→ TRUNCATE

表不再需要、結構也要刪除

→ DROP

結語

理解三者在日誌、鎖、觸發器、事務、空間回收、自增計數器上的本質差異，是 DBA 與開發人員的必修課。生產環境操作前務必備份、驗證、分批、可回滾。