你也許連刪庫跑路都不會（delete、drop和truncate刪除數(shù)據(jù)）
前言
這兩年 IT 界隔三岔五的出現(xiàn)一次程序員刪庫的新聞，這種刪庫跑路的行為往往會給受害公司造成很大的損失，甚至?xí)?dǎo)致一個公司的破產(chǎn)。我們程序員看到這類新聞的時候很大一部分會把它當(dāng)作一個閑聊的攤子，但是各位讀者你是否想過這么一個問題：我知道怎么正確刪庫嗎？看到這里估計(jì)有讀者會感覺刪庫誰不會啊 Delete 以下唄。如果你這么想的話那就接著看這篇文章吧，在后面的內(nèi)容中我將講解數(shù)據(jù)庫刪除數(shù)據(jù)的方式以及原理。
在 MySQL 中有三種刪除數(shù)據(jù)的方式，分別是 delete、drop 和 truncate。這三種方式用起來簡單，但是背后的原理和注意事項(xiàng)估計(jì)大部分開發(fā)人員并不清楚，下面我就來講解以下原理以及注意事項(xiàng)。
一、delete
Delete 是數(shù)據(jù)庫 DML 操作語言，在刪除數(shù)據(jù)的時候只會刪除數(shù)據(jù)而不會刪除數(shù)據(jù)所在表的表結(jié)構(gòu)，并且在刪除數(shù)據(jù)的時候會執(zhí)行事務(wù)和觸發(fā) trigger。而且如果你所使用的數(shù)據(jù)庫引擎是 InnoDB 的話 MySQL 會在執(zhí)行刪除數(shù)據(jù)操作的時候只把數(shù)據(jù)標(biāo)注為已刪除而不去真正的刪除數(shù)據(jù)，因此在這種情況下如果你使用 Delete 刪除數(shù)據(jù)的時候會發(fā)現(xiàn)表大小并沒有改變。
但是當(dāng)你的數(shù)據(jù)庫引擎是 MyISAM 的情況下執(zhí)行 delete from table 語句會真正的刪除表中的數(shù)據(jù)并釋放空間的，如果你想讓 MyISAM 引擎也能實(shí)現(xiàn) InnoDB 引擎假刪除的效果的話，你需要在 Delete 語句后面加上 Where 條件即可，例如 delete from user where age<18 這段語句執(zhí)行后符合條件的數(shù)據(jù)將會標(biāo)注為刪除，并且所占空間不釋放。
這時肯定有杠精的小朋友會說：“我就想使用 InnoDB 引擎，我也想釋放被刪除數(shù)據(jù)所占用的空間?！边@種想法 MySQL 為我們提供了解決方案，那就是 optimize table 語句，我們來看一下例子：我們用 Delete 語句刪除表中所有的數(shù)據(jù)后我們來查看以下現(xiàn)在這張表所占的空間大小（如下圖）
從上圖中我們可以看到我們刪除了 book 表中的所有數(shù)據(jù)，但是該表所占用的空間大小幾乎沒發(fā)生變化。這時我們執(zhí)行 optimize table 語句后再次查看表的空間大小，我們發(fā)現(xiàn)表的大小變成了 0.02M，只剩下了表結(jié)構(gòu)的大小了（如下圖）
我們在用 Delete 語句刪除數(shù)據(jù)的時候需要注意的是 delete 操作是一行一行的執(zhí)行的，并且會將每行的刪除日志記錄在 redo 和 undo 表空間中以方便日后回滾和重做，這樣就產(chǎn)生大量的日志進(jìn)而占用大量的空間。因此當(dāng)我們在進(jìn)項(xiàng)大規(guī)模刪除數(shù)據(jù)的時候不建議使用 Delete 語句。
Tip:這里有一個比較有意思的地方：雖說被刪除數(shù)據(jù)所占用的空間沒有被釋放，但是當(dāng)我們執(zhí)行 Insert 操作的時候這部分未被釋放的空間依然是可以被重用的。
二、truncate 和 drop
truncate 是數(shù)據(jù)庫 DDL 語言，它和 delete 最大的區(qū)別是它不走事務(wù)數(shù)據(jù)也不放在 rollback segment 里，同時也不會觸發(fā) trigger， 因此執(zhí)行 truncate 語句后會立即刪除指定的數(shù)據(jù)且無法恢復(fù)，并釋放數(shù)據(jù)所占用的空間（不管使用的數(shù)據(jù)庫引擎是 InnoDB 還是 MyISAM ）。truncate 語句一般用于快速清空表中數(shù)據(jù)并需要重置 auto_increment 值的情況。
關(guān)于重置 auto_increment 值需要有如下兩點(diǎn)注意：
- 在使用 InnoDB 引擎的情況下如果使用 truncate autoincrement會重置為 1 ，但是如果使用 delete 則 autoincrement 在重啟 MySQL 之前仍然保持當(dāng)前值，重啟值后重置為 1 ；
- 在使用 MyISAM 引擎的情況下如果使用 truncate autoincrement會重置為 1 ，但如果使用 delete 則 autoincrement 仍然保持當(dāng)前值。
drop 同樣是數(shù)據(jù)庫 DDL 語言，它和 truncate 一樣一旦執(zhí)行將立即刪除表和依賴、觸發(fā)器、索引，但是會保留和該表相關(guān)的存儲過程以及函數(shù)，并將狀態(tài)改為 invalid 。
總結(jié)
上面講這么多簡單總結(jié)一下：如果把表比喻成一個包子的話，那么 delete 就是把餡從包子里拿出來放在別的盤子里，truncate 就是把餡從包子里拿出來吃掉，drop 就是直接把包子吃掉。
刪庫的技能學(xué)會了嗎？
相關(guān)文章
NoSQL在2010年風(fēng)生水起，大大小小的Web站點(diǎn)在追求高性能高可靠性方面，不由自主都選擇了NoSQL技術(shù)作為優(yōu)先考慮的方面2016-02-02
使用Navicat導(dǎo)入和導(dǎo)出sql語句的圖文教程
Navicat是MySQL非常好用的可視化管理工具,功能非常強(qiáng)大,能滿足我們?nèi)粘?shù)據(jù)庫開發(fā)的所有需求,下面這篇文章主要給大家介紹了關(guān)于使用Navicat導(dǎo)入和導(dǎo)出sql語句的相關(guān)資料,需要的朋友可以參考下2023-03-03
數(shù)據(jù)庫系統(tǒng)結(jié)構(gòu)詳解之三級模式結(jié)構(gòu)
這篇文章主要為大家介紹了數(shù)據(jù)庫系統(tǒng)的結(jié)構(gòu)，文中通過圖文的方式詳細(xì)的解析了數(shù)據(jù)庫系統(tǒng)結(jié)構(gòu)的三級模式結(jié)構(gòu)，有需要的朋友可以借鑒參考下2021-09-09
頁面沒有顯示位,但有數(shù)據(jù)庫的報錯信息時,可使用報錯注入,下面這篇文章主要給大家介紹了關(guān)于SQL報錯注入的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-02-02
這篇文章主要給大家介紹了關(guān)于SQL注入中文件的讀寫方法，文中通過示例代碼介紹的非常詳細(xì)，相信對大家具有一定的參考價值，需要的朋友們下面來一起看看吧。2017-03-03
這篇文章主要為大家介紹了SQL關(guān)系模型，文中對SQL關(guān)系模型的知識作了詳細(xì)的梳理總結(jié)，有需要的朋友可以借鑒參考下希望能夠有所幫助2021-10-10
這篇文章主要為大家介紹了Doris實(shí)時多維分析的解決方案詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-05-05
在很多項(xiàng)目當(dāng)中都有sql文件導(dǎo)入到MySQL數(shù)據(jù)庫的需要,因?yàn)橛衧ql數(shù)據(jù)庫文件,這個項(xiàng)目才能正常運(yùn)行起來,那么現(xiàn)在就來學(xué)習(xí)一下怎么導(dǎo)入sql文件到navicat里面吧,需要的朋友可以參考下2024-03-03
在這里主要講解一下MySQL、SQLServer2000(及SQLServer2005)和ORCALE三種數(shù)據(jù)庫實(shí)現(xiàn)分頁查詢的方法。2009-07-07
圖文詳解如何在navicat中導(dǎo)入excel表格數(shù)據(jù)
Navicat可以方便的操作各種數(shù)據(jù)庫,也提供了豐富的導(dǎo)入導(dǎo)出功能,下面這篇文章主要給大家介紹了關(guān)于如何在navicat中導(dǎo)入excel表格數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下2023-02-02