如何利用SQL实现数据的软删除与过期自动清理_结合TTL索引或定时任务-SQL-PHP中文网

软删除字段只需 deleted_at（null 表示未删除），不需 is_deleted；mysql 无原生 ttl 索引，须用分批定时任务清理，并确保 deleted_at 已建索引。

如何利用sql实现数据的软删除与过期自动清理_结合ttl索引或定时任务

软删除字段设计必须包含 `deleted_at` 和 `is_deleted` 吗？

不需要二者共存，选一个即可，但推荐只用 deleted_at（NULL 表示未删除）。原因很实际：is_deleted 是冗余布尔字段，容易和 deleted_at 不一致；而 deleted_at IS NULL 语义清晰、可索引、支持范围查询（比如查“3天内软删的记录”），且与 TTL 索引天然兼容。

建表时建议这样写：

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id INT,
  status VARCHAR(20),
  deleted_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

查询活跃数据时统一加条件：WHERE deleted_at IS NULL。别漏掉——这是软删除生效的前提。

MySQL 能不能直接用 TTL 索引自动清理软删数据？

不能。MySQL 没有原生 TTL 索引机制（那是 MongoDB 的特性）。所谓“TTL 索引”在 MySQL 场景下是误传，实际只能靠定时任务模拟。

PostgreSQL 也不行（pg_cron 是插件，非内建）；真正原生支持 TTL 的只有 MongoDB（expireAfterSeconds 配合 created_at 字段）。

所以如果你用的是 MySQL 或 PostgreSQL，必须自己实现清理逻辑。常见做法：

小鱼AI写作

小鱼AI写作是一款AI文本写作工具，一站式AI写作平台，一键生成高质量原创内容。

下载

用 EVENT（MySQL）或 pg_cron（PostgreSQL）定期执行 DELETE FROM orders WHERE deleted_at
用外部调度器（如 Linux cron + mysql -e 命令）跑清理 SQL
在应用层定时触发（例如 Spring Boot 的 @Scheduled），但要注意分布式环境重复执行问题

注意：清理 SQL 必须走 deleted_at 索引，否则全表扫描会锁表。务必确认该字段已建索引：CREATE INDEX idx_deleted_at ON orders(deleted_at)。

为什么清理任务要分批执行而不是一口气 `DELETE`？

因为单次大事务会严重阻塞写入、拖慢主从同步、甚至触发 OOM 或超时中断。尤其当软删数据量达百万级时，DELETE FROM ... WHERE deleted_at 可能卡住十几秒。

安全做法是分页清理，例如 MySQL 中：

DELETE FROM orders 
WHERE deleted_at <p>然后循环执行，直到影响行为 0。关键点：</p>

必须带 ORDER BY（推荐主键），否则 LIMIT 行为不可控
每次 LIMIT 值不宜过大（5k–10k 较稳妥），避免单次事务过重
两次执行之间加短延时（如 0.1 秒），缓解 I/O 压力
记录最后处理的 id，下次从它之后继续，比时间条件更精确（避免时间重复或跳过）

应用代码里怎么避免手动拼 `deleted_at IS NULL`？

硬编码 WHERE 条件极易遗漏，尤其多人协作时。推荐两种落地方式：

ORM 层全局作用域：比如 Laravel Eloquent 的 globalScopes，或 Django 的 QuerySet 子类重写 get_queryset，自动追加 deleted_at__isnull=True
数据库视图封装：创建 orders_active 视图，SELECT * FROM orders WHERE deleted_at IS NULL，应用只查视图。缺点是无法直接对视图做 INSERT/UPDATE（除非用 INSTEAD OF 触发器）

无论哪种，都要明确：软删除不是“不删”，而是“删得可控、可逆、可审计”。真正难的不是加个字段，而是让所有读写路径都尊重它——包括统计报表、导出脚本、后台搜索、ES 同步任务……这些地方最容易漏掉过滤条件。