1.前言

對于drop、truncate和delete，雖然簡單，但是真要使用或者面試時候問到還是需要有一定的總結，今天來簡單講講他們直接的區(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ù)操作語言，是一種有助于檢索和管理關系數(shù)據(jù)庫中數(shù)據(jù)的SQL命令。DML中常用的命令有：insert，update，delete和select等等。

2.drop

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

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

代碼示例：

DROP TABLE employees;

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

3.truncate

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

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

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

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

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

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

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

示例：

TRUNCATE TABLE employees;

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

4.delete

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

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

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

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

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

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

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

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

示例：

DELETE FROM employees WHERE department = 'Sales';

5.總結

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

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

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

最新評論