如何通过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 放索引最左列（因它是范围过滤，IS NULL 算等值）
• 注意 MySQL 5.7+ 对 IS NULL 的索引使用是可靠的，但低版本需验证

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