软删除场景下的数据库唯一约束实现方案

2026/04/28技术杂谈3303127 字7 分钟

背景

在业务开发中，软删除（Soft Delete）是一种常见的数据保留策略。当记录被"删除"时，我们通常不会真正从数据库中移除，而是通过 deleted_at 字段标记删除时间。

一个典型的业务需求是：未删除的记录之间保持唯一约束，而已删除的记录允许重复。

例如，同一个邮箱可以出现在多条已删除记录中，但在所有未删除记录中必须唯一。

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    deleted_at DATETIME NULL,      -- NULL 表示未删除
    ...
);

一、PostgreSQL 方案：部分唯一索引

PostgreSQL 原生支持部分唯一索引（Partial Unique Index），这是最优雅的解决方案。

基础用法

CREATE UNIQUE INDEX uq_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

索引只包含未删除的行，未删除记录的 email 唯一，已删除记录则不受约束限制。

多字段场景

-- 按租户隔离的单字段唯一
CREATE UNIQUE INDEX uq_users_tenant_email_active
    ON users (tenant_id, email)
    WHERE deleted_at IS NULL;

-- 多字段组合唯一（用户名+手机号仅在租户内活跃记录唯一）
CREATE UNIQUE INDEX uq_users_tenant_username_phone_active
    ON users (tenant_id, username, phone)
    WHERE deleted_at IS NULL;

优点：声明式语法、无冗余列、性能最优、索引体积最小。

二、MySQL 方案

MySQL 本身不支持 WHERE 条件的部分索引，但我们可以利用唯一索引对 NULL 值不强制唯一这一特性。

在 UNIQUE 索引中，NULL 被认为不等于任何值，包括另一个 NULL，因此允许多个 NULL 共存。

核心思路是：将未删除记录的索引键映射为真实业务值，将已删除记录的索引键映射为 NULL。

2.1 MySQL 8.0.13+：函数索引

这是最接近 PostgreSQL 语义的方案。

-- 单字段唯一
CREATE UNIQUE INDEX uq_users_email_active
    ON users ((IF(deleted_at IS NULL, email, NULL)));

-- 或使用 CASE 写法
CREATE UNIQUE INDEX uq_users_email_active
    ON users ((CASE WHEN deleted_at IS NULL THEN email END));

多字段组合：

CREATE UNIQUE INDEX uq_users_tenant_email_active
    ON users (tenant_id, (IF(deleted_at IS NULL, email, NULL)));

工作原理：当 deleted_at IS NULL 时返回 email，否则返回 NULL。未删除记录的索引键有具体的 email 值，受唯一约束保护；已删除记录的索引键全是 NULL，可以重复。

⚠️ 注意事项：表达式索引在 MySQL 中采用虚拟实现，不额外占用存储。但查询时必须与原表达式完全一致才能使用该索引。

2.2 MySQL 5.7：虚拟生成列 + 唯一索引

MySQL 5.7 支持虚拟生成列（VIRTUAL），可以在其上建立唯一索引。

ALTER TABLE users ADD COLUMN email_active VARCHAR(255)
    GENERATED ALWAYS AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL;

CREATE UNIQUE INDEX uq_users_email_active ON users (email_active);

多租户场景需要综合多个字段：

ALTER TABLE users ADD COLUMN tenant_email_active VARCHAR(512)
    GENERATED ALWAYS AS (
        IF(deleted_at IS NULL, CONCAT(tenant_id, ':', email), NULL)
    ) VIRTUAL;

CREATE UNIQUE INDEX uq_users_tenant_email_active ON users (tenant_email_active);

优点：方案稳定可靠，索引与普通列无异，是 MySQL 5.7 的最佳实践。

⚠️ 注意：虚拟列不占用存储空间，但 VIRTUAL 列不能作为主键的一部分，也不能用于外键（唯一索引本身通常不需要作为外键目标，问题不大）。如果使用 STORED 模式则会占用额外磁盘空间。

2.3 MySQL 5.6 及更早版本：魔法默认值

旧版本不支持生成列或函数索引，只能利用唯一组合索引允许重复 NULL的特性，将 NULL 替换为一个非 NULL 的默认值。

ALTER TABLE users
    ADD COLUMN deleted_at DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00';

CREATE UNIQUE INDEX uq_users_email_undeleted
    ON users (email, deleted_at);

局限性：

语义不直观，需要放弃 NULL 表示"未删除"的惯例
若两次删除同一邮箱的时间完全相同（如批量删除），唯一索引会冲突
所有查询都需要使用魔法值 WHERE deleted_at = '1970-01-01 00:00:00'

不推荐，仅在无法升级时作为最后手段。

三、进阶场景

3.1 多字段唯一约束

PostgreSQL：

CREATE UNIQUE INDEX uq_users_tenant_email_phone_active
    ON users (tenant_id, email, phone)
    WHERE deleted_at IS NULL;

MySQL 8.0.13+：

CREATE UNIQUE INDEX uq_users_tenant_email_phone_active
    ON users (
        tenant_id,
        (IF(deleted_at IS NULL, email, NULL)),
        (IF(deleted_at IS NULL, phone, NULL))
    );

⚠️ 需要注意的唯一性"泄露"风险：在 MySQL 唯一索引中，键值中只要有一个列为 NULL，整行就可以重复。例如 (1, "a", NULL) 和 (1, "a", NULL) 在 MySQL 唯一索引中被视为不同值。如果业务允许唯一键中的某列为 NULL，唯一性约束就会"失效"。PostgreSQL 的部分索引同样存在这个问题——只有未删除行才在索引里，如果允许其中一列为 NULL，则认为键值不同。因此，业务上通常应确保唯一键中的所有列在未删除时都不为 NULL。

3.2 复杂删除条件

当软删除有多种状态（如 status = 'deleted'）时：

-- PostgreSQL
CREATE UNIQUE INDEX ... WHERE status <> 'deleted';

-- MySQL
CREATE UNIQUE INDEX ... ON users ((IF(status <> 'deleted', email, NULL)));

四、性能与维护对比

特性	PostgreSQL 部分唯一索引	MySQL 8.0+ 函数索引	MySQL 5.7 虚拟列
实现复杂度	极低	低	中（需添加列）
索引大小	仅含未删除行，最小	同左	同左
写入性能	极轻微开销	表达式计算开销极小	虚拟列计算极小
查询匹配	优化器自动使用	需与原表达式完全一致	用列名即可，更直观
多字段组合	原生支持	需要多个表达式	需要拼接为字符串
最低版本要求	所有版本	8.0.13+	5.7+

五、总结与选型建议

数据库版本	推荐方案
PostgreSQL（任意版本）	部分唯一索引——语义清晰、性能好、代码干净
MySQL 8.0.13+	函数索引——一行 DDL，与 PostgreSQL 最为接近
MySQL 5.7	虚拟生成列 + 唯一索引——生产成熟，唯一缺点是多了辅助列
MySQL 5.6 及更早	考虑升级；无法升级时使用"魔法默认值"方案

生产环境注意事项

正确选择索引类型：避免因索引配置不当导致隐藏的全表扫描
异常捕获：MySQL 的函数索引或虚拟列方案在插入冲突时返回标准的 Duplicate entry 错误，与 PostgreSQL 行为一致，应用代码中的异常处理逻辑可以统一处理

延伸话题

ORM 集成：如 GORM、SQLAlchemy 的软删除插件如何配合这些索引使用
大表在线加索引：使用 pt-online-schema-change 避免锁表风险

如有需要，可以进一步探讨这些话题。

暂无评论