SQL Truncate Table

作者：--    发布时间：2019-11-20

在本教程中，我们来学习如何使用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语句之间的差异。