MySQL 与 PostgreSQL 软删除进阶的唯一索引模式

有没有遇到过这样的场景：用户向支持团队投诉，每次登录时看到的订单历史都不一样。有时能看到最近的购买记录，有时显示几年前的订单，有时甚至什么都没有。

经过几个小时的排查，你发现这个用户竟然有三个 email 完全相同的账户。每次登录时，认证系统“随机”挑了一个账户来用，于是就出现了各种诡异的结果。

这种噩梦般的场景其实比你想的更常见，而它完全可以通过合适的数据库约束来提前预防。

重复数据是如何偷偷溜进来的

来看一个简单的例子。你有一个 users 表，注册逻辑就是把 email 和密码插入数据库：

INSERT INTO users (email, password)
VALUES ('alice@example.com', 'hashed_password_here');

看起来没问题。但如果用户在注册页面双击了“提交”按钮呢？这种情况在现实中非常多见。

-- 第一个请求成功
INSERT INTO users (email, password)
VALUES ('alice@example.com', 'hashed_password_here');

-- 第二个请求也成功（刷新页面、双击提交、网络重试等）
INSERT INTO users (email, password)
VALUES ('alice@example.com', 'hashed_password_here');

现在数据库里有了两条 email 完全相同的记录。当 Alice 登录时，你的查询可能是这样的：

SELECT * FROM users WHERE email = 'alice@example.com' LIMIT 1;

问题是：数据库会返回哪一个 Alice？答案是看运气。因为没有写 ORDER BY，返回哪条记录是未定义行为。这会导致各种不一致：订单历史对不上、偏好设置丢失、用户一脸懵逼地找客服。

引入唯一约束

幸好，关系型数据库提供了唯一约束（Unique Constraint）来彻底解决这个问题。只要在列（或列组合）上定义唯一约束，重复值就插不进去。

可以用 ALTER TABLE 或 CREATE UNIQUE INDEX 来实现：

ALTER TABLE users ADD UNIQUE INDEX users_email_unique (email);

或者：

CREATE UNIQUE INDEX users_email_unique ON users (email);

从此以后，任何试图插入相同 email 的操作都会直接报错：Duplicate entry。问题看起来解决了。

软删除带来的新麻烦

现在你的系统可以防止重复 email 了。但如果要支持“删除账户”功能，同时又允许用户以后重新注册呢？比如财务部门要求不能真删数据，因为可能还需要处理历史订单、退款等。

这时很多人会选择软删除（Soft Delete）：加一个 deleted_at 字段（或 is_active 布尔字段）。deleted_at IS NULL 表示正常用户，有值表示已删除。

用户删除账户时执行：

UPDATE users SET deleted_at = NOW() WHERE id = 123;

数据还在，但被标记为已删除。

现在假设这个用户想重新注册：

INSERT INTO users (email, password)
VALUES ('alice@example.com', 'new_hashed_password');
-- 报错：Duplicate entry 'alice@example.com' for key 'users_email_unique'

唯一约束依然生效，因为旧的那条软删除记录还占着这个 email。这导致用户根本无法用原来的邮箱重新注册，体验极差，最终要么疯狂提工单，要么直接跑去竞品。

PostgreSQL 的优雅解法：部分索引

PostgreSQL 提供了一个非常干净的解决方案——部分索引：

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

这个索引只包含 deleted_at IS NULL 的记录（即活跃用户）。已被软删除的记录根本不在这个索引里，也就不会参与唯一性检查。

语法直观，维护成本低，意图非常清晰。

MySQL 解决方案：生成列 + 唯一索引

MySQL 和 MariaDB 目前还不支持部分索引的 WHERE 条件，但可以用生成列（Generated Column）来曲线救国。

先添加一个虚拟生成列：

ALTER TABLE users
ADD COLUMN not_archived BOOLEAN
GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) VIRTUAL;

然后创建复合唯一索引：

CREATE UNIQUE INDEX users_email_active_unique ON users (email, not_archived);

原理是这样的：

• 活跃用户（deleted_at IS NULL） → not_archived = 1；
• 已删除用户（deleted_at 有值） → not_archived = NULL。

MySQL 的一个重要特性：唯一索引中，NULL 值不参与重复检查，可以有任意多条相同的 NULL。

所以：

• 所有已软删除的 alice@example.com 都是 (email, NULL) → 互相不冲突；
• 活跃的 alice@example.com 是 (email, 1) → 只能存在一条。

完美实现了“只对活跃记录唯一”的效果。

多租户场景下的写法

如果是多租户（multi-tenant）系统，你通常希望 email 在同一个租户内唯一，而不是全局唯一。这时可以把 tenant_id 也加到索引里：

PostgreSQL：

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

MySQL：

CREATE UNIQUE INDEX users_tenant_email_active_unique
ON users (tenant_id, email, not_archived);

这样不同租户的同一个 email 就可以共存，但同一租户内活跃 email 仍然保持唯一。

总结：你要记住的几点

• 普通唯一约束可以防止重复，但遇到软删除就失效；
• PostgreSQL 用部分唯一索引（Partial Unique Index + WHERE）最简洁优雅；
• MySQL/MariaDB 用生成列 + 唯一索引（把已删除记录映射为 NULL）来实现类似效果；
• 多租户场景记得把 tenant_id 加入复合唯一约束。

希望这几种模式能在你的下一个项目中帮到你，避免那些“同一个 email 多个账户”的深夜 debug 噩梦。

以上关于MySQL 与 PostgreSQL 软删除进阶的唯一索引模式的文章就介绍到这了，更多相关内容请搜索码云笔记以前的文章或继续浏览下面的相关文章，希望大家以后多多支持码云笔记。