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語句從表中有效地刪除所有數據，特別是對於擁有大量數據的表。