淺析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影片教學】

#

以上是淺析MySQL刪除方法：delete、truncate、drop的差別的詳細內容。更多資訊請關注PHP中文網其他相關文章！