27 DELETE

DELETE FROM table 会删除行。和 UPDATE 一样，WHERE 子句决定你是“删除这些特定的行”，还是“清空整张表”。和 UPDATE 一样，DELETE 也是事务性的——ROLLBACK 可以撤销它。DELETE 可以筛选子集，并触发行级删除触发器。DELETE 和 TRUNCATE 都会遵守外键限制；TRUNCATE 则支持自己的语句级触发器。

本章要点

• 使用 DELETE FROM ... WHERE 删除行
• 为什么除了 DELETE 还需要 TRUNCATE
• 使用 DELETE ... USING 连接源表
• 使用 RETURNING 返回已删除的行
• 事务安全性、外键级联和触发器

示例

-- Demo on a temp table so we don't drain `fruits`
DROP TABLE IF EXISTS to_delete;
CREATE TABLE to_delete AS SELECT * FROM fruits;

-- Delete a single row
DELETE FROM to_delete WHERE id = 1;

-- Delete by membership
DELETE FROM to_delete WHERE name IN ('chestnut', 'damson');

-- Delete returning the removed rows
DELETE FROM to_delete WHERE id = 5 RETURNING id, name;

-- Delete using another table as a source (DELETE ... USING)
DROP TABLE IF EXISTS keep;
CREATE TABLE keep (id integer);
INSERT INTO keep SELECT id FROM to_delete LIMIT 1;

DELETE FROM to_delete AS d USING keep k WHERE d.id = k.id;

SELECT * FROM to_delete ORDER BY id;

-- Clean up
DROP TABLE to_delete;
DROP TABLE keep;

DELETE 会将行版本标记为已删除，并运行适用的触发器和外键检查。对记录日志的表执行修改会生成 WAL，用于崩溃恢复；多版本并发控制（MVCC）会保留旧版本，因为事务可能仍然需要它们。

TRUNCATE table 会移除所有行而不扫描它们，对于大表通常快得多。它是事务性的：ROLLBACK 会恢复数据。它会获取 ACCESS EXCLUSIVE 锁，执行外键限制检查，并触发 ON TRUNCATE 语句级触发器，而不会触发行级的 ON DELETE 触发器。对于较早的快照，它不具备 MVCC 安全性；请在练习数据或暂存数据上谨慎使用。

DELETE ... USING 是 UPDATE ... FROM 的对应形式：通过连接到 s，表达“删除 t 中与 s 中行匹配的行”。

运行示例

运行方式（需要前面课程创建的 fruits）：

$ psql -f source/delete.sql postgres
DROP TABLE
SELECT 8
DELETE 1
DELETE 2
 id |    name
----+------------
  5 | elderberry
(1 row)

DELETE 1
DROP TABLE
CREATE TABLE
INSERT 0 1
DELETE 1
 id  |   name
-----+-----------
   2 | blueberry
 102 | fruit_2
 103 | fruit_3
(3 rows)

DROP TABLE

常见陷阱

• 忘记 WHERE——与 UPDATE 一样需要警惕，而且后果更严重。始终先用 SELECT count(*) FROM t WHERE same_condition; 预览。
• 对带有 ON DELETE CASCADE 外键的表执行 DELETE，可能会沿着多个子表产生扇出。运行前务必审查。
• DELETE 不会回收磁盘空间——行会被标记为死亡行，稍后由 VACUUM 回收。大量删除后，运行 VACUUM (VERBOSE) t; 来回收空间，并运行 ANALYZE 刷新统计信息。
• 软删除（用 deleted_at 列代替真正的 DELETE）在应用设计中很常见。选择一种策略并坚持使用。

下载

SQL 文件：delete.sql · 全部示例文件 · 准备工作