前言：

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

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)刪除表中的所有行數(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 不能重置自增列。

總結(jié)

delete、truncate 可用于刪除表中的行數(shù)據(jù)，而 drop 是把整張表全部刪除了，刪除的數(shù)據(jù)包含所有行數(shù)據(jù)和字段、索引等數(shù)據(jù)，其中 delete 刪除的數(shù)據(jù)可以被恢復(fù)，而 truncate 和 drop 是不可恢復(fù)的，但在執(zhí)行效率上，后兩種刪除方式又有很大的優(yōu)勢(shì)，所以要根據(jù)實(shí)際場(chǎng)景來(lái)選擇相應(yīng)的刪除命令，當(dāng)然 truncate 和 drop 這些不可恢復(fù)數(shù)據(jù)的刪除方式使用的時(shí)候也要小心。

到此這篇關(guān)于mysql中的delete,drop和truncate有什么區(qū)別的文章就介紹到這了,更多相關(guān)mysql delete drop truncate內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論