MySQL TRUNCATE TABLE 清空表

在本文中,我们介绍了在 MySQL 服务器上如何使用 TRUNCATE TABLE 语句清空表。

当我们需要清空一个表中的所有行时,除了使用 DELETE * FROM table 还可以使用 TRUNCATE TABLE 语句。

如果想要清空一个表, TRUNCATE TABLE 语句比 DELETE 语句更加有效。

TRUNCATE TABLE 语法

TRUNCATE TABLE 的语法很简单,如下:

TRUNCATE [TABLE] table_name;

其中 TABLE 关键字是可选的。也就是说, TRUNCATE t;TRUNCATE TABLE t; 是等效的。

TRUNCATE TABLE 语句相当于先将此表删除掉,再创建一个新表TRUNCATE TABLE 语句需要对操作的表具有 DROP 权限。

TRUNCATE 与 DELETE 不同

虽然 TRUNCATEDELETE 类似,但是他们在以下几个方面存在不同:

  • TRUNCATE 被归类为 DDL 语句,而 DELETE 被归类为 DML 语句。
  • TRUNCATE 操作无法被回滚,而 DELETE 可以被回滚。
  • TRUNCATE 操作删除和重建表,它的速度比 DELETE 快得多。
  • TRUNCATE 操作会重置表的自增值,而 DELETE 不会。
  • TRUNCATE 操作不会激活删除触发器,而 DELETE 会。
  • TRUNCATE 操作不返回代表删除行的数量的值,它通常返回 0 rows affectedDELETE 返回删除的行数。
  • 如果一个表被其他表的外键引用,对此表的 TRUNCATE 操作会失败。

TRUNCATE 实例

我们通过以下实例演示 TRUNCATE TABLE 的用法。

首先,我们使用下面的语句创建一个 test 表用于测试:

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  v INT
);

然后我们使用下面的 sql 语句插入 10 万条数据:

INSERT INTO test (v)
SELECT (
    t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i
  ) v
FROM (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t0,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t1,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t2,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t3,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t4
ORDER BY v;
Query OK, 100000 rows affected (1.24 sec)
Records: 100000  Duplicates: 0  Warnings: 0

通过以下 DELETE 语句删除所有表中的所有行:

DELETE FROM test;
Query OK, 100000 rows affected (1.01 sec)

这是 DELETE 语句的返回结果。它明确告诉我们删除了 100000 行,总共耗费 1.01 秒。

再使用上面的 INSERT 语句重新插入 100000 行后,通过以下 TRUNCATE TABLE 语句清空表:

TRUNCATE TABLE test;
Query OK, 0 rows affected (0.02 sec)

这是 TRUNCATE TABLE 语句的返回结果。它告诉我们总共耗费 0.02 秒。这里可以看出 TRUNCATEDELETE 快的多。

最后,我们用下面的语句插入一行数据:

INSERT INTO test (v) values (1);

然后查看一下表中的数据:

SELECT * FROM test;
+----+------+
| id | v    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

输出结果告诉我们,TRUNCATE 操作后,自增列 id 的值重置为 1。这与 DELETE 操作不同。

如果你需要在删除数据后仍然保持原有的自增值,请不要使用 TRUNCATE

结论

在本文中,我们介绍了在 MySQL 服务器上如何使用 TRUNCATE TABLE 语句清空表。本文的要点如下:

  • TRUNCATE TABLE 语句用于清空一个表。TABLE 关键字可以省略。
  • TRUNCATE TABLE 相当于删除并重建表,它不能回滚。
  • TRUNCATE TABLEDELETE 速度快的多,尤其是对大数据表来说。
  • TRUNCATE TABLE 会重置表的自增值。