软删除和唯一索引辩论：把 deleted_at 加进唯一索引，为什么反而不唯一了

软删除（逻辑删除）和唯一索引放在同一张表上，几乎是每个后端项目都会遇到的辩论：用户注销了账号，过两天想用同一个邮箱重新注册，效果插入报 Duplicate entry。由于那条"已删除"的记录还在表里，唯一索引可不管它删没删。
网上最常见的解法是"把 deleted_at 加进唯一索引"。这个解法有一个很隐蔽的漏洞，会让唯一索引在正常数据上直接失效。下面把四种常见方案在 MySQL 8.0.45 上逐个跑了一遍，效果都是现实输出。
方案 A：deleted_at 为 NULL + 联合唯一索引（有坑）

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

1. CREATE TABLE u1 (
2.   id BIGINT AUTO_INCREMENT PRIMARY KEY,
3.   email VARCHAR(100) NOT NULL,
4.   deleted_at DATETIME NULL,
5.   UNIQUE KEY uk (email, deleted_at)
6. );
8. INSERT INTO u1 (email) VALUES ('[email protected]');
9. INSERT INTO u1 (email) VALUES ('[email protected]');
10. SELECT COUNT(*) FROM u1 WHERE email = '[email protected]' AND deleted_at IS NULL;
11. -- 2

复制代码

两条都插进去了，没有任何报错。两个"未删除"的同邮箱用户同时存在。
原因是 SQL 标准里 NULL 不等于 NULL，MySQL 的唯一索引允许多个 NULL。('[email protected]', NULL) 和 ('[email protected]', NULL) 在唯一索引眼里是两个差别的值。
这个方案解决了"删除后能重新注册"，代价是未删除的数据完全失去了唯一性保护。更麻烦的是它平时不会暴露，只有在并发注册、接口重试这种时间才会出现重复数据，而当时间应用层的"先查后插"校验往往也挡不住。
方案 B：deleted 字段，未删除为 0，删除时写成自己的 id

1. CREATE TABLE u2 (
2.   id BIGINT AUTO_INCREMENT PRIMARY KEY,
3.   email VARCHAR(100) NOT NULL,
4.   deleted BIGINT NOT NULL DEFAULT 0,
5.   UNIQUE KEY uk (email, deleted)
6. );

复制代码

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

1. INSERT '[email protected]'            -- ok, id=1
2. INSERT '[email protected]'            -- ERROR 1062: Duplicate entry '[email protected]'
3. 删除 id=1 (deleted=1)
4. INSERT '[email protected]'            -- ok, id=3
5. 删除 id=3 (deleted=3)
6. INSERT '[email protected]'            -- ok, id=4
8. id  email    deleted
9. 4   [email protected]  0
10. 1   [email protected]  1
11. 3   [email protected]  3

复制代码

未删除的数据 deleted 都是 0，唯一性有保证；删除后每条记录的 deleted 都是自己的主键，绝不会互相辩论，想删多少次都行。
这是我最推荐的方案，没有任何边界情况。缺点是"删除时间"要另外存一个字段，而且和 ORM 自带的软删除约定不同等，需要自己实现删除和查询过滤（大部分 ORM 支持自定义软删除字段，改起来不难）。
注意中间那次失败的 INSERT 也消耗了一个自增 id（id=2 不见了），这是 InnoDB 的正常行为，不影响效果。
方案 C：生成列 + 唯一索引

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

1. CREATE TABLE u3 (
2.   id BIGINT AUTO_INCREMENT PRIMARY KEY,
3.   email VARCHAR(100) NOT NULL,
4.   deleted_at DATETIME NULL,
5.   active_email VARCHAR(100) AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL,
6.   UNIQUE KEY uk (active_email)
7. );

复制代码

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

1. INSERT '[email protected]'            -- ok
2. INSERT '[email protected]'            -- ERROR 1062: Duplicate entry '[email protected]'
3. 删除全部
4. INSERT '[email protected]'            -- ok
5. 删除未删除的那条
6. INSERT '[email protected]'            -- ok
8. id  email    deleted_at           active_email
9. 1   [email protected]  2026-09-26 10:19:02  NULL
10. 3   [email protected]  2026-09-26 10:19:02  NULL
11. 4   [email protected]  NULL                 [email protected]

复制代码

注意第 1 条和第 3 条的删除时间在同一秒，也没有辩论。这是它比方案 D 好的地方。
代价是多一个列和一个索引，而且应用层代码不能往 active_email 里写值（写了会报错），用 ORM 时要把它标记成只读。MySQL 5.7 起支持在生成列上建索引。
方案 D：deleted_at 用一个固定值表示"未删除"（有边界）

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

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

复制代码

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

1. ERROR 1062: Duplicate entry '[email protected] 11:00:00' for key 'u4.uk'

复制代码

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

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

1. 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 一段时间的表，大概率已经有了。
  
• 软删除本身会让表越来越大，唯一索引也跟着变大。如果删除的数据确实不需要了，定期归档到历史表比永久留在主表里更好。
  

免责声明：如果侵犯了您的权益，请联系站长及时删除侵权内容，谢谢合作！qidao123.com:ToB企服之家，中国第一个企服评测及软件市场,开放入驻,技术点评得现金.