MySQL truncate table語句

在本教學中，您將學習如何使用MySQL TRUNCATE TABLE語句刪除表中的所有資料。

MySQL TRUNCATE TABLE語句簡介

MySQL TRUNCATE TABLE語句允許您刪除表中的所有資料。因此，在功能方面，TRUNCATE TABLE語句就像沒有WHERE子句的DELETE語句。但是，在某些情況下，MySQL TRUNCATE TABLE語句比DELETE語句更有效。

MySQL TRUNCATE TABLE語句的語法如下：

TRUNCATE TABLE table_name;

在TRUNCATE TABLE子句後面指定要刪除所有資料的表名稱。

TABLE關鍵字是可選的。但是，應該使用它來區分TRUNCATE TABLE語句和TRUNCATE函式。

如果您使用InnoDB表，MySQL將在刪除資料之前檢查表中是否有可用的外來鍵約束。以下是一些情況：

如果表具有任何外來鍵約束，則TRUNCATE TABLE語句會逐個刪除行。如果外來鍵約束具有DELETE CASCADE動作，則子表中的相應行也將被刪除。
如果外來鍵約束沒有指定DELETE CASCADE動作，則TRUNCATE TABLE將逐個刪除行，並且遇到由子表中的行參照的行時，它將停止併行出錯誤。
如果表沒有任何外來鍵約束，則TRUNCATE TABLE語句將刪除該表並重新建立一個具有相同結構的新表，這比使用DELETE語句特別是對於大表更快更有效。

如果您使用其他儲存引擎，則TRUNCATE TABLE語句將刪除並重新建立一個新錶。

請注意，如果表具有AUTO_INCREMENT列，則TRUNCATE TABLE語句將自動遞增值重置為零。

此外，TRUNCATE TABLE語句不使用DELETE語句，因此與表關聯的DELETE觸發器將不被呼叫。

MySQL TRUNCATE TABLE範例

首先，建立一個名為books的新表：

CREATE DATABASE IF NOT EXISTS testdb;

USE testdb;

CREATE TABLE books(
 id int auto_increment primary key,
        title varchar(255) not null
)ENGINE=InnoDB;

接下來，使用以下儲存過程填充books表的資料：

DELIMITER $$

CREATE PROCEDURE load_book_data(IN num int(10))
BEGIN
 DECLARE counter int(10) default 0;
 DECLARE book_title varchar(255) default '';

 WHILE counter < num DO
   SET book_title = concat('Book title #',counter);
   SET counter = counter + 1;

   INSERT INTO books(title) VALUES(book_title);
 END WHILE;
END$$

DELIMITER ;
-- DROP PROCEDURE load_book_data;

然後，將10,000行資料插入到books表，執行上面語句將需要一段時間。

CALL load_book_data(10000);

執行上面語句之後，檢視books表中的資料：

SELECT * FROM books;

最後，使用TRUNCATE TABLE語句來與DELETE語句相比執行的速度。

select now() as start_time;
TRUNCATE TABLE books;
select now() as end_time;

上面語句執行結果，如下所示 -

mysql> select now() as start_time;
TRUNCATE TABLE books;
select now() as end_time;
+---------------------+
| start_time          |
+---------------------+
| 2017-07-24 21:31:07 |
+---------------------+
1 row in set

Query OK, 0 rows affected

+---------------------+
| end_time            |
+---------------------+
| 2017-07-24 21:31:08 |
+---------------------+
1 row in set

可以看到整個過程也就差不多 1 秒鐘完成，下面再次呼叫CALL load_book_data(10000);，完成插入資料後使用DELETE語句，檢視執行的時間 -

select now() as start_time;
DELETE FROM books;
select now() as end_time;

執行上面語句，得到以下結果 -

mysql> select now() as start_time;
DELETE FROM books;
select now() as end_time;
+---------------------+
| start_time          |
+---------------------+
| 2017-07-24 21:39:42 |
+---------------------+
1 row in set

Query OK, 10000 rows affected

+---------------------+
| end_time            |
+---------------------+
| 2017-07-24 21:39:44 |
+---------------------+
1 row in set

在本教學中，我們向您展示了如何使用MySQL TRUNCATE TABLE語句從表中有效地刪除所有資料，特別是對於擁有大量資料的表。