delete、truncate、drop的區別有哪些，該如何選擇

小伙伴想精準查找自己想看的MySQL文章？喏 →?MySQL專欄目錄 | 點擊這里

上周同事小姐姐問我：“哈哥你看，我發現MySQL有bug，我下午為了清理磁盤，明明刪除了100萬條MySQL數據，磁盤不僅沒有變小，反而更滿了呢？？”

那你是怎么刪除的？

“delete from table 呀”

“怪不得，其實要刪除MySQL數據是有好幾種方式的，有些場景下是不應該用DELETE的，比如你這種情況。好了，讓我來給你講一下吧。”

MySQL刪除數據的方式都有哪些？

咱們常用的三種刪除方式：通過 delete、truncate、drop 關鍵字進行刪除；這三種都可以用來刪除數據，但場景不同。

一、從執行速度上來說

drop > truncate >> DELETE

二、從原理上講

1、DELETE

DELETE from TABLE_NAME where xxx

1、DELETE屬于數據庫DML操作語言，只刪除數據不刪除表的結構，會走事務，執行時會觸發trigger；

2、在 InnoDB 中，DELETE其實并不會真的把數據刪除，mysql 實際上只是給刪除的數據打了個標記為已刪除，因此 delete 刪除表中的數據時，表文件在磁盤上所占空間不會變小，存儲空間不會被釋放，只是把刪除的數據行設置為不可見。雖然未釋放磁盤空間，但是下次插入數據的時候，仍然可以重用這部分空間（重用 → 覆蓋）。

3、DELETE執行時，會先將所刪除數據緩存到rollback segement中，事務commit之后生效;

4、delete from table_name刪除表的全部數據,對于MyISAM 會立刻釋放磁盤空間，InnoDB 不會釋放磁盤空間;

5、對于delete from table_name where xxx 帶條件的刪除, 不管是InnoDB還是MyISAM都不會釋放磁盤空間;

6、delete操作以后使用 optimize table table_name 會立刻釋放磁盤空間。不管是InnoDB還是MyISAM 。所以要想達到釋放磁盤空間的目的，delete以后執行optimize table 操作。

示例：查看表占用硬盤空間大小的SQL語句如下：（用M做展示單位，數據庫名：csjdemo，表名：demo2）

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size

from information_schema.tables

where table_schema='csjdemo' AND table_name='demo2';

然后執行空間優化語句，以及執行后的表Size變化：

optimize table demo2

再看看這張表的大小，就只剩下表結構size了。

7、delete 操作是一行一行執行刪除的，并且同時將該行的的刪除操作日志記錄在redo和undo表空間中以便進行回滾（rollback）和重做操作，生成的大量日志也會占用磁盤空間。

2、truncate

Truncate table TABLE_NAME

1、truncate：屬于數據庫DDL定義語言，不走事務，原數據不放到 rollback segment 中，操作不觸發 trigger。

執行后立即生效，無法找回

執行后立即生效，無法找回

執行后立即生效，無法找回

2、truncate table table_name 立刻釋放磁盤空間 ，不管是 InnoDB和MyISAM 。truncate table其實有點類似于drop table 然后creat,只不過這個create table 的過程做了優化，比如表結構文件之前已經有了等等。所以速度上應該是接近drop table的速度;

3、truncate能夠快速清空一個表。并且重置auto_increment的值。

但對于不同的類型存儲引擎需要注意的地方是：

對于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仍然保存在內存，但是重啟后就丟失了，只能從1開始。實質上重啟后的auto_increment會從 SELECT 1+MAX(ai_col) FROM t 開始。

4、小心使用 truncate，尤其沒有備份的時候，如果誤刪除線上的表，記得及時聯系中國民航，訂票電話：400-806-9553

3、drop

Drop table Tablename

1、drop：屬于數據庫DDL定義語言，同Truncate；

執行后立即生效，無法找回

執行后立即生效，無法找回

執行后立即生效，無法找回

2、drop table table_name 立刻釋放磁盤空間 ，不管是 InnoDB 和 MyISAM; drop 語句將刪除表的結構被依賴的約束(constrain)、觸發器(trigger)、索引(index);? 依賴于該表的存儲過程/函數將保留,但是變為 invalid 狀態。

3、小心使用 drop ，要刪表跑路的兄弟，請在訂票成功后在執行操作！訂票電話：400-806-9553

可以這么理解，一本書，delete是把目錄撕了，truncate是把書的內容撕下來燒了，drop是把書燒了

MySQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。