软删除（逻辑删除）和唯一索引放在同一张表上，几乎是每个后端项目都会遇到的冲突：用户注销了账号，过两天想用同一个邮箱重新注册，结果插入报 Duplicate entry。因为那条"已删除"的记录还在表里，唯一索引可不管它删没删。

网上最常见的解法是"把 deleted_at 加进唯一索引"。这个解法有一个很隐蔽的漏洞，会让唯一索引在正常数据上直接失效。下面把四种常见方案在 MySQL 8.0.45 上逐个跑了一遍，结果都是实际输出。

方案 A：deleted_at 为 NULL + 联合唯一索引（有坑）

很多 ORM 的软删除默认就是这种形态：未删除时 deleted_at 为 NULL，删除时写入时间。于是很自然地想到把它加进唯一索引：

CREATE TABLE u1 (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk (email, deleted_at)
);

INSERT INTO u1 (email) VALUES ('a@x.com');
INSERT INTO u1 (email) VALUES ('a@x.com');
SELECT COUNT(*) FROM u1 WHERE email = 'a@x.com' AND deleted_at IS NULL;
-- 2

两条都插进去了，没有任何报错。两个"未删除"的同邮箱用户同时存在。

原因是 SQL 标准里 NULL 不等于 NULL，MySQL 的唯一索引允许多个 NULL。('a@x.com', NULL) 和 ('a@x.com', NULL) 在唯一索引眼里是两个不同的值。

这个方案解决了"删除后能重新注册"，代价是未删除的数据完全失去了唯一性保护。更麻烦的是它平时不会暴露，只有在并发注册、接口重试这种时候才会出现重复数据，而那时候应用层的"先查后插"校验往往也挡不住。

方案 B：deleted 字段，未删除为 0，删除时写成自己的 id

CREATE TABLE u2 (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  deleted BIGINT NOT NULL DEFAULT 0,
  UNIQUE KEY uk (email, deleted)
);

删除时执行 UPDATE u2 SET deleted = id WHERE ...。跑一遍：

INSERT 'a@x.com'            -- ok, id=1
INSERT 'a@x.com'            -- ERROR 1062: Duplicate entry 'a@x.com-0'
删除 id=1 (deleted=1)
INSERT 'a@x.com'            -- ok, id=3
删除 id=3 (deleted=3)
INSERT 'a@x.com'            -- ok, id=4

id  email    deleted
4   a@x.com  0
1   a@x.com  1
3   a@x.com  3

未删除的数据 deleted 都是 0，唯一性有保证；删除后每条记录的 deleted 都是自己的主键，绝不会互相冲突，想删多少次都行。

这是我最推荐的方案，没有任何边界情况。缺点是"删除时间"要另外存一个字段，而且和 ORM 自带的软删除约定不一致，需要自己实现删除和查询过滤（大部分 ORM 支持自定义软删除字段，改起来不难）。

注意中间那次失败的 INSERT 也消耗了一个自增 id（id=2 不见了），这是 InnoDB 的正常行为，不影响结果。

方案 C：生成列 + 唯一索引

如果表结构已经是方案 A 的样子，不方便改 deleted_at 的语义，可以加一个生成列：

CREATE TABLE u3 (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  deleted_at DATETIME NULL,
  active_email VARCHAR(100) AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL,
  UNIQUE KEY uk (active_email)
);

思路是反过来利用"唯一索引允许多个 NULL"：未删除时 active_email 等于邮箱，参与唯一约束；删除后它变成 NULL，多少条都不冲突。

INSERT 'a@x.com'            -- ok
INSERT 'a@x.com'            -- ERROR 1062: Duplicate entry 'a@x.com'
删除全部
INSERT 'a@x.com'            -- ok
删除未删除的那条
INSERT 'a@x.com'            -- ok

id  email    deleted_at           active_email
1   a@x.com  2026-09-26 10:19:02  NULL
3   a@x.com  2026-09-26 10:19:02  NULL
4   a@x.com  NULL                 a@x.com

注意第 1 条和第 3 条的删除时间在同一秒，也没有冲突。这是它比方案 D 好的地方。

代价是多一个列和一个索引，而且应用层代码不能往 active_email 里写值（写了会报错），用 ORM 时要把它标记成只读。MySQL 5.7 起支持在生成列上建索引。

方案 D：deleted_at 用一个固定值表示"未删除"（有边界）

另一种绕开 NULL 的办法是让 deleted_at 非空，未删除时用一个固定的哨兵值：

CREATE TABLE u4 (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  deleted_at DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
  UNIQUE KEY uk (email, deleted_at)
);

未删除的数据唯一性没问题。问题在删除侧：同一个邮箱如果在同一秒内被删除两次（比如注册、注销、再注册、再注销，由脚本或者重试触发），第二次删除会失败：

ERROR 1062: Duplicate entry 'a@x.com-2026-09-26 11:00:00' for key 'u4.uk'

正常用户很难在一秒内完成两轮注册注销，所以这个方案在大多数业务里能用。但"删除操作可能因为唯一索引失败"这件事本身就很反直觉，出问题的时候很难第一时间想到。改成 DATETIME(6) 精确到微秒可以把概率压得很低，但不能归零。

顺带说一下 PostgreSQL

PostgreSQL 有部分索引（partial index），这个问题一行就解决了：

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

MySQL 没有部分索引，方案 C 的生成列可以看作是它的替代写法。

怎么选

方案未删除数据唯一可重复删除改动成本
A. deleted_at NULL + 联合索引 ❌ 不保证 ✅ 低
B. deleted = 0 / id ✅ ✅ 中，要改删除逻辑
C. 生成列 ✅ ✅ 低，加一列
D. 哨兵时间 ✅ 同一秒内不行 中

新表我会直接用 B。已经在用 ORM 默认软删除（方案 A 那种形态）的老表，加一个 C 的生成列最省事，不用动任何业务代码的写入逻辑。

我在做 forxi.cn 的后端时也碰到过这个选择，最后的体会是：唯一性这种约束，一定要让数据库来保证，不要只靠应用层"先查一下有没有"。先查后插在并发下必然有窗口，而数据库的唯一索引没有。

局限

  • 上面的结果都是在 MySQL 8.0.45 / InnoDB 上跑出来的。NULL 在唯一索引里的行为在 MySQL 各版本一致，但其他数据库不一定（比如 SQL Server 默认只允许一个 NULL），换库时要重新验证。
  • 给已有大表加唯一索引或生成列是 DDL 操作，数据量大的时候要用在线 DDL 工具，并且加之前先查一遍有没有已经重复的数据，有的话索引会建失败。用了方案 A 一段时间的表，大概率已经有了。
  • 软删除本身会让表越来越大，唯一索引也跟着变大。如果删除的数据确实不需要了，定期归档到历史表比永远留在主表里更好。