MySQL Truncate用法詳解

刪除表中的數(shù)據(jù)的方法有delete,truncate, 其中TRUNCATE TABLE用于刪除表中的所有行，而不記錄單個行刪除操作。TRUNCATE TABLE 與沒有 WHERE 子句的 DELETE 語句類似；但是，TRUNCATE TABLE 速度更快，使用的系統(tǒng)資源和事務日志資源更少。下面介紹SQL中Truncate的用法

當你不再需要該表時， 用 drop；當你仍要保留該表，但要刪除所有記錄時， 用 truncate；當你要刪除部分記錄時（always with a WHERE clause), 用 delete.

Truncate是一個能夠快速清空資料表內所有資料的SQL語法。并且能針對具有自動遞增值的字段，做計數(shù)重置歸零重新計算的作用。

MySQL Truncate用法

一、Truncate語法

[ { database_name.[ schema_name ]. | schema_name . } ]
table_name
[ ; ]

參數(shù)

database_name
數(shù)據(jù)庫的名稱。

schema_name
表所屬架構的名稱。

table_name
要截斷的表的名稱，或要刪除其全部行的表的名稱。

二、Truncate使用注意事項

1、TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統(tǒng)和事務日志資源少。

2、DELETE 語句每次刪除一行，并在事務日志中為所刪除的每行記錄一項。TRUNCATE TABLE 通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務日志中記錄頁的釋放。

3、TRUNCATE TABLE 刪除表中的所有行，但表結構及其列、約束、索引等保持不變。新行標識所用的計數(shù)值重置為該列的種子。如果想保留標識計數(shù)值，請改用 DELETE。如果要刪除表定義及其數(shù)據(jù)，請使用 DROP TABLE 語句。

4、對于由 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發(fā)器。

5、TRUNCATE TABLE 不能用于參與了索引視圖的表。

6、對用TRUNCATE TABLE刪除數(shù)據(jù)的表上增加數(shù)據(jù)時，要使用UPDATE STATISTICS來維護索引信息。

7、如果有ROLLBACK語句，DELETE操作將被撤銷，但TRUNCATE不會撤銷。

三、不能對以下表使用 TRUNCATE TABLE

1、由 FOREIGN KEY 約束引用的表。（您可以截斷具有引用自身的外鍵的表。）

2、參與索引視圖的表。

3、通過使用事務復制或合并復制發(fā)布的表。

4、對于具有以上一個或多個特征的表，請使用 DELETE 語句。

5、TRUNCATE TABLE 不能激活觸發(fā)器，因為該操作不記錄各個行刪除。

四、TRUNCATE、Drop、Delete區(qū)別

1.drop和delete只是刪除表的數(shù)據(jù)(定義),drop語句將刪除表的結構、被依賴的約束(constrain)、觸發(fā)器 (trigger)、索引(index);依賴于該表的存儲過程/函數(shù)將保留,但是變?yōu)閕nvalid狀態(tài)。
2.delete語句是DML語言,這個操作會放在rollback segement中,事物提交后才生效;如果有相應的觸發(fā)器(trigger),執(zhí)行的時候將被觸發(fā)。truncate、drop是DDL語言,操作后即 生效,原數(shù)據(jù)不會放到rollback中,不能回滾,操作不會觸發(fā)trigger。

3.delete語句不影響表所占用的extent、高水線(high watermark)保持原位置不動。drop語句將表所占用的空間全部釋放。truncate語句缺省情況下將空間釋放到minextents的 extent,除非使用reuse storage。truncate會將高水線復位(回到最初)。

4.效率方面:drop > truncate > delete

5.安全性:小心使用drop與truncate,尤其是在 沒有備份的時候,想刪除部分數(shù)據(jù)可使用delete需要帶上where子句,回滾段要足夠大,想刪除表可以用drop,想保留表只是想刪除表的所有數(shù)據(jù)、 如果跟事物無關可以使用truncate,如果和事物有關、又或者想觸發(fā) trigger,還是用delete,如果是整理表內部的碎片，可以用truncate跟上reuse stroage，再重新導入、插入數(shù)據(jù)。

6.delete是DML語句,不會自動提交。drop/truncate都是DDL語句,執(zhí)行后會自動提交。

7、drop一般用于刪除整體性數(shù)據(jù) 如表，模式，索引，視圖，完整性限制等；delete用于刪除局部性數(shù)據(jù) 如表中的某一元組

8、DROP把表結構都刪了；DELETE只是把數(shù)據(jù)清掉

9、當你不再需要該表時， 用 drop；當你仍要保留該表，但要刪除所有記錄時， 用 truncate；當你要刪除部分記錄時（always with a WHERE clause), 用 delete.

MYSQL中TRUNCATE和DELETE的區(qū)別

MYSQL中TRUNCATE和DELETE都能夠清理表中的數(shù)據(jù)，但是他們有什么區(qū)別呢？我們從下面的幾點來分析：

1. 條件刪除

這個比較好理解，因為DELETE是可以帶WHERE的，所以支持條件刪除；而TRUNCATE只能刪除整個表。

# delete - 條件刪除
DELETE FROM student WHERE id = 1;
# delete - 刪除整個表的數(shù)據(jù)
DELETE FROM student;
# truncate - 刪除整個表的數(shù)據(jù)
TRUNCATE TABLE student;

2. 事務回滾

由于DELETE是數(shù)據(jù)操作語言（DML - Data Manipulation Language），操作時原數(shù)據(jù)會被放到 rollback segment中，可以被回滾；而TRUNCATE是數(shù)據(jù)定義語言（DDL - Data Definition Language)，操作時不會進行存儲，不能進行回滾。

可以看到DELETE是可以回滾成功的。

可以看到TRUNCATE是不能回滾成功的。

3. 清理速度

在數(shù)據(jù)量比較小的情況下，DELETE和TRUNCATE的清理速度差別不是很大。但是數(shù)據(jù)量很大的時候就能看出區(qū)別。由于第二項中說的，TRUNCATE不需要支持回滾，所以使用的系統(tǒng)和事務日志資源少。DELETE 語句每次刪除一行，并在事務日志中為所刪除的每行記錄一項，固然會慢，但是相對來說也較安全。

4. 高水位重置

隨著不斷地進行表記錄的DML操作，會不斷提高表的高水位線（HWM），DELETE操作之后雖然表的數(shù)據(jù)刪除了，但是并沒有降低表的高水位，隨著DML操作數(shù)據(jù)庫容量也只會上升，不會下降。所以如果使用DELETE，就算將表中的數(shù)據(jù)減少了很多，在查詢時還是很和DELETE操作前速度一樣。
而TRUNCATE操作會重置高水位線，數(shù)據(jù)庫容量也會被重置，之后再進行DML操作速度也會有提升。

MySQL Truncate用法補充

前言：

當我們想要清空某張表時，往往會使用truncate語句。大多時候我們只關心能否滿足需求，而不去想這類語句的使用場景及注意事項。本篇文章主要介紹truncate語句的使用方法及注意事項。

1.truncate使用語法

truncate的作用是清空表或者說是截斷表，只能作用于表。truncate的語法很簡單，后面直接跟表名即可.

例如：truncate table tbl_name 或者 truncate tbl_name 。

執(zhí)行truncate語句需要擁有表的drop權限，從邏輯上講，truncate table類似于delete刪除所有行的語句或drop table然后再create table語句的組合。為了實現(xiàn)高性能，它繞過了刪除數(shù)據(jù)的DML方法，因此，它不能回滾。盡管truncate table與delete相似，但它被分類為DDL語句而不是DML語句。

2.truncate與drop,delete的對比

上面說過truncate與delete，drop很相似，其實這三者還是與很大的不同的，下面簡單對比下三者的異同。

• truncate與drop是DDL語句，執(zhí)行后無法回滾；delete是DML語句，可回滾。
• truncate只能作用于表；delete，drop可作用于表、視圖等。
• truncate會清空表中的所有行，但表結構及其約束、索引等保持不變；drop會刪除表的結構及其所依賴的約束、索引等。
• truncate會重置表的自增值；delete不會。
• truncate不會激活與表有關的刪除觸發(fā)器；delete可以。
• truncate后會使表和索引所占用的空間會恢復到初始大?。籨elete操作不會減少表或索引所占用的空間，drop語句將表所占用的空間全釋放掉。
  

3.truncate使用場景及注意事項

通過前面介紹，我們很容易得出truncate語句的使用場景，即該表數(shù)據(jù)完全不需要時可以用truncate。如果想刪除部分數(shù)據(jù)用delete，注意帶上where子句；如果想刪除表，當然用drop；如果想保留表而將所有數(shù)據(jù)刪除且和事務無關，用truncate即可；如果和事務有關，或者想觸發(fā)trigger，還是用delete；如果是整理表內部的碎片，可以用truncate然后再重新插入數(shù)據(jù)。

無論怎樣，truncate表都是高危操作，特別是在生產環(huán)境要更加小心，下面列出幾點注意事項，希望大家使用時可以做下參考。

• truncate無法通過binlog回滾。
• truncate會清空所有數(shù)據(jù)且執(zhí)行速度很快。
• truncate不能對有外鍵約束引用的表使用。
• 執(zhí)行truncate需要drop權限，不建議給賬號drop權限。
• 執(zhí)行truncate前一定要再三檢查確認，最好提前備份下表數(shù)據(jù)。

以上就是MySQL Truncate用法詳解的詳細內容，更多關于MySQL Truncate的資料請關注腳本之家其它相關文章！