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

2021-02-21 程序猿DD

點擊上方藍色「程序猿DD」，選擇「設為星標」

回復「資源」獲取獨家整理的學習資料！

這裡有個【1024】紅包等你來領取

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 操作。往期面試題匯總：001期~150期匯總
示例：查看表佔用硬碟空間大小的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）和重做操作，生成的大量日誌也會佔用磁碟空間。往期面試題匯總：001期~150期匯總
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是把書燒了
來源 | blog.csdn.net/qq_39390545/article/details/107144859
掃一掃，關注我
一起學習，一起進步
每周贈書，福利不斷

相關焦點

  • 面試題:Mysql中drop、delete與truncate有什麼區別?
    drop語句將表所佔用的空間全釋放掉。（3） 一般而言，drop > truncate > delete（4） 應用範圍。TRUNCATE 只能對TABLE；DELETE可以是table和view（5） TRUNCATE 和DELETE只刪除數據，而DROP則刪除整個表（結構和數據）。
  • 面試官靈魂一問:MySQL 的 delete、truncate、drop 有什麼區別?
    「delete from table 呀」「怪不得，其實要刪除MySQL數據是有好幾種方式的，有些場景下是不應該用DELETE的，比如你這種情況。好了，讓我來給你講一下吧。」MySQL刪除數據的方式都有哪些？
  • 面試被問到SQL | delete、truncate、drop 有什麼區別?
    現在來介紹另外兩個兄弟,都是刪除表數據的,其實也是很容易理解的 老大-drop出沒場合:drop table  tb --tb表示數據表的名字,下同絕招:刪除內容和定義，釋放空間。簡單來說就是把整個表去掉.以後要新增數據是不可能的,除非新增一個表,       例如:一個班就是一個表,學生就是表中的數據,學生的職務就是定義       drop table class,就是把整個班移除.學生和職務都消失比如下面TestSchool資料庫中有兩張表[Classes]表和[Teacher]表
  • 資料庫中 truncate與delete的區別你了解多少?
    要知道truncate和delete的區別，首先我們應該知道它們是什麼 truncate [/trket/]，delete [/dlit/].一。語法：(1)truncate: TRUNCATE [TABLE] tbl_name 。
  • Oracle資料庫知識點分享:truncate和delete區別
    嶽彩波，擅長Oracle、Mysql，有豐富的資料庫維護、調優經驗，曾服務於金融行業，目前專注於第三方支付領域。
  • SQL Server中DELETE和TRUNCATE的區別
    DELETE和TRUNCATE語句之間的區別是求職面試中最常見的問題之一。這兩條語句都可以從表中刪除數據。然而，也有不同之處。TRUNCATEDELETE從表中刪除所有記錄。我們不能使用WHERE刪除特定的記錄刪除所有記錄，並可以使用WHERE刪除特定記錄。
  • 面試被問delete後有必要加 limit麼 ?
    delete from t where sex = 1 limit 100; 你或許沒有用過，在一般場景下，我們對 delete 後是否需要加 limit 的問題很陌生，也不知有多大區別，今天帶你來了解一下，記得 mark！
  • delete後加 limit是個好習慣麼 !
    delete from t where sex = 1 limit 100; 你或許沒有用過，在一般場景下，我們對 delete 後是否需要加 limit 的問題很陌生，也不知有多大區別，今天帶你來了解一下，記得 mark！
  • delete後加 limit是個好習慣麼 ?
    delete from t where sex = 1 limit 100; 你或許沒有用過，在一般場景下，我們對 delete 後是否需要加 limit 的問題很陌生，也不知有多大區別，今天帶你來了解一下，記得 mark！
  • delete後加 limit是個好習慣麼?
    delete from t where sex = 1 limit 100; 你或許沒有用過，在一般場景下，我們對 delete 後是否需要加 limit 的問題很陌生，也不知有多大區別，今天帶你來了解一下，記得 mark！
  • delete 後加 limit 是個好習慣麼?!
    delete from t where sex = 1 limit 100;你或許沒有用過，在一般場景下，我們對刪除後是否需要加limit的問題很陌生，也不知有多大區別，今天帶你來了解一下，記得mark！
  • 漫話:如何給女朋友解釋什麼是刪庫跑路?
    1、dorp ，drop 屬於資料庫定義語言DDL，表示刪除表， 也可以用來刪除資料庫，刪除表格中的索引。使用drop刪除表的語法是：drop table 表名刪除資料庫的語法是：drop database 資料庫名2、truncate，　truncate 屬於資料庫定義語言DDL，表示刪除表中所有數據，DDL操作是隱性提交的!
  • delete後加 limit是個好習慣麼
    delete from t where sex = 1 limit 100; 你或許沒有用過，在一般場景下，我們對 delete 後是否需要加 limit 的問題很陌生，也不知有多大區別，今天帶你來了解一下，記得mark！
  • 不懂就問:delete 後加 limit 是個好習慣麼 ?
    比如，在刪除執行中，第一條就命中了刪除行，如果 SQL 中有 limit 1；這時就 return 了，否則還會執行完全表掃描才 return。效率不言而喻。那麼，在日常執行 delete 時，我們是否需要養成加 limit 的習慣呢？是不是一個好習慣呢？在日常的 SQL 編寫中，你寫 delete 語句時是否用到過以下 SQL？
  • delete 和 delete [] 的真正區別
    （點擊上方公眾號，可快速關注）來源：cbNotes，2014-08-28連結：http://blog.csdn.net/cbnotes/article/details/38900799如有好的文章投稿，請點擊 → 這裡查看詳情；c++中對new申請的內存的釋放方式有delete和delete[]兩種方式，到底這兩者有什麼區別呢？
  • IT大叔詳談mysql中update語句和delete語句及應用
    如何實現呢？添加數據1、精準刪除；標準寫法delete from 表名 where 條件；從上面的圖片可以看到老韓的飯店開張了，飯店裡有各種商品，吃的喝的，有一天一個客人吃了老韓做的紅燒肉拉肚子了，老韓就不再賣紅燒肉了