面試突擊58：truncate、delete和drop的6大區別

語言: CN / TW / HK
時間 2022-06-20 11:15:20

持續創作，加速成長！這是我參與「掘金日新計劃 · 6 月更文挑戰」的第10天，點選檢視活動詳情

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

truncate、delete、drop區別概述

它們 3 個的區別如下表所示：

| 區別點 | drop | truncate | delete | | ----------- | ---------------------------- | --------------------------- | -------------------------------- | | 執行速度 | 快 | 較快 | 慢 | | 命令分類 | DDL（資料定義語言） | DDL（資料定義語言） | DML（資料操作語言） | | 刪除物件 | 刪除整張表和表結構，以及表的索引、約束和觸發器。 | 只刪除表資料，表的結構、索引、約束等會被保留。 | 只刪除表的全部或部分資料，表結構、索引、約束等會被保留。 | | 刪除條件(where) | 不能用 | 不能用 | 可使用 | | 回滾 | 不可回滾 | 不可回滾 | 可回滾 | | 自增初始值 | - | 重置 | 不重置 |

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

準備工作

正式開始之前，我們先來建立一個使用者表和使用者測試資料，方便後續演示使用：

sql 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);

建立的表結構和資料如下圖所示： image.png

1.刪除物件不同

delete 和 truncate 只刪除表資料，不刪除表結構，其中 delete 刪除之後的結果如下： image.png 我們先將表還原到初始狀態，再使用 truncate 執行刪除操作，執行結果如下圖所示： image.png 把表還原到初始狀態，執行 drop 刪除語句，執行結果如下圖所示： image.png 從上述結果可以看出，delete 和 truncate 只刪除表資料，而 drop 把表結構和表資料都刪除了。

2.刪除條件支援不同

truncate 和 drop 不支援新增 where 條件，而 delete 支援 where 條件，如下圖所示： image.png

3.命令分類不同

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

  1. DDL【Data Definition Language】資料定義語言，用來維護儲存資料的結構代表指令: create、drop、alter、truncate。
  2. DML【Data Manipulation Language】資料操縱語言，用來對資料進行操作代表指令：insert，delete，update，DML 中又單獨分了一個 DQL，資料查詢語言，代表指令是 select。
  3. DCL【Data Control Language】資料控制語言，主要負責許可權管理和事務代表指令：grant，revoke，commit。

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

PS：truncate 是先複製一個新的表結構，再把原有舊錶結構和資料一起刪除，所以它屬於資料定義語言 DDL，而非資料操縱語言 DML。

4.回滾支援不同

delete 屬於 DML 支援事務回滾操作，而 truncate 和 drop 屬於 DDL，執行之後立馬生效，且資料是不可恢復的，接下來我們來驗證一下。 首先先將 MySQL 的自動事務提交關閉，自動事務提交的預設值是“ON”也就是開啟了自動提交，如下圖所示： image.png 我們使用以下命令將自動提交（事務）關掉：

sql set autocommit=off;

再次查詢事務自動提交的設定結果如下： image.png 接下來我們演示一下 delete 的回滾操作，如下圖所示： image.png 從上述結果可以看出 delete 之後是可以進行恢復（回滾）的，而 truncate 和 drop 之後是不能回滾的，各位老鐵可以使用相同的方法自行測試一下後兩種 SQL 的執行。

5.自增初始化不同

delete 不會重置自增欄位的初始值，如下圖所示： image.png 而 truncate 會重置自增欄位的初始值，如下圖所示： image.png

6.執行速度不同

delete 是逐行執行的，並且在執行時會把操作日誌記錄下來，以備日後回滾使用，所以 delete 的執行速度是比較慢的；而 truncate 的操作是先複製一個新的表結構，再把原先的表整體刪除，所以它的執行速度居中，而 drop 的執行速度最快。

總結

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

  1. 執行速度：drop > truncate > detele。
  2. delete 和 truncate 只刪除表資料，而 drop 會刪除表資料和表結構以及表的索引、約束和觸發器。
  3. delete 可以加 where 條件實現部分資料刪除，而 truncate 和 drop 不能加 where 條件是整體刪除。
  4. truncate 和 drop 是立即執行，且不能恢復；而 delete 會走事務，可以撤回和恢復。
  5. truncate 會重置自增列為 1，而 delete 不會重置自增列。
  6. truncate 和 drop 是 DDL 語句，而 delete 是 DML 語句。

是非審之於己，譭譽聽之於人，得失安之於數。

公眾號：Java面試真題解析

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

「」
「」