MySQL刪除方法delete、truncate、drop的區別是什么

本文小編為大家詳細介紹“MySQL刪除方法delete、truncate、drop的區別是什么”，內容詳細，步驟清晰，細節處理妥當，希望這篇“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 不能重置自增列。

讀到這里，這篇“MySQL刪除方法delete、truncate、drop的區別是什么”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注億速云行業資訊頻道。