MySQL系列教程 --- 17、MySQL TRUNCATE 清空表

MySQL 中的 TRUNCATE 语句删除完整数据而不删除其结构。它是DDL 或数据定义语言 命令的一部分。通常，当我们想从表中删除整个数据而不删除表结构时，我们会使用此命令。

TRUNCATE 命令的工作方式与 DELETE 命令相同，但不使用从表中删除完整行的WHERE 子句。但是，与DELETE命令相比，TRUNCATE 命令更有效，因为它删除并重新创建表，而不是一次删除单个记录。由于此命令在内部删除表并重新创建它，因此受 truncate 语句影响的行数为零，这与返回已删除行数的 delete 语句不同。

该命令在执行期间不维护事务日志。它释放数据页而不是行，并为释放页而不是事务日志中的行创建一个条目。此命令还锁定页面而不是行；因此，它需要更少的锁和资源。

使用 TRUNCATE 命令时必须考虑以下几点：

• 我们不能在此命令中使用WHERE子句，因此无法过滤记录。
• 执行此命令后，我们无法回滚已删除的数据，因为执行此操作时未维护日志。
• 当表被外键引用或参与索引视图时，我们不能使用 truncate 语句。
• TRUNCATE 命令不会触发与被截断的表关联的DELETE触发器，因为它不对单个行进行操作。

句法

以下语法解释了从表中删除数据的 TRUNCATE 命令：

TRUNCATE [TABLE] table_name;  

在此语法中，首先，我们将指定要删除的数据的表名。语法中的 TABLE 关键字不是强制性的。但是使用它来区分TRUNCATE () 函数和TRUNCATE TABLE 语句是一个很好的做法。

MySQL 截断表示例

让我们通过一个例子来演示如何截断表格。首先，我们将使用以下语句创建一个名为“ customer ”的表：

CREATE TABLE customer (    
    Id int PRIMARY KEY NOT NULL,     
    Name varchar(45) NOT NULL,     
    Product varchar(45) DEFAULT NULL,     
    Country varchar(25) DEFAULT NULL,     
    Year int NOT NULL    
);    

接下来，我们将使用以下语句向该表添加值：

INSERT INTO customer ( Id, Name, Product, Country, Year)     
VALUES (1, 'Stephen', 'Computer', 'USA', 2015),     
(2, 'Joseph', 'Laptop', 'India', 2016),     
(3, 'John', 'TV', 'USA', 2016),    
(4, 'Donald', 'Laptop', 'England', 2015),    
(5, 'Joseph', 'Mobile', 'India', 2015),    
(6, 'Peter', 'Mouse', 'England', 2016);  

现在，通过执行SELECT 语句来验证表是否插入了记录：

mysql> SELECT * FROM customer;    

我们会得到输出，如下图：

现在，执行以下语句，使用上面讨论的 TRUNCATE 语法截断表 customer：

mysql> TRUNCATE TABLE customer;  

执行成功后，我们会得到如下输出：

如我们所见，即使删除了所有表记录，此查询也会返回0 行受到影响。我们可以通过再次执行 SELECT 语句来验证数据的删除。此命令提供以下输出，显示表中不存在任何记录：

如何使用外键截断表？

如果我们对使用外键约束的表执行 TRUNCATE 操作，我们将得到以下错误：

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails 

在这种情况下，我们需要在执行 TRUNCATE 语句之前登录MySQL服务器并禁用外键检查，如下所示：

SET FOREIGN_KEY_CHECKS=0;  

现在，我们可以截断表格了。执行后，重新启用外键检查，如下所示：

SET FOREIGN_KEY_CHECKS=1;  

如何清空MySQL中的所有表？

MySQL 中的 TRUNCATE 语句一次只会删除一张表。如果要删除多张表，需要单独执行TRUNCATE语句。下面的例子展示了如何在 MySQL 中截断多个表：

TRUNCATE TABLE table_name1;  
TRUNCATE TABLE table_name2;  
TRUNCATE TABLE table_name3;  

我们还可以使用以下 SQL 查询，使用我们数据库中的表名一次生成多个 TRUNCATE TABLE 命令：

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME)  
FROM INFORMATION_SCHEMA.TABLES  
WHERE table_schema = 'database_name';