MySQL删除表三種操作（delete、truncate、drop的差別）

本文主要講mysql中三種删除表的操作，delete語句、truncate語句以及drop語句的差別

一.關聯與差別

1.差別

delete、truncate和drop是用于删除資料庫中的資料或對象的SQL語句，但它們有不同的功能和用法。

• delete語句：delete語句用于從表中删除特定的行或符合特定條件的行。delete語句是一種有條件的删除操作，它可以通過WHERE子句指定特定的條件來選擇要删除的行。delete語句隻能删除資料行，而不會删除表本身或其他對象。删除操作通常可以復原，也就是能夠撤回删除操作并恢複被删除的資料。
• truncate語句：truncate語句用于從表中删除所有的行，但保留表的結構。相比于delete，truncate是一種非常快速、高效的操作，因為它不需要逐行删除資料，而是直接将表中的資料頁清空。truncate操作不會觸發事務日志，無法復原。是以，一旦執行truncate操作，被删除的資料将無法恢複。
• drop語句：drop語句用于删除資料庫中的對象，包括表、視圖、索引等。drop語句是一種實體删除，它會完全删除指定的對象以及與該對象相關的所有資料和定義。drop操作是不可恢複的，是以在執行drop操作之前需要特别小心，以防止誤操作導緻資料丢失。

2.執行速度

drop> truncate > delete           

3.使用場景

在删除資料庫中的資料時，常常會存在這樣幾種情況：

• 已經确定删除整張表和資料，也不需要恢複資料了，用 drop；
• 删除資料不删除表，但是删除之後還有可能會後悔，用 delete；
• 隻想删除資料，不用删除表，删除資料後不會後悔了，用 truncate；

5.表和索引所占空間：

• 當表被truncate後，這個表和索引所占用的空間會恢複到初始大小；
• delete操作不會減少表或索引所占用的空間；
• drop語句将表所占用的空間全釋放掉。

2.總結

• delete 語句可以使用where子句實作部分删除，而truncate不可以，會将表中的整個資料全部删除，使用時，可以按需求選擇；
• 如果想從表中删除所有的資料，不要使用delete，可以使用truncate語句，因為這樣執行速度更快。truncate語句實際是删除原來的表然後重建立立一個新表；
• 在沒有備份情況下，謹慎使用 drop 與 truncate。要删除表結構使用drop;.對于由 FOREIGN KEY 限制引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，是以它不能激活觸發器。
• delete from删空表後，會保留一個空的頁，truncate在表中不會留有任何頁。
• DELETE語句執行删除的過程是每次從表中删除一行，并且同時将該行的删除操作作為事務記錄在日志中儲存以便進行進行復原操作。 TRUNCATE TABLE 則一次性地從表中删除所有的資料并不把單獨的删除操作記錄記入日志儲存，删除行是不能恢複的。并且在删除的過程中不會激活與表有關的删除觸發器。執行速度快。
• 當使用行鎖執行 DELETE 語句時，将鎖定表中各行以便删除。truncate始終鎖定表和頁，而不是鎖定各行。
• 如果有identity産生的自增id列，delete from後仍然從上次的數開始增加，即種子不變；使用truncate删除之後，種子會恢複到初始值。

delete删除内容，不删除表結構，可以删除全部資料，也可以删除部分資料，一行行删除，效率較低，可以復原。

truncate删除内容，不删除表結構，删除表全部資料，操作生效後，自動送出，不能復原。

drop既删除表内容，又删除表結構，執行速度最快。

二.用法：

一.delete

1.删除整張表的資料：

delete from table_name;           

2.删除部分資料，添加where子句：

delete  from table_name where...;           

3.說明

1. delete可以和where子句連用删除指定行。屬于DML語言，每次删除一行，都在事務日志中為所删除的每行記錄一項。産生rollback，事務送出之後才生效;如果有相應的 trigger,執行的時候将被觸發，如果删除大資料量的表速度會很慢。
2. 删除表中資料而不删除表的結構(定義)，同時也不釋放空間。
3. delete不會删除索引 （新插入的資料将在删除資料的索引後繼續增加）；

二.truncate

1.隻能操作表，将表中資料全部删除，在功能上和不帶where子句的delete語句相同：

truncate table table_name;           

2.說明

1. 預設情況下，truncate通過釋放存儲表資料所用的資料頁來删除資料，并且隻在事務日志中記錄頁的釋放。是以使用的系統和事務日志資源少，可以使用reuse storage; truncate會将高水線複位(回到最開始).
2. truncate是DDL語言, 操作立即生效,自動送出，原資料不放到rollback segment中,不能復原. 操作不觸發trigger.
3. 删除内容、釋放空間但不删除表的結構(定義)。

三.drop

1.drop語句将删除表的結構，以及被依賴的限制(constrain),觸發器(trigger),索引(index);

drop table table_name;           

2.說明

1. 删除之後，依賴于該表的存儲過程/函數将保留,但是變為invalid狀态.
2. drop也屬于DDL語言，立即執行，執行速度最快
3. 删除内容和定義，釋放空間。