SQL软删除机制实现详解：步骤与SQL语句范例

在数据库软删除设计中，强烈推荐默认采用 deleted_at 字段，它能够完整记录删除时间、支持按时间恢复操作以及审计归档，功能更加全面。如果业务场景仅需二态区分（已删/未删），且没有审计需求，那么使用 is_deleted 布尔字段配合索引也足以应付，但前提是你必须清楚自己丢失了什么——时间维度信息。

软删除字段该用 is_deleted 还是 deleted_at？

选择哪个字段，核心就看是否需要知道“什么时候删除的”。is_deleted（布尔型）确实查询逻辑简洁，但缺失了时间维度。而 deleted_at（datetime 类型）天然适配恢复逻辑、审计追踪、自动归档等场景，适用范围更广。这里要特别提醒：切勿使用 status 这类模糊字段来标记软删除——它容易被复用为“草稿/审核中/已发布”，反而让软删除的语义变得混乱，得不偿失。

建议起步就采用 deleted_at，默认值设为 NULL，非空即代表已软删除，语义清晰无歧义：

ALTER TABLE users ADD COLUMN deleted_at DATETIME NULL;

WHERE 条件里遗漏 deleted_at IS NULL 是最常见的事故

这一点再怎么强调也不为过。所有 SELECT、UPDATE、DELETE（非软删操作）——只要不是软删除本身——都必须显式过滤掉已软删除的记录。否则业务中就会读到“幽灵数据”，这类线上事故屡见不鲜。不能指望开发人员每次都自觉，必须靠机制兜底：

• 在应用层封装 DAO 方法，例如 findActiveUserById() 内部自动附加 AND deleted_at IS NULL，这样调用方就不会遗漏。
• 数据库视图可作为兜底方案，但要注意 ORM 可能不识别视图的更新操作：

  CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;

• 千万不要依赖“前端传参过滤”——参数容易被绕过，而且经常有人误写成 deleted_at = NULL（正确写法是 IS NULL），这是致命笔误。

UPDATE ... SET deleted_at = NOW() 是软删除唯一安全写法

别看它简单，很多人踩过坑。比如用 UPDATE ... SET is_deleted = 1 配合布尔字段——这样其实无法区分“从未删过”和“删了又手动改回 0”的异常状态，而且很难做幂等校验。一旦重复执行，数据状态就会混乱。

执行软删除时，推荐采用以下写法：

• 始终使用 UPDATE table SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL——后面的 AND deleted_at IS NULL 保证了幂等性，重复执行也不会产生副作用。
• 检查影响行数：如果返回 0，说明记录不存在或已被删除，这不是错误，而是预期行为，无需额外处理。
• 避免在事务中先 SELECT 再 UPDATE（容易产生竞态），直接使用带条件的单条 UPDATE 更安全。

索引必须覆盖 deleted_at 字段

没有索引的 deleted_at IS NULL 条件，在大表上就是全表扫描，性能会随着软删除记录比例升高而断崖式下跌。这一点务必在压测时验证。

建复合索引时，优先考虑高频查询模式：

• 单字段索引就够用：如果查询基本都是 WHERE deleted_at IS NULL AND user_id = ?，那就单独为 deleted_at 建一个索引，简单有效。
• 组合索引更优：如果常查 WHERE deleted_at IS NULL AND status = 'active' AND created_at > ?，把 deleted_at 放在索引最左列（因为 MySQL 中 IS NULL 被视为等值条件，不会影响后续索引的使用）。
• 注意 MySQL 5.7+ 对 IS NULL 的索引使用是可靠的，但低版本需要验证，存在风险。

真正麻烦的是历史表没有加索引，又不敢停机重建——这时软删除反而成了性能负债。所以上线前务必压测带 deleted_at 条件的真实查询场景，别等上了线才后悔。