MySQL 软删除后唯一索引怎么设计：保留历史记录，也允许同邮箱再次注册

会员操作注销账号后，运营和客服通常不希望直接清空对应的订单归属、历史操作记录；但过不了多久，同一个邮箱又会被新用户拿来注册。不少项目一开始只给邮箱字段建了 email 唯一索引，刚上线软删除功能，注册接口就频繁抛出重复键报错。这种情况别急着直接删掉唯一索引，问题根本不在约束规则本身，而是你没告诉数据库「只有处于正常使用状态的账号，邮箱才需要全局唯一」。

先看那个「删完账号还是没法注册」的实际场景

假设 account 这张表存了用户邮箱、昵称和删除时间字段。用户自己操作注销账号后，后台只需要把 deleted_at 改成当前时间，关联的订单表还是能通过 account_id 正常读到旧账号记录。等第二天他用同一个邮箱重新注册，插入新数据的时候直接撞上了原有索引的 uk_account_email 报错。

CREATE TABLE account (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  nickname VARCHAR(80) NOT NULL,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_account_email (email)
) ENGINE=InnoDB;

直接删掉原来的 uk_account_email 看似能放行新用户注册，后遗症就是两个同时处于活跃状态的账号也能共用同一个邮箱。后续登录、密码找回、通知推送、权限合并全都会出逻辑问题。这套数据链路真正要遵守的规则其实只有两条：所有历史记录的邮箱可以重复，当前正在使用的邮箱全局只能有一条。

把「活跃邮箱」单独抽出来做成可约束的独立数据

这套方案在 MySQL 8.0 上落地非常简单，只要新增一个生成列就行：账号处于活跃状态时，这个字段的值等于 email；账号被软删除之后，它的值自动变成 NULL。唯一索引直接建立在这个生成列上。MySQL 的唯一索引本身就允许多条记录存相同的 NULL，所以多份被删除的账号历史记录能完整留存，当前所有活跃账号的邮箱又会被索引规则严格约束不重复。

ALTER TABLE account
  DROP INDEX uk_account_email,
  ADD COLUMN active_email VARCHAR(255)
    GENERATED ALWAYS AS (
      IF(deleted_at IS NULL, email, NULL)
    ) STORED,
  ADD UNIQUE KEY uk_account_active_email (active_email);

这个设计直接把状态判断逻辑下沉到数据层面，不用在每一处写注册、修改邮箱的接口里重复加查询判断。新增账号的时候数据库会自动计算 active_email 完成校验；软删除账号的时候，生成列会跟着 deleted_at 自动变成 NULL；后续查历史记录的时候，还是可以直接按 email 加时间范围定位到旧数据。

不少开发者一开始会尝试写 UNIQUE(email, deleted_at)，想着账号没删除的时候 deleted_at 都是空，刚好能触发索引冲突。这个思路在 MySQL 里是走不通的：联合唯一键里包含 NULL 字段时，数据库允许多条记录同时出现相同非空字段搭配 NULL 的组合。既可能出现两个同时活跃的账号共用邮箱，也完全表达不准这个场景下的业务边界。

账号删除、重新注册、恢复操作共用同一套校验逻辑

索引规则兜住了最后写入防线，上层接口逻辑也要把不同场景的返回结果理清楚。比如账号正常走软删除流程时只更新状态字段就行；新用户注册直接靠数据库唯一索引做判断；要恢复早年被删除的旧账号时，必须先查一遍对应邮箱有没有被后来的新活跃账号占用。恢复操作不是简单把 deleted_at 改回空值这么随意，很容易刚好触发 uk_account_active_email 索引冲突。

-- 软删除：历史订单仍可关联 account.id
UPDATE account
SET deleted_at = NOW()
WHERE id = 4821 AND deleted_at IS NULL;

-- 恢复前查看活跃占用者；应用层应把冲突提示为可处理的业务状态
SELECT id, email
FROM account
WHERE email = 'lin@example.com'
  AND deleted_at IS NULL
FOR UPDATE;

如果查询到对应邮箱已经有新的活跃账号在用，运营后台要给出「保留现有新账号」「人工核对后合并账号数据」之类的可选操作，不能后台静默覆盖直接删掉新账号。确认没有其他账号占用之后，再操作恢复目标旧账号；哪怕刚好在并发时间窗口里有其他接口插入了同邮箱的新账号，数据库的唯一索引也会直接拒绝第二次写入，应用层捕获重复键报错之后，直接返回「该邮箱已被其他账号使用」的提示就可以。

迁移存量老表的时候，先清理完重复数据再加索引

线上跑了很久的老表基本都有不少脏数据：同一个邮箱被多条未删除的账号记录占用，或是 deleted_at 字段里同时混了 0000-00-00、空字符串之类的异常值。直接加新的唯一索引肯定会执行失败，修复的时候也不能随便挑一条保留直接删其他行。先把所有邮箱重复的活跃账号分组列出来，标记清楚业务主账号、待冻结账号，以及对应的需要保留关联记录的订单范围。

SELECT email, COUNT(*) AS active_count,
       GROUP_CONCAT(id ORDER BY id) AS account_ids
FROM account
WHERE deleted_at IS NULL
GROUP BY email
HAVING COUNT(*) > 1;

别把软删除做成永久堆积的垃圾数据

软删除只是用来满足账号可恢复、操作可审计的需求，不等于所有历史数据都要永远留在主库表里。账号核心表可以长期留存用户身份信息和删除时间，头像、会话、临时验证码、设备登录记录这类增长速度极快的非核心数据，要按设定的保留周期定期清理或者迁移到归档文件夹里。日常查询的默认逻辑也要带上 deleted_at IS NULL 的过滤条件，避免后台列表把已经注销的账号误展示成正常活跃用户。

如果系统用了读写分离架构，账号恢复、新用户注册这类强一致性判断的操作，要走主库查询或是和写入操作放在同一个事务连接里执行。只读节点有数据同步延迟的情况下，「当前没查到邮箱被占用」不代表写入的时候就一定不会冲突，最终的正确性判断必须交给主库的唯一索引来给出确定结果。取舍逻辑很简单：用户体验上可以提前做前置提示，数据正确性必须完全靠写入侧的强约束兜底。

相关问题

生成列一定要用 STORED 类型吗？

这个场景下选 STORED 更合适，把计算出来的活跃邮箱值存成固定的索引目标，后续排查问题的时候直接查列值就能快速定位。如果你想改用虚拟生成列，要结合当前线上用的 MySQL 版本对应的索引支持特性，先在预发布环境验证通过再上线。

直接给邮箱加 is_deleted 字段的联合唯一索引行不行？

如果联合索引设计成 UNIQUE(email, is_deleted)，确实能让一条活跃账号记录和一条删除记录共存，但第二次删除同一个账号的时候就会触发索引冲突，两条删除记录的 is_deleted 完全相同就没法并存。把逻辑抽成生成列转成 NULL 的方案，才更适合需要留存多份历史记录的账号表场景。

恢复账号的时候为什么还要在上层应用提前查一遍？

唯一索引只能在冲突发生的时候直接拒绝写入，不会主动告诉运营人员当前场景下该保留哪一个账号。应用层提前查询就能直接把正在占用该邮箱的账号信息展示出来，给运营提供对应处理选项，索引只负责兜底并发场景下的写入冲突，二者职责完全不一样。

账号删除之后是不是要立刻把邮箱匿名化处理？

完全看你这边的业务数据留存要求。如果需要满足用户删除申请、数据去标识化的合规要求，要先明确哪些字段必须做脱敏处理、订单关联关系怎么正常留存，再对应调整生成列的计算规则和归档流程，不能只靠软删除字段就替代完整的数据治理工作。

把唯一性判断从字段规则升级成状态规则

做账号软删除功能最容易踩的坑，就是还用「邮箱全局永远唯一」的旧规则去处理已经变长了的数据生命周期。把 active_email 设计成仅在账号活跃状态下才会出现的索引值，数据库就能同时兼容历史记录留存、同邮箱新账号注册两个需求。迁移之前先清理存量重复数据、恢复操作之前先展示冲突分支、写入侧全程靠唯一索引兜底，整套逻辑跑顺之后，账号软删除就不会变成拖垮注册流程的特殊边缘场景。