测试人员想要在新的测试运行之前清除暂存表。应该是DELETE、TRUNCATE还是DROP？错误的选择可能意味着工作缓慢、缺少桌子，或者在生产中意味着一个非常糟糕的下午。

我们在 SQLite 3.50 中运行了下面的 DELETE 和 DROP 示例，输出来自该运行。 SQLite 没有 TRUNCATE 命令，因此 TRUNCATE 注释基于 PostgreSQL、MySQL 和 SQL Server 官方文档，而不是我们自己的测试。

简短的回答

• 需要删除一些行？使用DELETE ... WHERE。
• 需要清空表但保留它吗？在支持的情况下使用 TRUNCATE。
• 桌子需要永久消失吗？使用DROP TABLE。

删除：删除行

DELETE 是数据更改 (DML) 命令。它一次删除一行，触发行级触发器并且可以进行过滤。我们设置了一个带有审计触发器的小表：

CREATE TABLE logs (id INTEGER PRIMARY KEY AUTOINCREMENT, level TEXT);
INSERT INTO logs (level) VALUES ('INFO'),('ERROR'),('INFO'),('DEBUG');

CREATE TABLE audit (msg TEXT);
CREATE TRIGGER logs_delete AFTER DELETE ON logs
BEGIN INSERT INTO audit VALUES ('deleted ' || OLD.id); END;

DELETE FROM logs WHERE level = 'DEBUG';

SELECT id, level FROM logs;  -- (1,'INFO'), (2,'ERROR'), (3,'INFO')
SELECT * FROM audit;         -- 'deleted 4'

只有匹配的行出现，并且触发器记录了它。运行不带 WHERE 子句的 DELETE FROM logs 删除了其他三行，然后审计表保存了四个条目，每个删除的行一个。

DELETE 不会重置自动递增计数器。清空表后，我们的下一个插入收到 id 5，而不是 1。在 SQLite 中，这是因为 AUTOINCREMENT。 MySQL InnoDB 和 SQL Server 标识列也会在删除后从原来的位置继续。

TRUNCATE：清空表

TRUNCATE TABLE logs; 一次删除每一行。它不能采用 WHERE 子句。数据库通常不会逐行删除，而是释放表的数据页，这就是它在大型表上速度更快的原因。

当我们在 SQLite 中运行它时，我们收到语法错误，因为 SQLite 没有命令：

TRUNCATE TABLE logs;
-- near "TRUNCATE": syntax error

在 SQLite 中，使用 DELETE FROM logs; 且不带 WHERE 子句。 SQLite 的文档描述了一种“截断优化”，当表没有触发器时，它可以加快速度。我们的表有一个触发器，因此删除是逐行运行的，并为每一行触发它。

在支持 TRUNCATE 的数据库中，通过其文档：

• 它通常不会触发行级 DELETE 触发器。
• 它通常会重置身份或自动增量计数器，例如在 MySQL 和 SQL Server 中。在 PostgreSQL 中，您添加 RESTART IDENTITY。
• 在大多数系统中它需要比 DELETE 更强的权限。

DROP：删除表本身

DROP TABLE 是一个架构 (DDL) 命令。它删除数据、表定义、索引、触发器和约束。引用该表的任何内容（例如视图或外键）也会受到影响。

DROP TABLE logs;
SELECT * FROM logs;
-- no such table: logs

DROP TABLE IF EXISTS logs;  -- no error, even though it is gone

IF EXISTS 在可能运行多次的安装脚本中很方便。正如我们在 SQL 视图解释 中所示，已删除表上的视图可能会损坏。

对照表

可以回滚吗？

这是很多文章过于简单化的地方。诚实的答案是这取决于数据库。

DELETE 可以在任何地方的事务内回滚。我们测试了它：

BEGIN;
DELETE FROM logs;
SELECT COUNT(*) FROM logs;  -- 0
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 3

DROP也在SQLite中回滚，它支持事务性DDL：

BEGIN;
DROP TABLE logs;
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 1 (the table is back)

对于其他数据库中的 TRUNCATE 和 DROP，根据其文档：

所以“TRUNCATE永远不能回滚”对于PostgreSQL和SQL Server来说是错误的，而“它总是可以回滚”对于MySQL来说是错误的。在事务之外，如果没有备份，它们都无法撤消。

为什么 TRUNCATE 很快

DELETE 记录每个删除的行，以便可以撤消和复制更改、触发触发器并逐行检查约束。在具有数百万行的表上，这会产生大量工作和日志量。 TRUNCATE 通常会在一次操作中取消分配存储，而日志记录要少得多。要删除较大的子集而不是所有内容，请分批删除，以避免长时间锁定。我们在 SQL 查询优化 中介绍了锁定和查询成本。

外键和权限

• 外键。 SQL Server 拒绝 TRUNCATE 外键引用的表。 PostgreSQL 也要求您截断引用表，或者使用 CASCADE。相反，DELETE 根据约束检查每一行。
• 权限。 DELETE需要删除权限。 TRUNCATE 通常需要 ALTER 或专用的 TRUNCATE 权限，而 DROP 需要模式的所有权或 ALTER。
• 复制和审计。如果其他系统依赖于行级更改日志或触发器，则 TRUNCATE 可能会跳过它们。首先与拥有这些系统的人核实。

使用哪一个

对于测试数据，TRUNCATE 很受欢迎，因为它会重置 ID，从而使测试可重复。将每个测试包装在回滚的事务中是另一种选择，SQL 事务和 ACID 中对此进行了解释。

安全检查表

1. 确认您连接到哪个数据库和环境。
2. 首先将 WHERE 子句作为 SELECT 运行。
3. 将更改包装在数据库支持的事务中。
4. 在生产中 TRUNCATE 或 DROP 之前进行最近的、经过测试的备份。
5. 将 DROP 语句放在经过审查的迁移脚本中，而不是放在临时控制台中。
6. 在删除表之前检查依赖于表的内容，例如视图、外键和报告。

安全删除大量数据

使用一次 DELETE 删除数百万行可能会长时间锁定表、填满事务日志并减慢复制速度。常见的模式是小批量删除并重复，直到什么都不剩：

-- SQL Server style (not run for this article)
DELETE TOP (5000) FROM logs WHERE created_at < '2026-01-01';

-- PostgreSQL style (not run for this article)
DELETE FROM logs
WHERE id IN (SELECT id FROM logs WHERE created_at < '2026-01-01' LIMIT 5000);

循环运行该语句，在批次之间短暂暂停，直到报告零行。每个批次都是一个小事务，因此锁很短并且其他查询继续运行。过滤器列上的索引（此处 created_at）可保持每个批次的速度。

对于按日期增长的数据（例如日志和事件），表分区 甚至更好。每个月都位于自己的分区中，删除旧数据成为一种快速操作，删除或截断一个分区，而不是逐行删除。

软删除

许多应用程序从不实际删除业务记录。他们添加一列（例如 deleted_at）并进行设置，然后查询 deleted_at IS NULL 进行过滤。软删除使恢复变得容易并保留审计历史记录，但每个查询都必须记住过滤器，这对于视图来说是一项很好的工作。唯一约束也需要小心，因为软删除的行仍然保留其值。隐私法可能要求在一段时间后真正删除个人数据，因此软删除不能替代保留策略。

最后的话

DELETE 是精确、仔细的工具。 TRUNCATE 是快速重置按钮，DROP 会完全删除表。最大的风险是假设一个数据库的行为与另一个数据库相似，尤其是在回滚方面。如有疑问，请首先在您自己的数据库中的表副本上测试该命令。

常见问题

继续学习