SQL 如何实现逻辑删除 + 自动过滤 deleted=1 的视图封装

应使用带 WHERE deleted = 0 的视图封装逻辑删除，避免重复写条件导致漏过滤、难维护；视图需显式列字段、禁用 ORDER BY，配合 INSTEAD OF 触发器支持可更新操作，并在关联查询中按业务语义决定是否对被关联表也启用软删。

为什么不能直接在每个查询里加 WHERE deleted = 0

重复写条件易出错、漏过滤、难维护，尤其当表被多处 JOIN 或嵌套子查询引用时。一旦某处忘了加 deleted = 0，逻辑删除就形同虚设，数据一致性立刻崩塌。

核心矛盾在于：业务代码需要「透明访问未删除数据」，而底层存储必须保留 deleted 字段用于恢复和审计。视图是解耦这两层最轻量、最标准的方案。

创建带自动过滤的视图：关键写法与陷阱

以 postgresql 为例（mysql / SQL Server 语法类似，仅需微调）：

CREATE VIEW user_active AS select id, name, email, created_at, updated_at FROM users WHERE deleted = 0;

注意点：

• SELECT * 不推荐 —— 视图字段顺序/数量变更时，依赖它的查询可能静默失败或取错列
• 别在视图里用 ORDER BY —— 大多数数据库不允许，且排序应在应用层或最终查询中控制
• 如果原表有索引在 deleted 字段上，这个视图查询通常能命中（PostgreSQL 12+、MySQL 8.0+ 支持谓词下推），但需 EXPLAIN 验证
• SQL Server 用户注意：WITH SCHEMABINDING 可防止底层表结构被误改，但会限制后续 DDL，按需启用

如何让 INSERT/UPDATE/DELETE 走逻辑删除而不是物理删除

视图默认不可更新（尤其带 WHERE 的）。要支持 INSERT INTO user_active 或 DELETE FROM user_active，必须配可更新视图或 instead-of 触发器：

• PostgreSQL：用 CREATE RULE 或更推荐 INSTEAD OF 触发器（对视图定义）
• SQL Server：直接支持可更新视图，前提是满足[一系列限制](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql#updatable-views)，比如不能含聚合、DISTINCT、子查询等
• MySQL：不支持可更新视图的 DELETE/UPDATE 映射到逻辑删除，必须用存储过程或应用层拦截

一个典型 PostgreSQL 触发器示例（针对 DELETE FROM user_active）：

CREATE OR REPLACE FUNCTION soft_delete_user() RETURNS TRIGGER AS $$ BEGIN   UPDATE users SET deleted = 1, updated_at = NOW() WHERE id = OLD.id;   RETURN NULL; END; $$ LANGUAGE plpgsql;  CREATE TRIGGER trig_soft_delete_user   INSTEAD OF DELETE ON user_active   FOR EACH ROW EXECUTE FUNCTION soft_delete_user();

关联查询时视图怎么 JOIN 才不丢逻辑过滤

视图封装只解决单表过滤，一旦涉及 JOIN，比如 user_active JOIN order ON user_active.id = order.user_id，只要 order 表自己没删标识，就仍可能拉出已软删用户的订单 —— 这不是视图的问题，是业务语义问题。

此时必须明确：是否允许「已软删用户仍有活跃订单」？如果否，order 表也得加 deleted 字段，并建 order_active 视图；如果允许，则 JOIN 本身无害，但查询结果里需注意用户字段来自已过滤视图，订单字段未过滤。

容易忽略的一点：LEFT JOIN user_active u ON ... 时，若用户已软删，u.* 全为 NULL —— 这符合预期，但开发者常误以为是 JOIN 条件写错。