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 噩梦。