SQL如何使用软删除替代物理DELETE

应使用deleted_at时间戳字段（TIMESTAMP或DATETIME类型），默认为NULL；NULL表示未删除，非NULL表示已软删除，避免用is_deleted布尔字段以防语义模糊和非法赋值。

软删除字段该用什么类型和默认值

直接加 is_deleted 布尔字段最常见，但容易踩坑：PostgreSQL 的 boolean 默认不支持 NULL，而刚插入的记录理应是“未删除”状态；MySQL 的 TINYINT(1) 虽常被当布尔用，但语义模糊，且 ORM 映射易出错。更稳妥的是用 deleted_at 时间戳字段（TIMESTAMP 或 DATETIME），默认为 NULL，非空即表示已软删除。

这样做的好处是：能记录删除时间、支持按时间范围查询回收记录、与 Lara vel/Ecto/Django 等主流框架的软删除约定一致，也方便后续做自动归档策略。

• deleted_at 必须允许 NULL，建表时显式写 deleted_at TIMESTAMP NULL DEFAULT NULL
• 避免用 is_deleted TINYINT —— 它在 MySQL 8.0+ 中虽支持 BOOLEAN 别名，但底层仍是整数，容易被误赋值为 2、-1 等非法值
• 如果业务明确只要“删/没删”二态，且不用查删除时间，可用 is_deleted BOOLEAN DEFAULT FALSE，但需在所有 INSERT 语句中显式指定该字段，或设触发器兜底

WHERE 条件里怎么安全过滤已删除数据

每次查询主表时漏掉 deleted_at IS NULL 就会把软删除数据查出来，这是线上事故高发点。不能靠开发自觉加条件，得从机制上约束。

在此，推荐的做法是进行视图封装，或者运用数据库行级安全策略（RLS）。就RLS而言，PostgreSQL 10+ 支持通过CREATE POLICY自动拦截对已删除行的SELECT/UPDATE/INSERT操作。而MySQL 8.0+ 虽然没有原生的RLS，但可以借助CHECK CONSTRAINT配合生成列来模拟实现，不过这种方式相较于RLS来说，不够直接。倘若暂时无法进行升级或者使用RLS，那么至少要将常用查询封装成视图：

CREATE VIEW user_active AS
SELECT id, name, email, created_at
FROM users
WHERE deleted_at IS NULL;

• 绝不依赖应用层拼 SQL 时手动加 AND deleted_at IS NULL —— 某次忘记加，敏感数据就暴露了
• ORM 如 Django 的 QuerySet 或 SQLAlchemy 的 default_filter 可设全局软删除钩子，但需确认团队所有开发者都启用且没绕过
• 对历史遗留表加软删除时，先跑一遍 UPDATE users SET deleted_at = NOW() WHERE id IN (SELECT id FROM users_to_archive)，再验证视图结果是否符合预期

UPDATE 和 DELETE 语句怎么改写才不破坏软删除语义

原本的 DELETE FROM users WHERE id = 123 得改成 UPDATE users SET deleted_at = NOW() WHERE id = 123 AND deleted_at IS NULL。重点就在末尾的 AND deleted_at IS NULL——这么做既能防止重复软删除覆盖掉原来的时间，又能避免误更新已经删除的记录。

同理，带条件的 UPDATE（比如重置密码）也得加这个守卫，否则可能意外“复活”已删除用户：

UPDATE users 
SET password_hash = 'xxx', updated_at = NOW()
WHERE id = 123 AND deleted_at IS NULL;

• 所有写操作（UPDATE/DELETE）必须检查 deleted_at IS NULL，否则逻辑一致性崩塌
• 不要用 REPLACE INTO 或 INSERT ... ON DUPLICATE KEY UPDATE 替代软删除 —— 它们会绕过 deleted_at 字段逻辑，造成数据状态错乱
• 外键关联表（如 user_profiles）也要同步加 deleted_at，并用触发器或应用层保证主表软删除时，关联数据也一并标记，否则出现“已删用户仍有活跃档案”

索引和性能要注意哪些隐性开销

加了 deleted_at 后，原本高效的 WHERE id = ? 查询可能变慢 —— 因为优化器发现大量数据已删除，索引选择率下降，尤其当软删除比例超过 20% 时，全表扫描概率上升。

解决方法不是不加索引，而是建组合索引覆盖高频查询路径。例如用户登录查 email，就得建 (email, deleted_at) 索引，让 WHERE email = ? AND deleted_at IS NULL 走索引查找。

• 单列 deleted_at 索引意义不大 —— 值分布极度倾斜（绝大多数是 NULL），B-tree 效率低
• 对含 deleted_at IS NULL 的查询，优先建 (filter_col, deleted_at) 组合索引，而非单独为 deleted_at 建索引
• 定期执行 VACUUM（PostgreSQL）或 OPTIMIZE TABLE（MySQL）清理 MVCC 死元组，否则软删除数据堆积会拖慢整个表的读写