SQL DELETE：如何安全地删除数据

在 SQL 中删除数据是一项具有潜在风险的操作。一条写得不当的命令就可能破坏表之间的关系，甚至清空整个数据集。

本教程将向您展示 DELETE 语句的工作原理、正确用法，以及如何避免常见错误，比如我刚刚提到的清空整个数据集之类的问题。 

如果您是 SQL 新手，可先从我们的 Introduction to SQL 课程开始；如果已有一定经验，可学习 Intermediate SQL 课程。 

DELETE 在 SQL 中的作用是什么？

在查看语法之前，先理解 DELETE 语句究竟做了什么很重要。以下是 SQL 中删除数据的不同方式和区别。

SQL DELETE 与“删除行”的概念性区别

DELETE 语句会从表中删除一行或多行，但保留表的结构。因此，表的模式（包括列名、数据类型、索引和约束）保持不变。删除后，表仍然存在，并可继续接收新数据。

可以把它想象成从账本上擦掉条目，而不是撕掉整页。模式保持不变，只是移除了选定的记录。

SQL DELETE vs. DROP vs. TRUNCATE

尽管 DELETE、DROP 和 TRUNCATE 都以某种形式移除数据，但它们的用途截然不同。下表总结了各语句的用法：

SQL DELETE 基本语法

既然已经理解了 DELETE 的工作原理，接下来看看它的语法以及如何应用。 

使用 WHERE 删除特定行

WHERE 子句定义了 DELETE 操作的作用范围。WHERE 子句始终是必需的，因为它明确告诉数据库要删除哪些行。

-- Basic DELETE syntax
DELETE FROM table_name
WHERE condition;技术支持

删除表中的所有行

如果在 DELETE 语句中省略 WHERE 子句，SQL 会默认您要针对表中的每一行。如果不小心这样做，数据库引擎会遍历并删除每一条记录。每删除一行，都会在事务日志中记录一次。如果有数百万行，这会非常缓慢并导致日志文件膨胀。

SQL DELETE 的安全实践

由于 DELETE 操作可能是永久性的，实施安全删除实践有助于您放慢节奏、核实意图，并在出现问题时/如果出现问题，规划如何恢复。

在删除前预览行

在运行 DELETE 语句之前，请始终先使用与计划在 DELETE 中相同的 WHERE 条件，执行一条 SELECT 语句预览将要删除的行。如果 SELECT 返回的行过多或不正确，您可以先修正条件，避免造成损失。 

例如，下面的查询会先检查在 2023-01-01 之前被取消的订单行，然后使用相同的筛选条件删除这些记录。

-- Step 1: Preview the targets
SELECT * FROM Orders 
WHERE Status = 'Cancelled' AND OrderDate < '2023-01-01';

-- Step 2: Once you've verified the list, convert to DELETE
DELETE FROM Orders 
WHERE Status = 'Cancelled' AND OrderDate < '2023-01-01';技术支持

使用事务与回滚

如果将 DELETE 操作放在事务中，就可以先执行命令、查看影响的行数，然后决定是使更改永久生效还是完全撤销。此时可以使用 ROLLBACK 将数据恢复到之前的状态。

下面的查询会暂时删除符合条件的行，然后将其恢复，让您在不丢失数据的情况下确认影响范围。

BEGIN TRANSACTION;

DELETE FROM Users
WHERE LastLogin < '2020-01-01';

-- Inspect the affected row count
ROLLBACK;技术支持

随后，您可以使用以下查询，永久删除最后登录时间早于 2020 年 1 月 1 日的用户。

BEGIN TRANSACTION;

DELETE FROM Users
WHERE LastLogin < '2020-01-01';

-- Confirm deletion of the required rows
COMMIT;技术支持

不过请注意，只有在尚未提交事务时，才能进行 ROLLBACK。

SQL DELETE 与连接、子查询

在真实世界的数据库中，数据很少是孤立存在的。多数情况下，您可能需要基于其他表中的值、过期账户、孤立记录或不再适用的关联实体来删除行。

基于另一张表删除行

使用子查询是基于另一张表删除行的最具可移植性且被广泛支持的方法。它几乎适用于所有 SQL 版本。比如，下面的查询会从 Users 表中删除那些在 Accounts 表中账号已被停用的不活跃用户。

-- Delete users whose accounts have been deactivated
DELETE FROM Users
WHERE AccountId IN (
    SELECT AccountId
    FROM Accounts
    WHERE Status = 'DEACTIVATED'
);技术支持

您也可以用 Join 来删除数据库中的行，但其语法因数据库而异，我们将在下一节看到差异。

特定数据库的 DELETE 语法

虽然 SQL 中 DELETE 的思想相同，但在使用 JOIN 基于另一张表删除某表行时，不同数据库的语法不同。

SQL Server 将目标表放在 DELETE 之后，然后在 FROM 中进行连接。下面的示例中，DELETE 后的别名（u）指定了要删除的是哪张表。

-- SQL Server: Delete users linked to deactivated accounts
DELETE u
FROM Users u
JOIN Accounts a
  ON u.AccountId = a.AccountId
WHERE a.Status = 'DEACTIVATED';技术支持

在 PostgreSQL 中，USING 的作用类似于 JOIN。只会删除目标表（Users）中的行。

-- PostgreSQL: Delete users linked to deactivated accounts
DELETE FROM Users
USING Accounts
WHERE Users.AccountId = Accounts.AccountId
  AND Accounts.Status = 'DEACTIVATED';技术支持

MySQL 支持多表删除，但需要显式指明表名。必须将要删除的表放在 FROM 子句之前。

-- MySQL: Delete users linked to deactivated accounts
DELETE u
FROM Users u
JOIN Accounts a
  ON u.AccountId = a.AccountId
WHERE a.Status = 'DEACTIVATED';技术支持

我建议学习我们的 Joining Data in SQL 课程，了解 SQL 中不同类型的连接，以及如何处理数据库中的多张关联表。

SQL DELETE 与参照完整性

如我们所知，在关系型数据库中，表很少是孤立存在的。它们通常通过外键相互关联。由于这些关联，在一张表中删除一行可能会带来意想不到的后果。

外键与约束错误

当一张表被外键引用时，数据库会对您尝试删除父行的行为施加规则。默认情况下，如果子表中仍存在相关行，大多数数据库会阻止删除。

例如，如果有一张引用 Customers 表的 Orders 表，那么在该客户仍有订单的情况下删除该客户，将因约束错误而失败。这可以防止数据库出现不再具有有效父级的孤立记录。

级联删除

如果希望自动删除相关数据，需要显式定义级联规则，指示数据库如何处理相关数据。ON DELETE CASCADE 告诉数据库在删除父行时自动删除相关的子行。

例如，下面的查询指示数据库：当 Customers 表中的某位客户被删除时，自动删除 Orders 表中引用该客户的所有行。

-- Parent table
CREATE TABLE Customers (
    CustomerId INT PRIMARY KEY,
    Name VARCHAR(100)
);

-- Child table with a cascading foreign key
CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    CustomerId INT,
    OrderDate DATE,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId)
        ON DELETE CASCADE
);技术支持

这种做法的优点是无需多次手动执行 DELETE 就能保持数据库整洁。但应谨慎使用，因为一次删除可能在多张表之间触发连锁反应，删除远超预期的数据。例如，您可能在 Departments 表中删除了一行，却意外清空了 Employees 表中的 500 行。

删除数据时的性能考量

在使用 DELETE 语句时，您会注意到删除少量行几乎是瞬时的，但当有数百万行时会很慢。请考虑以下最佳实践以安全删除记录：

针对大表选择 DELETE 还是 TRUNCATE

如前所述，DELETE 和 TRUNCATE 都会删除记录，但方式不同：

• DELETE：逐行检查是否符合条件，删除并将更改记录到事务日志。对于超大数据集，这会占用大量资源且较慢。它允许使用 WHERE 子句进行筛选，并支持回滚。

• TRUNCATE：一次性删除所有行，日志记录最少。速度显著更快，但无法筛选，通常不可回滚，并会受到外键约束的限制。

分批删除与长时间运行的操作

如果在一条语句中删除数百万行，可能会显著拖慢整个应用，甚至在处理不当时导致数据库崩溃。为降低风险，删除大数据集时应始终采用分批方式。

分批删除每次只移除有限数量的行，例如每次几千行。这样数据库能保持响应性，也更易于监控进度。一旦出现问题，您可以停止进程，而无需回滚一个庞大的事务。

SQL DELETE 常见错误

以下是在使用 DELETE 语句删除表中行时需要警惕的常见错误：

忘记 WHERE 子句

在使用 DELETE 时，最常见的错误就是忘记 WHERE 子句。没有它，语句会删除表中的每一行。务必使用 WHERE 指定要删除的行。作为预防措施，请养成先 SELECT 再 DELETE 的习惯，先查看将被删除的行。

以为 DELETE 可逆

另一个常见误区是认为删除的数据总能恢复。请注意，默认情况下，一旦提交，DELETE 会将数据标记为从磁盘中物理移除，除非您有备份、只读副本或审计日志。

只有在以下情况下，您才能“撤销”一条 DELETE 命令：

• 它在一个事务中执行。
• 事务尚未提交。
• 数据库和存储引擎支持回滚。

生产环境中的 SQL DELETE

在生产系统中，DELETE 的重点更多在于控制、可见性和问责，而非语法本身。在此类环境中，目标不仅是删除数据，更要安全、审慎地删除。

访问控制与权限

在专业环境中，删除数据的能力应遵循最小权限原则。并非每个用户或应用服务账号都应拥有 DELETE 权限。 

大多数“只读”用户或报表工具都应被明确拒绝此权限。多数公司通常要求进行“同侪审查”，即在生产环境执行手动删除脚本之前，需由第二人核验其 WHERE 子句。

如果您经常在工作中设计或维护数据库，我建议尝试 SQL Server for Database Administrators 技能路径，若您经常从事相关工作。 

审计与软删除

许多生产系统会避免硬删除，因为其风险较高。相反，它们采用软删除，即将一行标记为已删除。例如，使用 deleted_at 时间戳或 is_deleted 标志，而不是物理移除记录。

软删除便于恢复、支持审计，并为调试与合规保留历史数据。

结语

最重要的一点是：始终明确编写 WHERE 子句，先用 SELECT 预览受影响的行，并在可能时使用事务。在生产系统中，每一次删除都应当是审慎且可审计的。以对待模式变更或部署的同等谨慎态度对待 DELETE，它就会是有用的工具，而非代价高昂的错误。

我建议学习Database Design 课程，在那里您将学习如何创建和管理数据库，并为您的需求选择合适的 DBMS。我还建议尝试我们的 Associate Data Engineer in SQL 职业路径，学习数据工程和数据仓库的基础知识。