如何在SQL中优雅实现软删除并在查询时自动过滤?

千静吖_3396

千静吖_3396

2026-11-16

761人浏览

原创

优先选 deleted_at(DATETIME 或 TIMESTAMP 类型),因其能记录删除时间、支持按时间恢复与自动清理,避免 is_deleted 布尔字段无法追溯“何时删”“删几次”的缺陷;必须设为 NULL 默认、加索引,并通过视图或 ORM 自动过滤 deleted_at IS NULL。

如何在sql中优雅实现软删除并在查询时自动过滤?

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

优先选 deleted_at(DATETIME 或 TIMESTAMP 类型),不是因为“高大上”,而是它能保留删除时间、支持恢复逻辑、兼容范围查询。用 is_deleted TINYINT(1) 看似简单,但一旦需要查“昨天删了哪些用户”或“删了超过30天的记录可物理清理”,就得加额外字段或改表结构。

常见错误:设成 is_deleted DEFAULT 0,但忘记在所有 INSERT 语句里显式写 is_deleted = 0,导致新插入记录默认为 NULL,后续 WHERE is_deleted = 0 就漏数据。

实操建议:

  • deleted_at 设为 NULL 表示未删除,非 NULL 表示已软删除
  • 建索引:CREATE INDEX idx_deleted_at ON users(deleted_at);,否则带 WHERE deleted_at IS NULL 的查询可能全表扫
  • 避免用 is_deleted = 0 这类布尔判断——它无法利用 NULL 索引优化,且语义不如 IS NULL 清晰

如何让应用层查询自动过滤已删除记录?

不能靠每个开发手写 WHERE deleted_at IS NULL,容易遗漏。主流方案是数据库视图 + 应用层封装双保险。

视图做法(MySQL/PostgreSQL 都支持):

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

这样业务代码查 SELECT * FROM users_active 就天然安全。但注意:视图不阻止对基表 users 的直接写入,所以仍需在应用层拦截。

应用层关键点:

  • ORM 如 Laravel Eloquent 用 SoftDeletes trait,会自动在所有 SELECT 加 WHERE deleted_at IS NULL,但你要确认 restore() 和 withTrashed() 是否真被用到了对应场景
  • 原生 SQL 查询必须走统一 DAO 方法,例如 findUserById(id) 内部拼 WHERE deleted_at IS NULL,禁止裸写 SELECT * FROM users WHERE id = ?
  • 聚合查询(如 COUNT(*))最容易漏过滤——别只查活跃数却忘了加条件

硬删除前为什么必须检查外键关联?

软删除只是标记,不影响外键约束;但真要物理删除时,如果 orders 表有 user_id 外键且没设 ON DELETE CASCADE,就会报错:Cannot delete or update a parent row: a foreign key constraint fails。

更隐蔽的问题是:软删除后,关联表(比如 user_profiles)可能没同步处理,导致“已删用户仍有资料”。这不是数据库问题,是设计漏洞。

实操建议:

  • 软删除触发器不推荐——跨表逻辑难维护,且 ORM 可能绕过触发器
  • 写清除脚本前先查依赖:SELECT COUNT(*) FROM orders WHERE user_id = ? AND deleted_at IS NULL;(注意:这里查的是订单本身的软删除状态,不是用户)
  • 物理删除应作为独立运维操作,而非日常 API 接口,避免误触

分页和排序时 deleted_at IS NULL 会影响性能吗?

会,尤其当软删除比例高(比如 70% 记录已删),WHERE deleted_at IS NULL 可能让索引失效,分页 LIMIT 10000,20 变得极慢。

根本原因:数据库优化器发现大量 NULL 值后,可能放弃使用 deleted_at 索引,转而全表扫描再过滤。

解法不是去掉条件,而是优化索引结构:

  • 复合索引优先:CREATE INDEX idx_user_active ON users(deleted_at, created_at DESC);,把过滤字段放最左
  • PostgreSQL 可用部分索引:CREATE INDEX idx_users_active ON users(id, name) WHERE deleted_at IS NULL;,体积小、命中率高
  • MySQL 8.0+ 支持隐藏索引,上线前可先 ALTER TABLE users ALTER INDEX idx_deleted_at INVISIBLE; 测试执行计划是否改善

真正麻烦的是历史数据迁移——旧表没索引,又不敢停写,得用在线 DDL 工具分批加索引,这个过程比实现软删除本身耗时得多。

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
数据分析工具有哪些
数据分析工具有哪些

数据分析工具有Excel、SQL、Python、R、Tableau、Power BI、SAS、SPSS和MATLAB等。详细介绍:1、Excel,具有强大的计算和数据处理功能;2、SQL,可以进行数据查询、过滤、排序、聚合等操作;3、Python,拥有丰富的数据分析库;4、R,拥有丰富的统计分析库和图形库;5、Tableau,提供了直观易用的用户界面等等。

2023.10.12

5043

8

SQL中distinct的用法
SQL中distinct的用法

SQL中distinct的语法是“SELECT DISTINCT column1, column2,...,FROM table_name;”。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.10.27

1031

4

SQL中months_between使用方法
SQL中months_between使用方法

在SQL中,MONTHS_BETWEEN 是一个常见的函数,用于计算两个日期之间的月份差。想了解更多SQL的相关内容,可以阅读本专题下面的文章。

2024.02.23

1329

5

SQL出现5120错误解决方法
SQL出现5120错误解决方法

SQL Server错误5120是由于没有足够的权限来访问或操作指定的数据库或文件引起的。想了解更多sql错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

7101

10

sql procedure语法错误解决方法
sql procedure语法错误解决方法

sql procedure语法错误解决办法:1、仔细检查错误消息;2、检查语法规则;3、检查括号和引号;4、检查变量和参数;5、检查关键字和函数;6、逐步调试;7、参考文档和示例。想了解更多语法错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

3743

4

oracle数据库运行sql方法
oracle数据库运行sql方法

运行sql步骤包括:打开sql plus工具并连接到数据库。在提示符下输入sql语句。按enter键运行该语句。查看结果,错误消息或退出sql plus。想了解更多oracle数据库的相关内容,可以阅读本专题下面的文章。

2024.04.07

7100

11

sql中where的含义
sql中where的含义

sql中where子句用于从表中过滤数据,它基于指定条件选择特定的行。想了解更多where的相关内容,可以阅读本专题下面的文章。

2024.04.29

9941

6

sql中删除表的语句是什么
sql中删除表的语句是什么

sql中用于删除表的语句是drop table。语法为drop table table_name;该语句将永久删除指定表的表和数据。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1350

5

sql中删除一列的命令是什么
sql中删除一列的命令是什么

在sql中,使用alter table语句可以删除一列,语法为:alter table table_name drop column column_name。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1172

5

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
热门推荐
/
最新课程
phpStudy极速入门视频教程
phpStudy极速入门视频教程

共6课时 | 54.7万人学习

独孤九贱(4)_PHP视频教程
独孤九贱(4)_PHP视频教程

共89课时 | 134.3万人学习