MySQL：刪除操作Delete、Truncate、Drop用法比較
今天小編給大家梳理一下MySQL刪除操作Delete、Truncate、Drop用法有什么區(qū)別，到底該如何合理使用，希望對大家能有幫助！
1、執(zhí)行速度比較
Delete、Truncate、Drop關(guān)鍵字都可以刪除數(shù)據(jù)
drop>truncate>delete
2、原理方面
2.1 delete
delete屬于數(shù)據(jù)庫DML操作語言，只會刪除數(shù)據(jù)表中的記錄，會執(zhí)行事務(wù)，執(zhí)行的時候也會觸發(fā)觸發(fā)器。
InnoDB數(shù)據(jù)庫引擎中，執(zhí)行delete操作只會給刪除的記錄打上了刪除標(biāo)記，并不會真正刪除數(shù)據(jù)，只是把刪除的數(shù)據(jù)記錄設(shè)置為不可見，不會釋放磁盤空間，如果插入新的數(shù)據(jù)可以覆蓋該部分空間。
如果開啟事務(wù)的話，執(zhí)行delete操作，會先將要刪除數(shù)據(jù)緩存到rollback segement中，等事務(wù)commit之后才生效。
delete from table_name 不帶查詢條件會刪除表的全部數(shù)據(jù)，MyISAM引擎會立刻釋放磁盤空間，InnoDB 不會釋放磁盤空間;如果帶查詢條件的話都不會釋放磁盤空間，可以執(zhí)行optimize table table_name 會立刻釋放磁盤空間。建議如果需要釋放存儲空間的話可以執(zhí)行delete后，然后執(zhí)行optimize table table_name 語句達(dá)到清理磁盤空間的目的。
-- 查詢數(shù)據(jù)庫test對應(yīng)的表t_user 占用的磁盤空間
- select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size
- from information_schema.tables
- where table_schema='test' AND table_name='t_user';
說明：delete 操作是逐行執(zhí)行刪除的，并且同時將每行的的刪除操作日志記錄在redo和undo表空間中去，便于進(jìn)行回滾（rollback）和重做操作，因此生成的大量操作日志也會占用磁盤空間。
2.2 truncate
truncate是數(shù)據(jù)庫DDL定義語言，不受事務(wù)影響，也不會觸發(fā) trigger。執(zhí)行操作后會立即生效，無法找回刪除的數(shù)據(jù)。
執(zhí)行truncate table table_name 會立刻釋放磁盤空間 ，不管是 InnoDB和MyISAM 都一樣 。
truncate可以退快速清空一個表。并且重置auto_increment自動增長的值。針對不同類型的數(shù)據(jù)存儲引擎是有區(qū)別的，具體如下：
MyISAM：truncate會重置auto_increment（自增序列）的值為1。而delete后表仍然保持auto_increment。
InnoDB：truncate會重置auto_increment的值為1。delete后表仍然保持auto_increment。但是在做delete整個表之后重啟MySQL的話，則重啟后的auto_increment會被置為1。
說明：InnoDB的表本身是無法持久保存auto_increment。delete表之后auto_increment仍然保存在內(nèi)存，但是重啟后就找不到了，只能從1開始。實際上重啟后的auto_increment會從 SELECT 1+MAX(ai_col) FROM t 開始。
使用truncate操作的時候要最好備份表，避免出現(xiàn)不可挽回的情況。
2.3 drop
drop屬于數(shù)據(jù)庫DDL定義語言，和truncate一樣。執(zhí)行后會立即生效，不可恢復(fù)。
drop table table_name 執(zhí)行成功后不管是MyISM還是InnoDB都會立刻釋放磁盤空間 ，并且會刪除該數(shù)據(jù)表上依賴的約束(constrain)、觸發(fā)器(trigger)、索引(index); 依賴于該表的存儲過程/函數(shù)將保留,但是會變?yōu)槭顟B(tài)。
總結(jié)
在工作當(dāng)中執(zhí)行數(shù)據(jù)庫刪除的時候一定要慎重再慎重，建議每次進(jìn)行數(shù)據(jù)刪除的使用最好數(shù)據(jù)表的備份工作，這樣就會大大減少你刪除跑路的幾率。很多時候不要過于相信自己的動手能力，老虎還有打盹的時候，萬一手滑了呢。盡可能養(yǎng)成好的數(shù)據(jù)庫運維習(xí)慣，這樣會讓自己少跌跟頭，你的事業(yè)才會更加順利。
本文轉(zhuǎn)載自微信公眾號「IT技術(shù)分享社區(qū)」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請聯(lián)系IT技術(shù)分享社區(qū)公眾號。
個人博客網(wǎng)站：https://programmerblog.xyz
