1.前言

對于drop、truncate和delete，雖然簡單，但是真要使用或者面試時候問到還是需要有一定的總結(jié)，今天來簡單講講他們直接的區(qū)別。在此之前先簡單了解下什么是DDL和DML。
DDL(數(shù)據(jù)定義語言,Data Definition Language)：DDL代表數(shù)據(jù)定義語言，是一種有助于創(chuàng)建數(shù)據(jù)庫模式的SQL命令。DDL中常用的命令有：create，drop，alter，truncate和rename等等。
DML(數(shù)據(jù)操作語言，Data Manipulation Language)：DML代表數(shù)據(jù)操作語言，是一種有助于檢索和管理關(guān)系數(shù)據(jù)庫中數(shù)據(jù)的SQL命令。DML中常用的命令有：insert，update，delete和select等等。

2.drop

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

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

代碼示例：

DROP TABLE employees;

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

3.truncate

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

• 快速清空表：TRUNCATE比使用DELETE刪除表中的所有行要快得多，因為它不逐行刪除數(shù)據(jù)，而是通過釋放存儲這些數(shù)據(jù)的數(shù)據(jù)頁來刪除數(shù)據(jù)并重新初始化表。

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

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

• 自動重置自增ID：對于有自增主鍵的表，TRUNCATE會重置自增計數(shù)器。

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

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

示例：

TRUNCATE TABLE employees;

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

4.delete

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

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

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

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

性能：因為DELETE操作逐行刪除數(shù)據(jù)，并記錄日志，所以在刪除大量數(shù)據(jù)時可能會比較慢。

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

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

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

示例：

DELETE FROM employees WHERE department = 'Sales';

5.總結(jié)

在速度上，一般來說，drop> truncate > delete。

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

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

最新評論