面試突擊58：truncate、delete和drop的6大區(qū)別！

作者 | 磊哥

來源 | Java面試真題解析（ID：aimianshi666）

轉(zhuǎn)載請聯(lián)系授權(quán)（微信ID：GG_Stone）

在 MySQL  中，使用 truncate、delete 和 drop 都可以實現(xiàn)表刪除，但它們 3 個的使用場景和執(zhí)行效果完全不同，接下來我們來盤點一下。

truncate、delete、drop區(qū)別概述

它們 3 個的區(qū)別如下表所示：

接下來我們用案例來演示一下它們的區(qū)別。

準(zhǔn)備工作

正式開始之前，我們先來創(chuàng)建一個用戶表和用戶測試數(shù)據(jù)，方便后續(xù)演示使用：

CREATE TABLE `userinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT comment '編號',
  `name` varchar(250) NOT NULL comment '姓名' unique,
  `balance` decimal(10,2) NOT NULL DEFAULT '0.00' comment '賬戶余額',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
insert into userinfo values(1,'張三',1000),(2,'李四',500),(3,'王五',2000),(4,'李六',500);

創(chuàng)建的表結(jié)構(gòu)和數(shù)據(jù)如下圖所示：

1.刪除對象不同

delete 和 truncate 只刪除表數(shù)據(jù)，不刪除表結(jié)構(gòu)，其中 delete 刪除之后的結(jié)果如下：我們先將表還原到初始狀態(tài)，再使用 truncate 執(zhí)行刪除操作，執(zhí)行結(jié)果如下圖所示：把表還原到初始狀態(tài)，執(zhí)行 drop 刪除語句，執(zhí)行結(jié)果如下圖所示：從上述結(jié)果可以看出，delete 和 truncate 只刪除表數(shù)據(jù)，而 drop 把表結(jié)構(gòu)和表數(shù)據(jù)都刪除了。

2.刪除條件支持不同

truncate 和 drop 不支持添加 where 條件，而 delete 支持 where 條件，如下圖所示：

3.命令分類不同

truncate、delete 和 drop 所屬 SQL 分類不同，SQL 分為以下 3 類：

1. DDL【Data Definition Language】數(shù)據(jù)定義語言，用來維護(hù)存儲數(shù)據(jù)的結(jié)構(gòu)代表指令: create、drop、alter、truncate。
2. DML【Data Manipulation Language】數(shù)據(jù)操縱語言，用來對數(shù)據(jù)進(jìn)行操作代表指令：insert，delete，update，DML 中又單獨(dú)分了一個 DQL，數(shù)據(jù)查詢語言，代表指令是 select。
3. DCL【Data Control Language】數(shù)據(jù)控制語言，主要負(fù)責(zé)權(quán)限管理和事務(wù)代表指令：grant，revoke，commit。

其中 delete 屬于 DML，而 truncate 和 drop 屬于 DDL。

PS：truncate 是先復(fù)制一個新的表結(jié)構(gòu)，再把原有舊表結(jié)構(gòu)和數(shù)據(jù)一起刪除，所以它屬于數(shù)據(jù)定義語言 DDL，而非數(shù)據(jù)操縱語言 DML。

4.回滾支持不同

delete 屬于 DML 支持事務(wù)回滾操作，而 truncate 和 drop 屬于 DDL，執(zhí)行之后立馬生效，且數(shù)據(jù)是不可恢復(fù)的，接下來我們來驗證一下。首先先將 MySQL 的自動事務(wù)提交關(guān)閉，自動事務(wù)提交的默認(rèn)值是“ON”也就是開啟了自動提交，如下圖所示：我們使用以下命令將自動提交（事務(wù)）關(guān)掉：

set autocommit=off;

再次查詢事務(wù)自動提交的設(shè)置結(jié)果如下：接下來我們演示一下 delete 的回滾操作，如下圖所示：從上述結(jié)果可以看出 delete 之后是可以進(jìn)行恢復(fù)（回滾）的，而 truncate 和 drop 之后是不能回滾的，各位老鐵可以使用相同的方法自行測試一下后兩種 SQL 的執(zhí)行。

5.自增初始化不同

delete 不會重置自增字段的初始值，如下圖所示：而 truncate 會重置自增字段的初始值，如下圖所示：

6.執(zhí)行速度不同

delete 是逐行執(zhí)行的，并且在執(zhí)行時會把操作日志記錄下來，以備日后回滾使用，所以 delete 的執(zhí)行速度是比較慢的；而 truncate 的操作是先復(fù)制一個新的表結(jié)構(gòu)，再把原先的表整體刪除，所以它的執(zhí)行速度居中，而 drop 的執(zhí)行速度最快。

總結(jié)

truncate、drop 和 delete 的區(qū)別主要有以下 6 點：

1. 執(zhí)行速度：drop > truncate > detele。
2. delete 和 truncate 只刪除表數(shù)據(jù)，而 drop 會刪除表數(shù)據(jù)和表結(jié)構(gòu)以及表的索引、約束和觸發(fā)器。
3. delete 可以加 where 條件實現(xiàn)部分?jǐn)?shù)據(jù)刪除，而 truncate 和 drop 不能加 where 條件是整體刪除。
4. truncate 和 drop 是立即執(zhí)行，且不能恢復(fù)；而 delete 會走事務(wù)，可以撤回和恢復(fù)。
5. truncate 會重置自增列為 1，而 delete 不會重置自增列。
6. truncate 和 drop 是 DDL 語句，而 delete 是 DML 語句。

是非審之于己，毀譽(yù)聽之于人，得失安之于數(shù)。

公眾號：Java面試真題解析

面試合集：https://gitee.com/mydb/interview