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