SQL TRUNCATE 和 DROP TABLE

第 9.3 课 · 阅读时间：约 6 分钟 · 练习题 ↓

SQL TRUNCATE 删除表中的所有行，但保留表本身；DROP TABLE 则删除表本身，连同其中的数据和结构。在本课中，你将学习这两条语句如何工作、它们在速度、回滚和自增行为上与 DELETE 有何不同，以及外键如何影响它们。

删除数据可以分三个层次：删除部分行（DELETE ... WHERE）、删除所有行（TRUNCATE）或删除整张表（DROP TABLE）。第一种已在第 8.3 课中介绍。TRUNCATE 和 DROP TABLE 属于 DDL 语句：它们把表作为一个整体来操作，速度快得多，但也更不容错。

SQLtest 上的示例数据库是只读的；示例会创建自己的表，因此你可以在练习场中选择不含示例数据的数据库来运行它们。

TRUNCATE：清空表

TRUNCATE TABLE table_name;

执行 TRUNCATE 之后：

• 所有行都被删除；
• 表、它的列、索引和约束都保留；
• 自增计数器重新从 1 开始。

CREATE TABLE daily_import (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payload VARCHAR(100)
);
INSERT INTO daily_import (payload) VALUES ('a'), ('b'), ('c');

TRUNCATE TABLE daily_import;

INSERT INTO daily_import (payload) VALUES ('d');
SELECT * FROM daily_import;

结果：一行，id = 1，payload = 'd'。表被清空，id 计数器也被重置。

DROP TABLE：删除表

DROP TABLE table_name;

执行 DROP TABLE 之后，表就不存在了：它的数据、列、索引和约束全部消失，任何使用这张表的查询都会报错，直到重新创建它为止。

DROP TABLE IF EXISTS old_reports;

当表不存在时，IF EXISTS 会跳过该语句而不报错，这在会重建表的脚本中很有用。

TRUNCATE、DELETE 与 DROP 对比

回滚这一行最重要。在 MySQL 和 MariaDB 中，DDL 语句会自动提交当前事务，因此误执行的 TRUNCATE 或 DROP TABLE 无法用 ROLLBACK 撤销，只能依靠备份。

外键

外键保护那些被其他表引用的表：

• 如果另一张表有指向它的外键，DROP TABLE 会失败。请先删除子表，或者删除该约束。
• 在 MySQL 中，对被外键引用的表执行 TRUNCATE 会失败，即使子表是空的。这种情况下 DELETE FROM 可以执行，因为它检查的是实际存在的行。

CREATE TABLE departments (department_id INT PRIMARY KEY);
CREATE TABLE staff (
    staff_id INT PRIMARY KEY,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments (department_id)
);

-- 错误：Cannot truncate a table referenced in a foreign key constraint
TRUNCATE TABLE departments;

该用哪一个

• 删除部分行 → DELETE ... WHERE。
• 清空一张还要重新填充的表，例如用于导入的中间表 → TRUNCATE。
• 表不再需要 → DROP TABLE。

在真实数据库上执行 TRUNCATE 或 DROP 之前，请检查表名以及当前连接的数据库：这两条语句会立即生效，而且在 MySQL 中无法撤回。

要点：

• TRUNCATE TABLE 快速删除所有行并保留表；自增计数器会被重置。
• DROP TABLE 删除表及其数据和结构；IF EXISTS 可以避免报错。
• DELETE 可以只删除部分行，并且可以回滚；TRUNCATE 不能使用 WHERE。
• 在 MySQL 和 MariaDB 中，TRUNCATE 和 DROP 会隐式提交，无法回滚。
• 外键会阻止删除或清空被引用的表。

常见问题

TRUNCATE 和 DELETE 有什么区别？

DELETE 逐行删除，可以使用 WHERE，会触发删除触发器，并且可以回滚。TRUNCATE 一次删除所有行，速度快得多，会重置自增计数器，并且在 MySQL 中无法回滚。

TRUNCATE 和 DROP TABLE 有什么区别？

TRUNCATE 清空表但保留它，可以继续插入新行。DROP TABLE 彻底删除表；要再次使用，必须用 CREATE TABLE 重新创建。

TRUNCATE 可以回滚吗？

在 MySQL 和 MariaDB 中不可以：它会导致隐式提交。在 PostgreSQL 和 SQL Server 中，如果在事务内执行，则可以回滚。