SQL 如何实现“软删除”并用视图封装自动过滤 deleted=1

不能直接用delete语句删数据，因为物理删除会丢失审计痕迹、违反合规要求并破坏外键关联；应采用软删除，即通过deleted_at字段标记删除状态，并配合索引和视图自动过滤已删记录。

为什么不能直接用 DELETE 语句删数据

因为真实业务中，订单、用户、日志等记录一旦被物理删除，就无法追溯操作痕迹、影响审计合规，还可能破坏外键关联或统计口径。软删除本质是用字段标记状态，不是真删——最常见做法是加 deleted（tinyint/Boolean）或 deleted_at（datetime）字段。

建表时必须加软删除字段并设默认值

别等后期补，否则要改大量 sql 和应用逻辑。推荐用 deleted_at 而非 deleted，原因有三：能区分“未删”“已删”“删的时间”，便于恢复和分析；避免 WHERE deleted = 0 和 IS NULL 混用；兼容 mysql 5.7+、postgresql、SQL Server 的索引优化。

示例建表语句：

CREATE TABLE users (   id BIGINT PRIMARY KEY,   name VARCHAR(50),   deleted_at DATETIME NULL DEFAULT NULL,   INDEX idx_deleted_at (deleted_at) );

注意：INDEX idx_deleted_at (deleted_at) 很关键——后续视图和查询都依赖这个字段过滤，没索引会导致全表扫描。

用视图封装「自动过滤已删除」的查询逻辑

视图不是装饰，是统一入口。所有业务代码查 users 表，都应该改成查 v_users 视图，这样哪怕某天忘了写 WHERE deleted_at IS NULL，也不会漏掉过滤。

创建视图语句：

CREATE VIEW v_users AS SELECT id, name FROM users WHERE deleted_at IS NULL;

使用时直接：

SELECT * FROM v_users WHERE name LIKE '%张%';

而不是：

SELECT * FROM users WHERE name LIKE '%张%' AND deleted_at IS NULL;

⚠️ 注意点：

• MySQL 中视图默认是 undefined 算法，不影响性能；但若视图里含子查询或聚合，要考虑是否转为 MERGE 或物化（MySQL 不原生支持物化视图）
• PostgreSQL 可以用 CREATE OR REPLACE VIEW 随时更新定义，MySQL 8.0+ 也支持
• 别在视图里做 ORDER BY——排序应由最终查询决定，否则可能被忽略或引发错误

删除操作必须用 UPDATE 替代 DELETE

这是软删除落地最关键的一步。所有“删用户”接口，背后执行的必须是：

UPDATE users SET deleted_at = NOW() WHERE id = 123;

而不是：

DELETE FROM users WHERE id = 123;

容易踩的坑：

• ORM 框架（如 laravel Eloquent、Django ORM）通常自带软删除支持，但需显式启用，且要确认它生成的是 UPDATE 而非 DELETE
• 手写 DAO 层时，务必检查所有 deleteById 方法，替换为 softDeleteById，并统一收口
• 级联删除要重写逻辑：比如删用户时，其订单不能真删，而应同步设 deleted_at；否则外键虽在，数据语义已断

真正难的不是语法，是让整个团队在所有增删改查路径里，对 deleted_at 保持条件敏感——尤其报表、导出、定时任务这些边缘场景，最容易漏过滤。