本文主要講mysql中三種刪除表的操作，delete語句、truncate語句以及drop語句的區(qū)別

一.關(guān)聯(lián)與區(qū)別

1.區(qū)別

delete、truncate和drop是用于刪除數(shù)據(jù)庫中的數(shù)據(jù)或?qū)ο蟮腟QL語句，但它們有不同的功能和用法。

• delete語句：delete語句用于從表中刪除特定的行或符合特定條件的行。delete語句是一種有條件的刪除操作，它可以通過WHERE子句指定特定的條件來選擇要?jiǎng)h除的行。delete語句只能刪除數(shù)據(jù)行，而不會刪除表本身或其他對象。刪除操作通?？梢曰貪L，也就是能夠撤回刪除操作并恢復(fù)被刪除的數(shù)據(jù)。
• truncate語句：truncate語句用于從表中刪除所有的行，但保留表的結(jié)構(gòu)。相比于delete，truncate是一種非?？焖?、高效的操作，因?yàn)樗恍枰鹦袆h除數(shù)據(jù)，而是直接將表中的數(shù)據(jù)頁清空。truncate操作不會觸發(fā)事務(wù)日志，無法回滾。因此，一旦執(zhí)行truncate操作，被刪除的數(shù)據(jù)將無法恢復(fù)。
• drop語句：drop語句用于刪除數(shù)據(jù)庫中的對象，包括表、視圖、索引等。drop語句是一種物理刪除，它會完全刪除指定的對象以及與該對象相關(guān)的所有數(shù)據(jù)和定義。drop操作是不可恢復(fù)的，因此在執(zhí)行drop操作之前需要特別小心，以防止誤操作導(dǎo)致數(shù)據(jù)丟失。

2.執(zhí)行速度

drop> truncate > delete

3.使用場景

在刪除數(shù)據(jù)庫中的數(shù)據(jù)時(shí)，常常會存在這樣幾種情況：

• 已經(jīng)確定刪除整張表和數(shù)據(jù)，也不需要恢復(fù)數(shù)據(jù)了，用 drop；
• 刪除數(shù)據(jù)不刪除表，但是刪除之后還有可能會后悔，用 delete；
• 只想刪除數(shù)據(jù)，不用刪除表，刪除數(shù)據(jù)后不會后悔了，用 truncate；

4.表和索引所占空間：

• 當(dāng)表被truncate后，這個(gè)表和索引所占用的空間會恢復(fù)到初始大?。?/li>
• delete操作不會減少表或索引所占用的空間；
• drop語句將表所占用的空間全釋放掉。

5.總結(jié)

• delete 語句可以使用where子句實(shí)現(xiàn)部分刪除，而truncate不可以，會將表中的整個(gè)數(shù)據(jù)全部刪除，使用時(shí)，可以按需求選擇；
• 如果想從表中刪除所有的數(shù)據(jù)，不要使用delete，可以使用truncate語句，因?yàn)檫@樣執(zhí)行速度更快。truncate語句實(shí)際是刪除原來的表然后重新建立一個(gè)新表；
• 在沒有備份情況下，謹(jǐn)慎使用 drop 與 truncate。要?jiǎng)h除表結(jié)構(gòu)使用drop;.對于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應(yīng)使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發(fā)器。
• delete from刪空表后，會保留一個(gè)空的頁，truncate在表中不會留有任何頁。
• DELETE語句執(zhí)行刪除的過程是每次從表中刪除一行，并且同時(shí)將該行的刪除操作作為事務(wù)記錄在日志中保存以便進(jìn)行進(jìn)行回滾操作。
• TRUNCATE TABLE 則一次性地從表中刪除所有的數(shù)據(jù)并不把單獨(dú)的刪除操作記錄記入日志保存，刪除行是不能恢復(fù)的。并且在刪除的過程中不會激活與表有關(guān)的刪除觸發(fā)器。執(zhí)行速度快。
• 當(dāng)使用行鎖執(zhí)行 DELETE 語句時(shí)，將鎖定表中各行以便刪除。truncate始終鎖定表和頁，而不是鎖定各行。
• 如果有identity產(chǎn)生的自增id列，delete from后仍然從上次的數(shù)開始增加，即種子不變；使用truncate刪除之后，種子會恢復(fù)到初始值。

• delete刪除內(nèi)容，不刪除表結(jié)構(gòu)，可以刪除全部數(shù)據(jù)，也可以刪除部分?jǐn)?shù)據(jù)，一行行刪除，效率較低，可以回滾。
• truncate刪除內(nèi)容，不刪除表結(jié)構(gòu)，刪除表全部數(shù)據(jù)，操作生效后，自動(dòng)提交，不能回滾。
• drop既刪除表內(nèi)容，又刪除表結(jié)構(gòu)，執(zhí)行速度最快。

二.用法：

一.delete

1.刪除整張表的數(shù)據(jù)：

delete from table_name;

2.刪除部分?jǐn)?shù)據(jù)，添加where子句：

delete  from table_name where...;

3.說明

• delete可以和where子句連用刪除指定行。
• 屬于DML語言，每次刪除一行，都在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。產(chǎn)生rollback，事務(wù)提交之后才生效;如果有相應(yīng)的 trigger,執(zhí)行的時(shí)候?qū)⒈挥|發(fā)，如果刪除大數(shù)據(jù)量的表速度會很慢。
• 刪除表中數(shù)據(jù)而不刪除表的結(jié)構(gòu)(定義)，同時(shí)也不釋放空間。
• delete不會刪除索引 （新插入的數(shù)據(jù)將在刪除數(shù)據(jù)的索引后繼續(xù)增加）；

二.truncate

1.只能操作表，將表中數(shù)據(jù)全部刪除，在功能上和不帶where子句的delete語句相同：

truncate table table_name;

2.說明

• 默認(rèn)情況下，truncate通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。所以使用的系統(tǒng)和事務(wù)日志資源少，可以使用reuse storage; truncate會將高水線復(fù)位(回到最開始).
• truncate是DDL語言, 操作立即生效,自動(dòng)提交，原數(shù)據(jù)不放到rollback segment中,不能回滾. 操作不觸發(fā)trigger.
• 刪除內(nèi)容、釋放空間但不刪除表的結(jié)構(gòu)(定義)。

三.drop

1.drop語句將刪除表的結(jié)構(gòu)，以及被依賴的約束(constrain),觸發(fā)器(trigger),索引(index);

drop table table_name;

2.說明

• 刪除之后，依賴于該表的存儲過程/函數(shù)將保留,但是變?yōu)閕nvalid狀態(tài).
• drop也屬于DDL語言，立即執(zhí)行，執(zhí)行速度最快
• 刪除內(nèi)容和定義，釋放空間。

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

最新評論