delete和truncate 的差別

MySQL裡面，delete和truncate都能清空表資料，保留表結構。但是這2個指令還是有些差別的。

二者的差別在于：即便是支援事務的環境下，delete的能復原，truncate的復原不了。

看下面的例子：

> use hellodb;

> select * from coc; 原始表内容如下：

<a href="http://s2.51cto.com/wyfs02/M02/84/22/wKiom1eGaNfzFJh_AABhiV-zi0o355.jpg" target="_blank"></a>

##delete方式

&gt; begin;

&gt; delete from coc;

&gt; rollback;

&gt; select * from coc;  此時資料又恢複出來了

##truncate方式

&gt; truncate coc;

&gt; select * from coc; 此時資料再也恢複不了了,如下圖：

<a href="http://s3.51cto.com/wyfs02/M01/84/22/wKiom1eGaNjzQnuCAAB_N82sdTE147.jpg" target="_blank"></a>

下面内容摘錄自網上：

<a href="http://blog.163.com/jun_ai_ni_1314/blog/static/1378480552010426112233123/" target="_blank">http://blog.163.com/jun_ai_ni_1314/blog/static/1378480552010426112233123/</a>

相同點 

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&gt;; truncate &gt;; delete 

5.安全性:小心使用drop 和truncate,尤其沒有備份的時候.否則哭都來不及

使用上,想删除部分資料行用delete,注意帶上where子句. 復原段要足夠大. 

想删除表,當然用drop 

想保留表而将所有資料删除. 如果和事務無關,用truncate即可. 如果和事務有關,或者想觸發trigger,還是用delete. 

如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新導入/插入資料 

本文轉自 lirulei90 51CTO部落格，原文連結：http://blog.51cto.com/lee90/1826227，如需轉載請自行聯系原作者