SQL实现数据软删除机制

推荐默认使用 deleted_at 字段实现软删除机制，它支持记录删除时间、按时间恢复以及审计归档，功能更完整。如果业务场景只需要二态区分（已删/未删），且没有审计需求，那用 is_deleted 布尔字段配合索引也够用，但前提是你清楚自己丢掉了什么——时间信息。软删除字段的选择直接影响后续数据恢复和查询效率，因此在设计数据库时需慎重考虑。

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

选哪个，核心就看你需不需要知道“什么时候删的”。is_deleted（布尔型）确实够用，查询逻辑也简单，但它丢失了时间维度。而 deleted_at（datetime 类型）天然支持恢复逻辑、审计追踪、自动归档等场景，适用范围更广。这里要特别提醒一句：别用 status 这种模糊字段来做软删除标记——它容易被复用成“草稿/审核中/已发布”，反倒是让软删除的语义变得混乱，得不偿失。使用 deleted_at 作为软删除字段，能更清晰地表示删除状态，同时保留时间戳信息。

建议起步就用 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），这是致命笔误。软删除查询中 IS NULL 的写法必须严格执行。

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

别看它简单，不少人踩过坑。比如用 UPDATE ... SET is_deleted = 1 配合布尔字段——这其实无法区分“从未删过”和“删了又手动改回 0”的异常状态，而且很难做幂等校验。一旦重复执行，数据状态就乱了。软删除操作需要保证幂等性和可追溯性，使用 deleted_at 结合时间戳是更稳妥的做法。

执行软删除时，推荐这样写：

• 始终用 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 放在索引最左列（因为 IS NULL 在 MySQL 中被视为等值条件，不会影响后续索引的使用）。
• 注意 MySQL 5.7+ 对 IS NULL 的索引使用是可靠的，但低版本需要验证，存在风险。

真正麻烦的是历史表没加索引，又不敢停机重建——这时候软删除反而成了性能负债。所以上线前务必压测带 deleted_at 条件的真实查询场景，别等上了线才后悔。合理利用索引能显著提升软删除相关操作的响应速度，避免数据库性能瓶颈。