淺析MySQL刪除方法：delete、truncate、drop的區別

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

1.delete

detele 可用於刪除表的部分或所有資料，它的使用語法如下：

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

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

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

delete from student order by math desc limit 3;

1.1 delete 實現原理

在 InnoDB 引擎中，delete 操作並不是真的把資料刪除掉了，而是給資料打上刪除標記，標記為刪除狀態，這一點我們可以通過將 MySQL 設定為非自動提交模式，來測試驗證一下。非自動提交模式的設定 SQL 如下：

set autocommit=0;

之後先將一個資料 delete 刪除掉，然後再使用 rollback 回滾操作，最後驗證一下我們之前刪除的資料是否還存在，如果資料還存在就說明 delete 並不是真的將資料刪除掉了，只是標識資料為刪除狀態而已，驗證 SQL 和執行結果如下圖所示：

1.2 關於自增列

在 InnoDB 引擎中，使用了 delete 刪除所有的資料之後，並不會重置自增列為初始值，我們可以通過以下命令來驗證一下：

2.truncate

truncate 執行效果和 delete 類似，也是用來刪除表中的所有行資料的，它的使用語法如下：

truncate [table] table_name

truncate 在使用上和 delete 最大的區別是，delete 可以使用條件表示式刪除部分資料，而 truncate 不能加條件表示式，所以它只能刪除所有的行資料，比如以下 truncate 新增了 where 命令之後就會報錯：

2.1 truncate 實現原理

truncate 看似只刪除了行資料，但它卻是 DDL 語句，也就是 Data Definition Language 資料定義語言，它是用來維護儲存資料的結構指令，所以這點也是和 delete 命令是不同的，delete 語句屬於 DML，Data Manipulation Language 資料操縱語言，用來對資料進行操作的。為什麼 truncate 只是刪除了行資料，沒有刪除列資料（欄位和索引等資料）卻是 DDL 語言呢？這是因為 truncate 本質上是新建了一個表結構，再把原先的表刪除掉，所以它屬於 DDL 語言，而非 DML 語言。

2.2 重置自增列

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

3.drop

drop 和前兩個命令只刪除表的行資料不同，drop 會把整張表的行資料和表結構一起刪除掉，它的語法如下：

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

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

drop 使用範例如下：

三者的區別

資料恢復方面：delete 可以恢復刪除的資料，而 truncate 和 drop 不能恢復刪除的資料。
執行速度方面：drop > truncate > delete。
刪除資料方面：drop 是刪除整張表，包含行資料和欄位、索引等資料，而 truncate 和 drop 只刪除了行資料。
新增條件方面：delete 可以使用 where 表示式新增查詢條件，而 truncate 和 drop 不能新增 where 查詢條件。
重置自增列方面：在 InnoDB 引擎中，truncate 可以重置自增列，而 delete 不能重置自增列。

總結

delete、truncate 可用於刪除表中的行資料，而 drop 是把整張表全部刪除了，刪除的資料包含所有行資料和欄位、索引等資料，其中 delete 刪除的資料可以被恢復，而 truncate 和 drop 是不可恢復的，但在執行效率上，後兩種刪除方式又有很大的優勢，所以要根據實際場景來選擇相應的刪除命令，當然 truncate 和 drop 這些不可恢復資料的刪除方式使用的時候也要小心。

【相關推薦：】

以上就是淺析MySQL刪除方法：delete、truncate、drop的區別的詳細內容，更多請關注TW511.COM其它相關文章！