SQL Truncate Table - tw511教學網

在本教學中，我們來學習如何使用SQL TRUNCATE TABLE語句高效，快速地刪除表中的所有資料。

1. SQL TRUNCATE TABLE語句簡介

要刪除表中的所有資料，可使用不帶WHERE子句的DELETE語句。但是對於具有幾百萬行的大表，DELETE語句很慢且效率不高。

要快速刪除大表中的所有行，使用以下TRUNCATE TABLE語句：

TRUNCATE TABLE table_name;

在此語法中，指定要在TRUNCATE TABLE子句後刪除資料的table_name。

某些資料庫系統(如MySQL和PostgreSQL)允許直接省略TABLE關鍵字，因此TRUNCATE TABLE語句如下所示：

TRUNCATE table_name;

發出TRUNCATE TABLE語句時，資料庫系統通過取消分配表分配的資料頁來刪除表中的所有行。通過這樣做，RDBMS可以減少紀錄檔記錄的資源和需要獲取的鎖的數量。

如果要一次截斷多個表，可以在TRUNCATE TABLE子句後使用逗號分隔的表名列表，如下所示：

TRUNCATE TABLE table_name1, table_name2, ...;

並非所有資料庫系統都支援這種使用TRUNCATE TABLE語句來一次刪除多個表。如果不使用，則要發出多個TRUNCATE TABLE語句來截斷多個表。

2. SQL TRUNCATE TABLE與DELETE

邏輯上，TRUNCATE TABLE語句和不帶WHERE子句的DELETE語句提供了從表中刪除所有資料的相同效果。但是，它們確實存在一些差別：

使用DELETE語句時，資料庫系統會記錄操作。通過一些努力，可以回滾已刪除的資料。但是，當使用TRUNCATE TABLE語句時，除非在尚未提交的事務中使用它，否則無法回滾。
要從外來鍵約束參照的表中刪除資料，不能使用TRUNCATE TABLE語句。在這種情況下，必須使用DELETE語句。
如果表具有與之關聯的觸發器，則TRUNCATE TABLE語句不會觸發delete觸發器。
執行TRUNCATE TABLE語句後，某些資料庫系統會將自動增量列(或標識，序列等)的值重置為其起始值。 DELETE語句不是這種情況。
帶有WHERE子句的DELETE語句從表中刪除部分資料，而TRUNCATE TABLE語句始終從表中刪除所有資料。

3. SQL TRUNCATE TABLE範例

下面我們來看一個截斷表的例子。
首先，建立一個名為big_table的新表，如下所示：

CREATE TABLE big_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    val INT
);

其次，如果要將樣本資料插入big_table表，請多次執行以下語句：

INSERT INTO big_table (val)
VALUES
    (RAND(100000));

請注意，如果使用支援儲存過程的資料庫系統，則可以將此語句放在迴圈中。例如，MySQL中的以下儲存過程將資料載入到big_table表中，其中包含num引數指定的行數。

DELIMITER $$
CREATE PROCEDURE load_big_table_data(IN num int)
BEGIN
 DECLARE counter int default 0;

 WHILE counter < num DO
   INSERT INTO big_table(val)
   VALUES(RAND(1000000));
 END WHILE;

END$$

以下語句呼叫load_big_table_data儲存過程將999999行資料插入到big_table表。

CALL load_big_table_data(999999)

第三，要刪除big_table中所有資料，請使用以下語句：

TRUNCATE TABLE big_table;

如上所見，TRUNCATE TABLE語句的速度有多快。

現在我們已經學習了如何使用TRUNCATE TABLE語句來快速刪除大表中的所有資料，並理解TRUNCATE TABLE和DELETE語句之間的差異。