面試突擊55：delete、drop、truncate有什么區(qū)別？

作者 | 磊哥

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

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

在 MySQL 中，刪除的方法總共有 3 種：delete、truncate、drop，而三者的用法和使用場景又完全不同，接下來我們具體來看。

1.delete

detele 可用于刪除表的部分或所有數(shù)據(jù)，它的使用語法如下：

delete from table_name [where...] [order by...] [limit...]

PS：[] 中的命令為可選命令，可以被省略。

如果我們要刪除學(xué)生表中數(shù)學(xué)成績排名最高的前 3 位學(xué)生，可以使用以下 SQL：

delete from student order by math desc limit 3;

1.1 delete 實現(xiàn)原理

在 InnoDB 引擎中，delete 操作并不是真的把數(shù)據(jù)刪除掉了，而是給數(shù)據(jù)打上刪除標記，標記為刪除狀態(tài)，這一點我們可以通過將 MySQL 設(shè)置為非自動提交模式，來測試驗證一下。非自動提交模式的設(shè)置 SQL 如下：

set autocommit=0;

之后先將一個數(shù)據(jù) delete 刪除掉，然后再使用 rollback 回滾操作，最后驗證一下我們之前刪除的數(shù)據(jù)是否還存在，如果數(shù)據(jù)還存在就說明 delete 并不是真的將數(shù)據(jù)刪除掉了，只是標識數(shù)據(jù)為刪除狀態(tài)而已，驗證 SQL 和執(zhí)行結(jié)果如下圖所示：

1.2 關(guān)于自增列

在 InnoDB 引擎中，使用了 delete 刪除所有的數(shù)據(jù)之后，并不會重置自增列為初始值，我們可以通過以下命令來驗證一下：

2.truncate

truncate 執(zhí)行效果和 delete 類似，也是用來刪除表中的所有行數(shù)據(jù)的，它的使用語法如下：

truncate [table] table_name

truncate 在使用上和 delete 最大的區(qū)別是，delete 可以使用條件表達式刪除部分數(shù)據(jù)，而 truncate 不能加條件表達式，所以它只能刪除所有的行數(shù)據(jù)，比如以下 truncate 添加了 where 命令之后就會報錯：

2.1 truncate 實現(xiàn)原理

truncate 看似只刪除了行數(shù)據(jù)，但它卻是 DDL 語句，也就是 Data Definition Language 數(shù)據(jù)定義語言，它是用來維護存儲數(shù)據(jù)的結(jié)構(gòu)指令，所以這點也是和 delete 命令是不同的，delete 語句屬于 DML，Data Manipulation Language 數(shù)據(jù)操縱語言，用來對數(shù)據(jù)進行操作的。為什么 truncate 只是刪除了行數(shù)據(jù)，沒有刪除列數(shù)據(jù)（字段和索引等數(shù)據(jù)）卻是 DDL 語言呢？這是因為 truncate 本質(zhì)上是新建了一個表結(jié)構(gòu)，再把原先的表刪除掉，所以它屬于 DDL 語言，而非 DML 語言。

2.2 重置自增列

truncate 在 InnoDB 引擎中會重置自增列，如下命令所示：

3.drop

drop 和前兩個命令只刪除表的行數(shù)據(jù)不同，drop 會把整張表的行數(shù)據(jù)和表結(jié)構(gòu)一起刪除掉，它的語法如下：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [,tbl_name]

其中 TEMPORARY 是臨時表的意思，一般情況下此命令都會被忽略。

drop 使用示例如下：

三者的區(qū)別

1. 數(shù)據(jù)恢復(fù)方面：delete 可以恢復(fù)刪除的數(shù)據(jù)，而 truncate 和 drop 不能恢復(fù)刪除的數(shù)據(jù)。
2. 執(zhí)行速度方面：drop > truncate > delete。
3. 刪除數(shù)據(jù)方面：drop 是刪除整張表，包含行數(shù)據(jù)和字段、索引等數(shù)據(jù)，而 truncate 和 drop 只刪除了行數(shù)據(jù)。
4. 添加條件方面：delete 可以使用 where 表達式添加查詢條件，而 truncate 和 drop 不能添加 where 查詢條件。
5. 重置自增列方面：在 InnoDB 引擎中，truncate 可以重置自增列，而 delete 不能重置自增列。

總結(jié)

delete、truncate 可用于刪除表中的行數(shù)據(jù)，而 drop 是把整張表全部刪除了，刪除的數(shù)據(jù)包含所有行數(shù)據(jù)和字段、索引等數(shù)據(jù)，其中 delete 刪除的數(shù)據(jù)可以被恢復(fù)，而 truncate 和 drop 是不可恢復(fù)的，但在執(zhí)行效率上，后兩種刪除方式又有很大的優(yōu)勢，所以要根據(jù)實際場景來選擇相應(yīng)的刪除命令，當然 truncate 和 drop 這些不可恢復(fù)數(shù)據(jù)的刪除方式使用的時候也要小心。

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

公眾號：Java面試真題解析

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