讲师中心 微信公众号

工具推荐

视频效率加速
立刻MV
立刻MV

低成本歌曲 MV、视频生成

updream

一键生成高质量推广成片

Laper
Laper

剧本策划到成片协作

AniShort
AniShort

AI短剧协同制作平台

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

千静吖_3396

千静吖_3396

发布时间:2026-11-16 11:17:47

|

761人浏览过

|

来源于php中文网

原创

优先选 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 工具分批加索引,这个过程比实现软删除本身耗时得多。

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

热门AI工具

更多
超级麦吉

一款通过数字员工协作完成调研、数据分析与文档制作的AI办公平台。

小橡皮AI

一款AI工具,主要用于小橡皮一键去AI味,将机器味重的内容改得更有活人感,防止因为 AI 内容被平台打上 AI 标签,以及限流,适合需要提升相关任务效率的用户。

咔片AIPPT

一款在线AI演示文稿制作工具,可根据主题和内容需求辅助生成PPT结构与页面,提高演示材料制作效率。

VibeKnow
VibeKnow Hot

一款AI视频创作工具,主要用于全球首个AI知识视频创作平台,文档、文章、网页,一键生成视频,适合需要提升相关任务效率的用户。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

墨刀AI
墨刀AI Hot

一款AI图像与设计工具,主要用于产品经理的专属智能体,适合需要提升相关任务效率的用户。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

码上飞
码上飞 Hot

一款基于自然语言的AI应用开发平台,可根据用户描述自动生成应用程序,帮助个人和团队快速完成应用原型与开发。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

相关专题

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

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

5043

2023.10.12

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

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

1031

2023.10.27

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

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

1329

2024.02.23

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

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

7101

2024.03.06

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

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

3743

2024.03.06

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

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

7100

2024.04.07

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

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

9941

2024.04.29

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

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

1350

2024.04.29

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

120

2026.11.12

热门下载

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

精品课程

更多
热门推荐
/
最新课程
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn