用生成列加唯一索引实现软删除下的唯一约束

软删除是业务表的常见设计：加一个 deleted_at 或 is_deleted 字段标记删除，查询时过滤。真删除会绕过这个设计，所以大部分团队都会遇到同一个问题——原本在 email、username、order_no 这类字段上的唯一索引，在软删除之后不再”正确”：删除一条记录后再次注册同一个邮箱会撞唯一键，报 Duplicate entry 'a@b.com' for key 'uk_email'，业务上却明明应该允许。

常见的绕过手段有三种：一是在应用层先查再插（并发下必然漏判）；二是把 email 改成 concat(email, '_', id) 之类的脏写法（污染数据、查询还得截断）；三是干脆去掉唯一索引（数据质量失控）。这三种都不理想。MySQL 5.7 引入的生成列（generated column）给出了一条干净的路子：把唯一性建立在”仅对未删除行生效”的派生值上，让已删除行自动”让出”唯一键。下面从机制讲起，给出可直接落库的 DDL、配套的 Java 与 MyBatis 写法、边界情况与踩坑记录。

问题根源：唯一索引不知道你在做软删除

先看一个典型表：

CREATE TABLE `sys_user` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email`      VARCHAR(128)    NOT NULL,
  `nickname`   VARCHAR(64)     NOT NULL DEFAULT '',
  `deleted_at` DATETIME(3)     NULL DEFAULT NULL,
  `created_at` DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

软删除执行的是 UPDATE sys_user SET deleted_at = NOW(3) WHERE id = 1，行还在表里，uk_email 依然占位。再插入相同 email 时的报错非常明确：

ERROR 1062 (23000): Duplicate entry ‘alice@example.com’ for key ‘sys_user.uk_email’

关键点在于：唯一索引只看索引列的值，它无法表达”仅当 deleted_at IS NULL 时唯一”这个条件。MySQL 不支持部分索引（partial index，PostgreSQL 有 WHERE 子句），所以必须把”条件”物化进列值本身——这正是生成列的用武之地。

思路：把删除状态编码进一个派生列

生成列的值由表达式在写入/读取时自动算出，不能直接 INSERT/UPDATE（除非 STORED 且显式指定 DEFAULT，MySQL 也不允许）。我们让这个派生列满足两点：

• 未删除时，值等于业务键（email），从而参与唯一性判定；
• 已删除时，值为 NULL。

MySQL 的 InnoDB 唯一索引对 NULL 的处理是：允许多个 NULL 并存（因为 NULL <> NULL）。于是已删除的行在唯一索引里互相不冲突，天然满足”软删除后可以复用邮箱”。

表达式写出来就是：

CASE WHEN deleted_at IS NULL THEN email ELSE NULL END

注意生成列表达式的限制：只能用确定性函数（deterministic），不能引用其他表、不能用子查询、不能用非确定函数如 NOW()、UUID()、RAND()。任何对当前时间的依赖都会导致建表失败：

ERROR 3105 (HY000): The value specified for generated column ‘uk_email_active’ in table ‘sys_user’ is not allowed.

或者更直白的：

ERROR 3763 (HY000): Expression of generated column ‘uk_email_active’ contains a disallowed function: now.

落地 DDL：一次建对，注意 STORED 与索引

两种常见形态，推荐第二种（列类型显式收窄，语义清晰）。

形态一：布尔标记 + 业务键

ALTER TABLE `sys_user`
  ADD COLUMN `deleted_flag` TINYINT
    GENERATED ALWAYS AS (CASE WHEN `deleted_at` IS NULL THEN NULL ELSE 1 END) VIRTUAL,
  ADD UNIQUE KEY `uk_email_active` (`email`, `deleted_flag`);

这种写法依赖”未删除时 deleted_flag 为 NULL，唯一索引中任列为 NULL 即整体视为不冲突”这一行为。它在 MySQL 中确实成立（复合唯一索引任意一列为 NULL 就允许多行），但可读性差，后人容易误解，而且一旦哪天有人把 deleted_flag 的表达式改成 IF(deleted_at IS NULL,0,1)，唯一性立刻全盘失效且没有任何报错。不建议在生产使用。

形态二：直接物化”活跃键”（推荐）

ALTER TABLE `sys_user`
  ADD COLUMN `active_email` VARCHAR(128)
    GENERATED ALWAYS AS (
      CASE WHEN `deleted_at` IS NULL THEN `email` ELSE NULL END
    ) STORED,
  ADD UNIQUE KEY `uk_active_email` (`active_email`);

说明几点：

• STORED 还是 VIRTUAL：5.7.8+ 支持在 VIRTUAL 生成列上建索引（二级索引），因此两者都可行。区别在于 VIRTUAL 不占行存空间、读时计算，写索引时需要现算；STORED 占空间但写入时就已落盘，索引维护更直接。对这种”短字符串 + 高频唯一校验”的场景两者性能差异可忽略，优先 STORED，因为它在 EXPLAIN 与备份恢复、在线 DDL 变更时的行为更可预期，并且可以在此基础上再加普通索引做查询前缀。若表极大且磁盘紧张再用 VIRTUAL。
• 类型必须匹配：active_email 声明为 VARCHAR(128) 与 email 完全一致，避免字符集/长度不一致导致隐式转换，进而让唯一索引比较走成”看起来能建但判定异常”。生成列会继承表达式结果的排序规则，显式写全类型最稳。
• DDL 期间锁：ALTER TABLE ... ADD COLUMN ... , ADD UNIQUE KEY 在 InnoDB 上通常走 ALGORITHM=INPLACE, LOCK=NONE（加 STORED 生成列需要重建表数据时可能要 COPY）。大表先执行 ALTER TABLE sys_user ADD COLUMN ... , ALGORITHM=INPLACE, LOCK=NONE 试探，失败再评估 pt-online-schema-change 或 gh-ost。

建完后验证：

SHOW CREATE TABLE sys_user\G
-- 关键行应包含：
-- `active_email` varchar(128) GENERATED ALWAYS AS ((case when (`deleted_at` is null) then `email` else NULL end)) STORED
-- UNIQUE KEY `uk_active_email` (`active_email`)

验证行为：插入、软删、复用、并发

准备两条数据并做完整回归：

-- 1) 首次注册成功
INSERT INTO sys_user (email, nickname) VALUES ('alice@example.com', 'Alice');
-- Query OK, 1 row affected

-- 2) 未删除时重复注册，仍应被拦截
INSERT INTO sys_user (email, nickname) VALUES ('alice@example.com', 'Alice2');
-- ERROR 1062 (23000): Duplicate entry 'alice@example.com' for key 'sys_user.uk_active_email'

-- 3) 软删除
UPDATE sys_user SET deleted_at = NOW(3) WHERE email = 'alice@example.com';
-- Query OK, 1 row affected
SELECT id, email, deleted_at, active_email FROM sys_user;
-- active_email 此时为 NULL

-- 4) 复用同一邮箱，应成功
INSERT INTO sys_user (email, nickname) VALUES ('alice@example.com', 'Alice3');
-- Query OK, 1 row affected

-- 5) 重复软删除多条同邮箱，验证多个 NULL 不冲突
UPDATE sys_user SET deleted_at = NOW(3) WHERE email = 'alice@example.com' AND deleted_at IS NULL;
INSERT INTO sys_user (email, nickname) VALUES ('alice@example.com', 'Alice4');
-- Query OK, 1 row affected  （表中已有 2 行 active_email = NULL）

第 5 步是这套方案最容易被质疑的地方——多个 NULL 会不会冲突？答案是 InnoDB 唯一索引允许多个 NULL，验证方式也很直接：SELECT COUNT(*) FROM sys_user WHERE active_email IS NULL; 返回 2 即为预期。

并发下的表现

唯一约束的判定最终落在 InnoDB 的索引上，因此它和普通唯一索引一样具备原子性：两个并发事务同时插入 alice@example.com，其中一个会拿到 Duplicate entry，绝不会两个都成功。这一点是”应用层先查再插”永远做不到的。需要提醒的是不要依赖 INSERT ... ON DUPLICATE KEY UPDATE 做”软删后复活”：由于已删除行的 active_email 为 NULL，它不会与待插入的 email 冲突，ON DUPLICATE KEY UPDATE 会直接插入新行而不是更新旧行。若业务要求”复活旧账号”，应当走显式查询 + UPDATE ... SET deleted_at = NULL WHERE id = ? 的路径，并单独处理旧行复活时再次撞唯一键的情况。

Java / MyBatis 侧怎么配合

生成列不参与写入，这一点必须让 ORM 明确知道，否则会出现在 INSERT 里带上 active_email 的 SQL，MySQL 直接拒绝：

ERROR 3105 (HY000): The value specified for generated column ‘active_email’ in table ‘sys_user’ is not allowed.

JPA 用 @Generated 或 insertable = false, updatable = false：

import javax.persistence.*;
import java.time.LocalDateTime;

@Entity
@Table(name = "sys_user",
       uniqueConstraints = @UniqueConstraint(name = "uk_active_email", columnNames = "active_email"))
public class SysUser {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, length = 128)
    private String email;

    @Column(nullable = false, length = 64)
    private String nickname;

    @Column(name = "deleted_at")
    private LocalDateTime deletedAt;

    // 生成列：永不由应用写入
    @Column(name = "active_email", insertable = false, updatable = false)
    private String activeEmail;

    public void softDelete() {
        this.deletedAt = LocalDateTime.now();
    }

    public boolean isDeleted() {
        return deletedAt != null;
    }

    // getters / setters 省略
}

MyBatis-Plus 场景更简单，用 @TableField(exist = false) 让生成列完全不进 SQL，或 insertStrategy = FieldStrategy.NEVER：

import com.baomidou.mybatisplus.annotation.*;

@TableName("sys_user")
public class SysUser {

    @TableId(type = IdType.AUTO)
    private Long id;

    private String email;

    private String nickname;

    @TableField("deleted_at")
    private java.time.LocalDateTime deletedAt;

    @TableField(value = "active_email", insertStrategy = FieldStrategy.NEVER,
                updateStrategy = FieldStrategy.NEVER)
    private String activeEmail;

    // getters / setters 省略
}

如果用的是纯 MyBatis XML，只要 INSERT 列清单里不写 active_email 即可；resultMap 里可以映射它用于查询展示。捕获冲突异常时按唯一键名区分业务含义：

import java.sql.SQLIntegrityConstraintViolationException;

public void register(String email, String nickname) {
    SysUser u = new SysUser();
    u.setEmail(email);
    u.setNickname(nickname);
    try {
        sysUserMapper.insert(u);
    } catch (org.springframework.dao.DuplicateKeyException e) {
        Throwable root = e.getMostSpecificCause();
        String msg = root.getMessage() == null ? "" : root.getMessage();
        if (msg.contains("uk_active_email")) {
            throw new IllegalStateException("该邮箱已被注册");
        }
        throw e;
    }
}

Spring 会把 MySQL 的 1062 翻译成 DuplicateKeyException，用 getMostSpecificCause() 拿到原生消息再按索引名判断，比字符串全匹配健壮一些。

查询与索引维护的注意事项

建了 uk_active_email 之后，原来 uk_email 需要评估是否可以删掉。如果业务里存在”按 email 精确查询包含已删除数据”的需求（比如后台客服排查），uk_active_email 无法覆盖 deleted_at IS NOT NULL 的行，因为这些行的值都是 NULL。此时应：

• 保留一个普通索引 KEY idx_email (email)，而不是唯一索引；
• 日常查询统一加 deleted_at IS NULL 条件，命中 uk_active_email；
• 若 active_email 是 VIRTUAL 列，注意它不能作为主键、也不能被用作外键约束；
• 物理删除（purge）任务可以安全执行：删除已删除行不会影响其他行的唯一性。

另一个高频问题是字符集与大小写。utf8mb4_0900_ai_ci 是大小写不敏感排序规则，Alice@Example.com 与 alice@example.com 会被判定重复——这通常正是注册系统想要的。但如果你需要大小写敏感的唯一性（例如某些 token 字段），要在生成列表达式里显式加 BINARY，或让列使用 utf8mb4_0900_as_cs。测试方法：

INSERT INTO sys_user (email, nickname) VALUES ('Alice@Example.com', 'A');
-- 在 ai_ci 下，若已存在 alice@example.com 的活跃行，这里会报 1062

踩坑实录：几个真实报错

报错信息	原因	解决办法
ERROR 3105: The value specified for generated column ‘active_email’ … is not allowed	INSERT/UPDATE 语句显式写了生成列	ORM 标记 insertable=false / updateStrategy=NEVER，或改 SQL 列清单
ERROR 3763: … contains a disallowed function: now	生成列表达式引用了非确定函数	表达式只依赖行内已有列，删除时间用普通列存储
ERROR 1215: Cannot add foreign key constraint	外键引用了 VIRTUAL 生成列，或与 STORED 列类型不一致	外键指向原始业务列，不要指向生成列
ERROR 1062: Duplicate entry ‘b@x.com’ for key ‘sys_user.uk_active_email’	软删除未真正写入 deleted_at（常见于逻辑删除插件只改了标志位未改时间）	确认软删除字段与生成列表达式用的字段是同一个，且确实被更新
ERROR 1846: The used table type doesn’t support FULLTEXT indexes / generated columns	误用了 MyISAM 引擎	改 ENGINE=InnoDB；MyISAM 不支持生成列的索引语义

还有一个不报错但更隐蔽的坑：物理删除后重建。如果 purge 任务把已删除行物理删除，再插入同一 email，本来没问题；但如果某次批量修复脚本直接 UPDATE sys_user SET deleted_at = NULL 把多条历史删除行”批量复活”，同一 email 会瞬间产生多个活跃行，唯一索引会让这条 UPDATE 整体失败并回滚：

ERROR 1062 (23000): Duplicate entry ‘alice@example.com’ for key ‘sys_user.uk_active_email’

这其实是好事——它把数据不一致暴露在修复的时刻而不是之后。处理方式是按 email 分组只复活 id 最大的一条，其余保持删除状态。

小结

软删除下的唯一约束有三种选择：去掉约束（不可接受）、应用层校验（并发不可靠）、把约束下沉到数据库。生成列方案属于第三种，核心只有一行表达式 CASE WHEN deleted_at IS NULL THEN email ELSE NULL END，利用 InnoDB 唯一索引对多个 NULL 的宽容行为，让已删除行自动退出唯一性判定。它兼容 5.7.8 及以上版本，DDL 可以通过 INPLACE 在线执行，写入侧只需在 ORM 上做一次 insertable = false 的适配，是当前 MySQL 技术栈里成本最低、语义最清晰的解法。唯一需要持续的纪律是：软删除必须真的写时间字段，且不要用 ON DUPLICATE KEY UPDATE 去做”复活”。

FAQ

Q1：为什么不直接用 UNIQUE(email, deleted_at)？

因为 MySQL 的唯一索引允许多个 NULL，而 deleted_at 在未删除时正是 NULL。UNIQUE(email, deleted_at) 会导致未删除的相同 email 全部判为不冲突，唯一性彻底失效——方向正好相反。这个组合只适合”删除时间非空且每次删除时间不同”的极少数场景，不建议使用。

Q2：生成列能否只建 VIRTUAL 不 STORED，索引会不会失效？

不会。MySQL 5.7.8 起支持在 VIRTUAL 生成列上建二级索引，唯一索引同样支持。区别只是索引里存的值在写入时现算，不额外占行存空间。若要把它当普通列频繁做范围查询或参与排序，STORED 更稳。

Q3：已有大量数据的表怎么迁移，会锁表吗？

加 STORED 生成列需要回填数据，MySQL 可能选择 COPY 算法而阻塞写入。建议：先在从库或影子表验证；主库执行时加 ALGORITHM=INPLACE, LOCK=NONE 让 MySQL 明确拒绝不可行的方案；对大表使用 gh-ost / pt-online-schema-change 在线变更，变更完成后再单独加唯一索引（ADD UNIQUE KEY 本身通常是 INPLACE 的）。注意在线工具对生成列的兼容性要提前测。

Q4：软删除字段是 is_deleted TINYINT(1) 而不是时间戳，能用吗？

能，把表达式换成 CASE WHEN is_deleted = 0 THEN email ELSE NULL END 即可。但布尔字段无法记录删除时间，审计能力弱；若已有 deleted_at，优先用它作为唯一事实来源，避免两个删除标记不一致。

Q5：一张表有多个需要唯一约束的业务键怎么办？

每个键配一个生成列与一个唯一索引，例如 active_email、active_phone、active_order_no，表达式结构完全一致。索引数量增加会带来写放大，需评估写入 QPS；一般 2~3 个唯一键是常见规模，超过 5 个就要考虑是否部分字段改由应用层保证。

Q6：这方案和 PostgreSQL 的 partial unique index 比如何？

语义等价，实现方式不同。PG 的 CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL 更直观、无需多余列；MySQL 没有这个能力，生成列是把 WHERE 条件编码进列值的最小代价方案。迁移数据库时这一段需要重写，但业务层的唯一性判定代码可以保持不变。