MySQL刪除記錄Delete 與Truncate 語句介紹

我理解的在mysql中Delete 與Truncate使用都是刪除數據表中記錄，但delete帶條件刪除或不帶條件刪除，但表不會收回ID，然後 Truncate清空表，並且把表ID恢復初始。

MySQL為我們提供了delete和truncate語句來刪除數據。

delete 語句的定義：

經常和數據庫打交道的孩子們，刪除數據的時候用的大多都是 delete 語句。現在讓我們來看一下 delete語句的定義。

 

 代碼如下 復制代碼

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_definition]

[ORDER BY ...]

[LIMIT row_count]

這裡補充說明一下，也許有人要問了："小小子，你也太懶了，抄手冊上面的語法結構都不抄全。那些多表刪除的語法結構為啥不寫呢？" 我的答案是"這裡是博客小站，不可能面面俱到，只能列出平時用的最多的語法，至於一些更高深的東西，也許以後會介紹到，但是現在不在此篇日志討論之列"， YY完畢。

delete 語句的示例：

MySQL的這些語法都和口語類似，你要指出你想從哪個表刪除數據，還有刪除哪些數據，這就夠了。就像寫記敘文的時候，時間、地點、人物、環境、情節幾要素必不可少一樣。

示例是最形象，最能說明問題的。所以，不整沒用的了，直接進入主題。按照上面的語法結構，我想刪除 firends 表中所有 user_name 等於 simaopig 的記錄，就可以使用如下SQL語句：

 

 代碼如下 復制代碼 delete from friends where user_name = 'simaopig';

delete 注意事項：

從語法結構中，我們就可以看出，和 update 語法一樣，我們是可以省略 where 子句的。不過這是一個很危險的行為。因為如果不指定 where 子句，delete 將刪除表中所有的記錄，而且是立即刪除，即使你想哭都沒有地方，也沒有時間，因為你需要馬上和主管承認錯誤，並且立即找出MySQL日志，來回滾記錄。不過一旦你有過一次這樣的經歷，我相信這一定是印象深刻的。

truncate 語句的簡單說明：

這個語句之前我也沒有接觸過，也沒有使用過。因為一般情況下，刪除數據大家都在使用delete語句。其實這個 truncate 命令很簡單，它的意思是：刪除表的所有記錄。相當於 delete 語句不寫 where 子句一樣。其語法結構為：

 

 代碼如下 復制代碼 TRUNCATE [TABLE] tbl_name

這裡簡單的給出個示例，我想刪除 friends 表中所有的記錄，可以使用如下語句：

 

 代碼如下 復制代碼 truncate table friends;

truncate 和 delete的效率問題：

如果想要刪除表的所有數據，truncate語句要比 delete 語句快。因為 truncate 刪除了表，然後根據表結構重新建立它，而 delete 刪除的是記錄，並沒有嘗試去修改表。這也是為什麼當向一個使用 delete 清空的表插入數據時，MySQL 會記住前面產生的AUTOINCREMENT序列，並且繼續利用它對AUTOINCREMENT字段編號。而truncate刪除表後，表是從1開始為autoincrement字段編號。

不過truncate命令快規快，卻不像delete命令那樣對事務處理是安全的。因此，如果我們想要執行truncate刪除的表正在進行事務處理，這個命令就會產生退出並產生錯誤信息。

總結

注意:這裡說的delete是指不帶where子句的delete語句
相同點
truncate和不帶where子句的delete, 以及drop都會刪除表內的數據

不同點:

1. truncate和 delete只刪除數據不刪除表的結構(定義)
    drop語句將刪除表的結構被依賴的約束(constrain),觸發器(trigger),索引(index); 依賴於該表的存儲過程/函數將保留,但是變為invalid狀態.
2.delete語句是dml,這個操作會放到rollback segement中,事務提交之後才生效;如果有相應的trigger,執行的時候將被觸發.
   truncate,drop是ddl, 操作立即生效,原數據不放到rollback segment中,不能回滾. 操作不觸發trigger.
3.delete語句不影響表所占用的extent, 高水線(high watermark)保持原位置不動
  顯然drop語句將表所占用的空間全部釋放
  truncate 語句缺省情況下見空間釋放到 minextents個 extent,除非使用reuse storage;   truncate會將高水線復位(回到最開始).
4.速度,一般來說: drop>; truncate >; delete
5.安全性:小心使用drop 和truncate,尤其沒有備份的時候.否則哭都來不及
使用上,想刪除部分數據行用delete,注意帶上where子句. 回滾段要足夠大.
想刪除表,當然用drop
想保留表而將所有數據刪除. 如果和事務無關,用truncate即可. 如果和事務有關,或者想觸發trigger,還是用delete.
如果是整理表內部的碎片,可以用truncate跟上reuse stroage,再重新導入/插入數據