19 数据操作之删除 —— 行不消失，只是被标记了一个时间戳

前置阅读: 17-数据操作之新增、18-数据操作之更新
关键词: soft delete, deleted_at, update, 过滤
难度: ★★★☆☆

场景导入：DELETE 真的要把数据抹掉吗？

第 17 章实现了新增，第 18 章用乐观锁搞定了更新。现在来到 CRUD 的最后一环——删除。直觉上，删除就是 DELETE FROM books WHERE id = 3。但图书管理这类业务场景有一个硬需求：操作必须是可逆的。管理员误删了一本书，你必须在 30 秒内恢复，而不是从备份里翻半小时。

这就是软删除（soft delete）的用武之地。它不真正删除行，而是在 deleted_at 列写一个 UTC 时间戳。后续所有面向用户的查询自动附加 WHERE deleted_at IS NULL——已删除的行在业务视角"消失"了，但在数据库里完好无损。误删恢复只需一行 UPDATE books SET deleted_at = NULL WHERE id = 3。

本篇沿用第 18 章的 Core 层 update() 写法，把 DELETE /books/{book_id} 改造为软删除端点，并配套修改 list_books 等查询，确保"已删除行不可见"成为 DAO 层的默认契约。

原理解析：软删除 = Core 层 UPDATE + 全局过滤

软删除和硬删除在 SQL 层面的差别只有一层窗户纸：

-- 硬删除（本系列不使用）
DELETE FROM books WHERE id = 3;

-- 软删除（本系列选择）
UPDATE books
   SET deleted_at = '2026-07-21T10:00:00+00:00'
 WHERE id = 3
   AND deleted_at IS NULL

选择软删除意味着三件事。可恢复：误删后一行 SET deleted_at = NULL 即可。审计完整：删除前的所有字段保留，合规回溯不需要归档表。查询必须过滤：所有面向用户的 SELECT 必须追加 WHERE deleted_at IS NULL——漏一处就是"客户端 DELETE 成功但 GET 仍然能看到"的幽灵数据事故。

（图注：update(Book).values(deleted_at=...) 把删除动作翻译为 Core 层 UPDATE，rowcount 决定后续是 commit 还是抛 NotFoundError——与第 18 章乐观锁的 rowcount 用法一脉相承。）

update(Book).where(Book.id == book_id, Book.deleted_at.is_(None)).values(deleted_at=datetime.now(UTC)) 是本期最重要的语句。Book.deleted_at.is_(None) 翻译为 deleted_at IS NULL——这个条件确保二次删除（对已删除行再次 DELETE）也会返回 404。

时间戳的生成位置有讲究：datetime.now(UTC) 在应用层生成，锁定的是"请求到达的瞬间"；而 MySQL 的 NOW() 在数据库执行 UPDATE 时生成——如果事务因为锁等待排队，NOW() 的时间会比请求到达时刻晚。这和第 18 章"version + 1 必须由数据库计算"形成互补：乐观锁自增必须交给数据库（保证并发正确性），删除时间戳必须由应用生成（保证语义对齐请求瞬间）。

result.rowcount == 0 在软删除场景下有两种可能：id 不存在（404 语义），或行已被删除（二次删除）。两者在 SQL 层都是 WHERE 不命中，本系列统一抛 NotFoundError(book_id)，由第 08 章的异常处理器映射为 404。

（图注：软删除的 Core 层流程与第 18 章更新几乎同构，只是 values 改为写入 deleted_at、失败时抛 NotFoundError 而非 OptimisticLockError。）

查询侧必须配套过滤。第 16 章的 list_books 需要在 DAO 层追加 stmt = stmt.where(Book.deleted_at.is_(None))——所有 list/get 一致生效，路由层不感知 deleted_at 的存在。软删除对 API 调用方完全不可见。

代码实现：soft_delete_book + NotFoundError + 查询过滤

DAO 层新增 soft_delete_book：

# app/dao/book_dao.py（增量追加）
from datetime import datetime, UTC

from sqlalchemy import update
from sqlalchemy.ext.asyncio import AsyncSession

from app.errors import NotFoundError
from app.models.book import Book


async def soft_delete_book(db: AsyncSession, book_id: int) -> None:
    """按 id 软删除图书；行不存在或已删除抛 NotFoundError。"""
    stmt = (
        update(Book)
        .where(Book.id == book_id, Book.deleted_at.is_(None))
        .values(deleted_at=datetime.now(UTC))
    )
    result = await db.execute(stmt)
    if result.rowcount == 0:
        raise NotFoundError(book_id)
    await db.commit()

领域异常类：

# app/errors.py（新增）
class NotFoundError(Exception):
    """资源（id）不存在或已被软删除。"""

    def __init__(self, resource_id: int) -> None:
        super().__init__(f"resource {resource_id} not found")
        self.resource_id = resource_id

查询侧的过滤——在 list_books 的 select(Book) 之后追加一行：

# app/dao/book_dao.py（list_books 内追加）
stmt = stmt.where(Book.deleted_at.is_(None))

路由层追加 DELETE /books/{book_id} handler，返回 204 No Content：

# app/api/books.py（追加路由）
from fastapi import status

from app.dao import book_dao
from app.errors import NotFoundError


@router.delete("/{book_id}", status_code=status.HTTP_204_NO_CONTENT)
async def delete_book(book_id: int, db: DBDep) -> None:
    """按 id 软删除图书，成功返回 204，不存在返回 404。"""
    await book_dao.soft_delete_book(db, book_id)

性能优化：复合索引让"未删除"查询走索引

软删除场景的典型查询是 WHERE deleted_at IS NULL ORDER BY id。如果 deleted_at 单独一个索引，MySQL 仍需回表或排序。复合索引 (deleted_at, id) 让 B+ 树以这两列为前缀：deleted_at IS NULL 走索引定位到连续范围，id 已是索引第二列，ORDER BY id 不再触发 filesort。

# app/models/book.py（追加）
from sqlalchemy import Index, DateTime

class Book(Base):
    # ... 其他字段 ...
    deleted_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True
    )

    __table_args__ = (
        Index("ix_books_deleted_at_id", "deleted_at", "id"),
    )

列顺序为什么是 (deleted_at, id) 而不是 (id, deleted_at)？因为典型查询 WHERE deleted_at IS NULL ORDER BY id 需要先按 deleted_at 过滤再按 id 排序。(deleted_at, id) 完美命中最左前缀；反转后 B+ 树以 id 排序，deleted_at 散落各处，索引失效。

避坑指南

• 查询过滤是软删除的"安全阀"。list_books、get_book、所有 select(Book) 路径都必须在 DAO 层追加 deleted_at.is_(None)。漏一处就是幽灵数据。Code Review 时，任何出现的 select(Book) 都应该被质疑"加了软删除过滤吗？"

• 软删除不释放磁盘空间。books 表持续积累"墓碑行"。生产环境应配套定时清理任务（如保留 90 天后物理删除 DELETE FROM books WHERE deleted_at < ...），保留审计窗口后真正回收空间。

• 唯一索引的软删除陷阱。ISBN 唯一索引不允许重复值。删除一本 ISBN=“xxx” 的书后，再创建同 ISBN 的新书会报 1062 冲突——因为被删行还在索引里。解决方案：软删除后将 ISBN 改为 {原值}#deleted-{id} 释放唯一值，或在业务层允许管理员手动恢复而非重新创建。

• 关联对象的软删除。如果 Book 被软删除，关联的 Borrow 也应通过查询过滤屏蔽——第 20 章引入 Borrow 模型时复用同一套 deleted_at 过滤规则。

面试 QA

Q1 [原理]: 软删除与硬删除在数据一致性、查询语义与索引膨胀上各自的取舍是什么？

软删除保留行并以 deleted_at 标记，核心收益是"删除可逆 + 审计完整"。代价有三：所有查询必须追加 deleted_at IS NULL 过滤——漏一处就是事故；索引体积膨胀——墓碑行不释放 B+ 树节点；唯一索引逻辑复杂——被删行仍占用唯一值。

硬删除直接物理移除行，收益是语义和存储干净——查询不需要过滤，索引自动收缩。代价是"删除不可逆"——合规要求保留历史数据时必须配合归档表或事件溯源。

图书 API 是"误删可恢复 + 审计可追溯"场景，软删除是默认选择。第 20 章的借阅记录也能复用 deleted_at 标记"作废借阅"。

Q2 [项目]: 为什么用 Index("ix_books_deleted_at_id", Book.deleted_at, Book.id) 这种复合索引？列顺序为什么是 (deleted_at, id)？

软删除场景最频繁的查询是 WHERE deleted_at IS NULL ORDER BY id LIMIT 20。Index("ix_books_deleted_at_id", Book.deleted_at, Book.id) 以 (deleted_at, id) 为前缀建 B+ 树：deleted_at IS NULL 走索引定位到连续范围，id 已在索引第二列，ORDER BY id 不触发 filesort——P99 延迟从全表扫描的数百毫秒降到个位毫秒。

列顺序不能反过来。(id, deleted_at) 让 B+ 树以 id 排序，deleted_at 值散落各处，deleted_at IS NULL 过滤需要回表逐行检查——索引完全失效。这是"高基数列放后面"的经典反例——在这个查询模式下，低基数列反而应该放前面。

小结

本期把 DELETE /books/{book_id} 改造为软删除：update(Book).values(deleted_at=datetime.now(UTC)) 标记删除，rowcount == 0 抛 NotFoundError，查询在 DAO 层默认过滤 deleted_at IS NULL。软删除对路由层完全透明——handler 只看到"资源不存在"或"删除成功"，不需要知道数据在数据库里是消失了还是被标记了。

下一篇《20 ORM 总结》将引入 Borrow 模型与借书/还书跨表事务，把第 03 章的 async def/def 判别准则、第 13 章的异步基础设施和第 14-19 章的 CRUD 操作串成闭环——这是整个数据层的终点。