萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL DELETE vs TRUNCATE vs DROP:刪除操作深度對比與選型指南

語法速覽

-- DELETE:DML,可帶條件,可回滾
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE ...] [ORDER BY ...] [LIMIT ...];

-- TRUNCATE:DDL,清空全表,不可回滾
TRUNCATE [TABLE] tbl_name;

-- DROP:DDL,刪除表結構及數據
DROP TABLE [IF EXISTS] tbl_name;

核心差異對比表

維度DELETETRUNCATEDROP
類型DMLDDLDDL
執行速度慢(逐行刪除、記錄日志)快(釋放數據頁、僅記錄頁釋放)最快(直接刪除元數據)
事務安全✅ 支持回滾❌ 不支持回滾❌ 不支持回滾
觸發器✅ 觸發 FOR EACH ROW❌ 不觸發❌ 不觸發
自增計數器保留,繼續遞增重置為 1(或種子值)表不存在,無計數器
空間釋放不釋放(高水位線不變)釋放到 MIN_EXTENTS(可 REUSE STORAGE)完全釋放
外鍵約束檢查約束,可刪除❌ 被引用表不可 TRUNCATE✅ 級聯刪除(若定義 CASCADE)
WHERE 條件✅ 支持❌ 不支持❌ 不支持

DELETE 進階用法

多表刪除(JOIN 語法)

-- 刪除 t1 中在 t2 有匹配的行
DELETE t1 FROM t1 INNER JOIN t2 ON t1.id = t2.id;

-- 刪除 t1 中在 t2 無匹配的行
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL;

限制刪除行數(防長事務阻塞)

DELETE FROM big_table WHERE create_time < '2023-01-01' LIMIT 10000;

TRUNCATE 適用場景與禁忌

  • ✅ 適用:定時清理日志表、臨時表、測試數據重置、大表全量刷新前清空。
  • ❌ 禁用:被外鍵引用的父表、需觸發 Trigger 的表、事務中需回滾的操作。

實戰選型決策樹

  1. 要刪除部分數據 → DELETE + WHERE(加 LIMIT 分批)。
  2. 要刪除全表數據且保留表結構:
    • 無外鍵、無觸發器、不需回滾 → TRUNCATE(秒級完成)。
    • 有外鍵/觸發器/需回滾 → DELETE 無 WHERE(慢但安全)。
  3. 要徹底移除表(結構+數據+索引+約束) → DROP TABLE。
  4. 要整理碎片 → TRUNCATE + REUSE STORAGE 後重新導入數據。

權限說明

  • DELETE:需表級 DELETE 權限。
  • TRUNCATE:需 DROP 權限(實質是 DROP + CREATE)。
  • DROP:需 DROP 權限。

總結

DELETE 精準可控、TRUNCATE 極速重置、DROP 徹底銷毀。 根據業務對「可回滾」「觸發器」「外鍵」「自增重置」的要求選型。切記:無備份不敢 TRUNCATE/DROP,無 WHERE 不敢 DELETE。

copyright © 萬盛學電腦網 all rights reserved