SQL DELETE：从表中删除行

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

SQL DELETE 语句从表中删除行。在本课中，你将学习 DELETE ... WHERE 语法、如何按另一张表的条件删除行、为什么外键有时会阻止删除、如何在执行前检查 DELETE，以及它与 TRUNCATE 的区别。

DELETE 是三条数据修改语句中的最后一条。它写起来最简单，也最危险：漏掉一个条件就会删除表中的所有行。本课的规则就是为了让你只删除真正想删除的行。

SQLtest 上的示例数据库是只读的，因此在那里执行这些语句会返回权限错误。示例使用 Sakila 模式。

DELETE 语法

DELETE FROM table_name
WHERE condition;

• DELETE FROM 指定表。
• WHERE 选择要删除的行。没有 WHERE 时，所有行都会被删除。
• 只删除行：表本身、它的列和索引都会保留。

按条件删除行

DELETE FROM payment
WHERE payment_date < '2005-05-25';

结果：删除 2005 年 5 月 25 日之前的所有付款。

按主键删除只会删除一行：

DELETE FROM payment
WHERE payment_id = 16049;

按另一张表的条件删除

WHERE 中的子查询可以根据其他表中的数据选择行。删除非活跃客户的付款：

DELETE FROM payment
WHERE customer_id IN (
    SELECT customer_id
    FROM customer
    WHERE active = 0
);

结果：删除 15 位非活跃客户的所有付款。

与 UPDATE 一样，MySQL 和 MariaDB 不允许子查询读取正在删除数据的那张表。MySQL 还支持多表形式：DELETE p FROM payment AS p INNER JOIN customer AS c ON c.customer_id = p.customer_id WHERE c.active = 0;。

外键可能阻止 DELETE

其他表引用了客户：rental 和 payment 中存有 customer_id。在 Sakila 中，这些外键定义为 ON DELETE RESTRICT，因此数据库会拒绝删除仍有租赁或付款记录的客户：

DELETE FROM customer
WHERE customer_id = 1;

结果：报错“Cannot delete or update a parent row: a foreign key constraint fails”。该客户保留。

这样可以保护数据：否则租赁记录就会指向一个已经不存在的客户。要删除这样的客户，需要先删除或重新分配依赖的行，或者把外键定义为 ON DELETE CASCADE，让数据库自动删除它们。你将在第 9.1 课中结合 CREATE TABLE 学习外键选项。

删除前先检查

1. 用相同的 WHERE 执行 SELECT，查看这些行。
2. 执行 DELETE，并比较受影响的行数。

-- 1. 哪些行会被删除？
SELECT payment_id, customer_id, amount, payment_date
FROM payment
WHERE payment_date < '2005-05-25';

-- 2. 只删除这些行
DELETE FROM payment
WHERE payment_date < '2005-05-25';

在生产环境中，请在事务中执行重要的删除操作，并确保有备份。许多系统根本不删除业务数据：它们把行标记为已删除（active = 0、deleted_at），这称为软删除（soft delete）。

不带 WHERE 的 DELETE 与 TRUNCATE

DELETE FROM table_name; 删除所有行，但保留表。TRUNCATE TABLE table_name; 做同样的事情要快得多，但工作方式不同：

你将在第 9.3 课中学习 TRUNCATE 和 DROP TABLE。

要点：

• DELETE FROM table WHERE condition 删除匹配的行；表本身保留。
• 没有 WHERE 时，所有行都会被删除。
• WHERE 中的子查询可以按其他表的条件删除。
• 带 RESTRICT 的外键会阻止删除仍被引用的行。
• 先用 SELECT 检查；重要的删除操作要使用事务和备份。
• 要快速清空整张表，请使用 TRUNCATE。

常见问题

如果执行不带 WHERE 的 DELETE 会怎样？

表中的所有行都会被删除，留下一张空表。除非这条语句是在可以回滚的事务中执行的，否则数据只能从备份中恢复。

为什么我在 SQL 中删不掉一行？

通常是因为外键：另一张表仍然引用着这一行。请先删除或修改依赖的行，或者把外键定义为 ON DELETE CASCADE。

DELETE 和 TRUNCATE 有什么区别？

DELETE 逐行删除，并且可以使用 WHERE；TRUNCATE 一次删除所有行，速度快得多，还会重置自增计数器。