mysql中的delete,drop和truncate有哪些區(qū)別

這篇文章主要講解了“mysql中的delete,drop和truncate有哪些區(qū)別”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“mysql中的delete,drop和truncate有哪些區(qū)別”吧！

1.delete

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

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

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

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

delete from student order by math desc limit 3;

1.1 delete 實(shí)現(xiàn)原理

在 InnoDB 引擎中，delete 操作并不是真的把數(shù)據(jù)刪除掉了，而是給數(shù)據(jù)打上刪除標(biāo)記，標(biāo)記為刪除狀態(tài)，這一點(diǎn)我們可以通過(guò)將 MySQL 設(shè)置為非自動(dòng)提交模式，來(lái)測(cè)試驗(yàn)證一下。

非自動(dòng)提交模式的設(shè)置 SQL 如下：

set autocommit=0;

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

1.2 關(guān)于自增列

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

2.truncate

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

truncate [table] table_name

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

2.1 truncate 實(shí)現(xiàn)原理

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

2.2 重置自增列

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

3.drop

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

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

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

drop 使用示例如下： 

4.三者的區(qū)別

• 數(shù)據(jù)恢復(fù)方面：delete 可以恢復(fù)刪除的數(shù)據(jù)，而 truncate 和 drop 不能恢復(fù)刪除的數(shù)據(jù)。

• 執(zhí)行速度方面：drop > truncate > delete。

• 刪除數(shù)據(jù)方面：drop 是刪除整張表，包含行數(shù)據(jù)和字段、索引等數(shù)據(jù)，而 truncate 和 drop 只刪除了行數(shù)據(jù)。

• 添加條件方面：delete 可以使用 where 表達(dá)式添加查詢條件，而 truncate 和 drop 不能添加 where 查詢條件。

• 重置自增列方面：在 InnoDB 引擎中，truncate 可以重置自增列，而 delete 不能重置自增列。

感謝各位的閱讀，以上就是“mysql中的delete,drop和truncate有哪些區(qū)別”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)mysql中的delete,drop和truncate有哪些區(qū)別這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！