1.前言

對(duì)于drop、truncate和delete，雖然簡(jiǎn)單，但是真要使用或者面試時(shí)候問(wèn)到還是需要有一定的總結(jié)，今天來(lái)簡(jiǎn)單講講他們直接的區(qū)別。在此之前先簡(jiǎn)單了解下什么是DDL和DML。

• DDL(數(shù)據(jù)定義語(yǔ)言,Data Definition Language)：DDL代表數(shù)據(jù)定義語(yǔ)言，是一種有助于創(chuàng)建數(shù)據(jù)庫(kù)模式的SQL命令。DDL中常用的命令有：create，drop，alter，truncate和rename等等。
• DML(數(shù)據(jù)操作語(yǔ)言，Data Manipulation Language)：DML代表數(shù)據(jù)操作語(yǔ)言，是一種有助于檢索和管理關(guān)系數(shù)據(jù)庫(kù)中數(shù)據(jù)的SQL命令。DML中常用的命令有：insert，update，delete和select等等。

2.drop

DROP命令用于刪除整個(gè)表（結(jié)構(gòu)和數(shù)據(jù)），或數(shù)據(jù)庫(kù)等對(duì)象，特點(diǎn)如下：

• 徹底刪除：DROP命令不僅刪除表內(nèi)的所有數(shù)據(jù)，還刪除了表的結(jié)構(gòu)定義。這個(gè)過(guò)程是不可逆的，除非有備份可以恢復(fù)。
• 釋放資源：執(zhí)行DROP命令后，與該表相關(guān)的數(shù)據(jù)庫(kù)資源會(huì)被釋放。
• 非事務(wù)性操作：DROP操作通常不能回滾，執(zhí)行DROP命令后，相關(guān)對(duì)象就被立即刪除。
• 自增ID：如果創(chuàng)建新表，自增ID會(huì)重新開(kāi)始計(jì)數(shù)。

代碼示例：

DROP TABLE employees;

注意：DROP語(yǔ)句執(zhí)行后立即生效，無(wú)法找回。

3.truncate

TRUNCATE命令用于刪除表中的所有行，其特點(diǎn)如下：

• 快速清空表：TRUNCATE比使用DELETE刪除表中的所有行要快得多，因?yàn)樗恢鹦袆h除數(shù)據(jù)，而是通過(guò)釋放存儲(chǔ)這些數(shù)據(jù)的數(shù)據(jù)頁(yè)來(lái)刪除數(shù)據(jù)并重新初始化表。

• 非事務(wù)性操作：盡管某些數(shù)據(jù)庫(kù)管理系統(tǒng)可能允許TRUNCATE操作在事務(wù)中回滾，但在很多情況下，TRUNCATE并不記錄詳細(xì)的日志，因此不能像DELETE操作那樣保證事務(wù)安全。

• 不觸發(fā)觸發(fā)器：通常，執(zhí)行TRUNCATE操作不會(huì)觸發(fā)表的觸發(fā)器。

• 自動(dòng)重置自增ID：對(duì)于有自增主鍵的表，TRUNCATE會(huì)重置自增計(jì)數(shù)器。

• truncate會(huì)刪除表中所有記錄，并且將重新設(shè)置高水線和所有的索引。

  就是truncate會(huì)刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識(shí)所用的計(jì)數(shù)值重置為該列的種子），缺省情況下將空間釋放到minextents的extent（就是表結(jié)構(gòu)中的段內(nèi)的區(qū)域），除非使用reuse storage（使用這句話(huà)，所在的extent空間不會(huì)被回收，只是將數(shù)據(jù)刪除掉，數(shù)據(jù)刪除之后的freespace空間，只能供本表使用，其他的不可以使用）。不會(huì)記錄日志，所以執(zhí)行速度很快，但不能通過(guò)rollback撤消操作（如果一不小心把一個(gè)表truncate掉，也是可以恢復(fù)的，只是不能通過(guò)rollback來(lái)恢復(fù)

示例：

TRUNCATE TABLE employees;

注意：TRUNCATE語(yǔ)句執(zhí)行后立即生效，無(wú)法找回。

4.delete

DELETE命令用于刪除表中的一行或多行記錄，具有如下特點(diǎn)：

• 選擇性刪除：可以通過(guò)WHERE子句指定刪除哪些行。若不指定WHERE子句，則刪除表中的所有行。

• 事務(wù)性操作：DELETE操作是事務(wù)安全的，這意味著你可以在一個(gè)事務(wù)中回滾DELETE操作。這在你意外刪除了錯(cuò)誤數(shù)據(jù)時(shí)非常有用。

• 觸發(fā)器：如果表上有觸發(fā)器，執(zhí)行DELETE操作會(huì)觸發(fā)它們。

• 性能：因?yàn)?code>DELETE操作逐行刪除數(shù)據(jù)，并記錄日志，所以在刪除大量數(shù)據(jù)時(shí)可能會(huì)比較慢。

• delete語(yǔ)句不影響表所占用的extent（就是表結(jié)構(gòu)的中的區(qū)），高水線(high watermark)保持原位置不變。 （高水位線就存在于段（segment)中，它用于標(biāo)識(shí)段中已使用過(guò)的數(shù)據(jù)塊與未使用的數(shù)據(jù)塊二者間交界，掃描表數(shù)據(jù)的時(shí)候，高水位線以下的所有數(shù)據(jù)塊都必須被掃描。）

• 在 InnoDB 中，delete其實(shí)并不會(huì)真的把數(shù)據(jù)刪除，mysql 實(shí)際上只是給刪除的數(shù)據(jù)打了個(gè)標(biāo)記為已刪除，因此 delete 刪除表中的數(shù)據(jù)時(shí)，表文件在磁盤(pán)上所占空間不會(huì)變小，存儲(chǔ)空間不會(huì)被釋放，只是把刪除的數(shù)據(jù)行設(shè)置為不可見(jiàn)。雖然未釋放磁盤(pán)空間，但是下次插入數(shù)據(jù)的時(shí)候，仍然可以重用這部分空間（重用 → 覆蓋）。

• delete執(zhí)行時(shí)，會(huì)先將所刪除數(shù)據(jù)緩存到rollback segement中，事務(wù)commit之后生效;delete from table_name刪除表的全部數(shù)據(jù)，對(duì)于MyISAM 會(huì)立刻釋放磁盤(pán)空間，InnoDB 不會(huì)釋放磁盤(pán)空間;
  對(duì)于delete from table_name where xxx帶條件的刪除, 不管是InnoDB還是MyISAM都不會(huì)釋放磁盤(pán)空間;
  delete操作以后使用 optimize table table_name則會(huì)立刻釋放磁盤(pán)空間。不管是InnoDB還是MyISAM 。所以要想達(dá)到釋放磁盤(pán)空間的目的，delete以后執(zhí)行optimize table操作。

示例：

DELETE FROM employees WHERE department = 'Sales';

5.總結(jié)

在速度上，一般來(lái)說(shuō)，drop> truncate > delete。

• 如果想刪除部分?jǐn)?shù)據(jù)用delete，注意帶上where子句，回滾段要足夠大；如果想刪除表，當(dāng)然用drop；
• 如果想保留表而將所有數(shù)據(jù)刪除，如果和事務(wù)無(wú)關(guān)(不能回滾)，用truncate即可；
• 如果和事務(wù)有關(guān)，或者想觸發(fā)trigger，還是用delete；
• 如果是整理表內(nèi)部的碎片，可以用truncate跟上reuse stroage，再重新導(dǎo)入/插入數(shù)據(jù)。
• truncate與drop是DDL語(yǔ)句，執(zhí)行后無(wú)法回滾；delete是DML語(yǔ)句，可回滾。
• truncate只能作用于表；delete，drop可作用于表、視圖等。
• truncate會(huì)清空表中的所有行，但表結(jié)構(gòu)及其約束、索引等保持不變；drop會(huì)刪除表的結(jié)構(gòu)及其所依賴(lài)的約束、索引等。
• truncate會(huì)重置表的自增值；delete不會(huì)。
• truncate不會(huì)激活與表有關(guān)的刪除觸發(fā)器；delete可以。
• truncate后會(huì)使表和索引所占用的空間會(huì)恢復(fù)到初始大小；delete操作不會(huì)減少表或索引所占用的空間，drop語(yǔ)句將表所占用的空間全釋放掉。

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

最新評(píng)論