11.11 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语句从表中有效地删除所有数据，特别是对于拥有大量数据的表。