引言

本地的设计来自于群里的一个小伙伴的提问：

A: 请教一下，关于软删除，删除后新增同样的数据报 UNIQUE 索引冲突的问题，大家有没有什么好办法处理？
B: 重建唯一索引，原来字段和软删除时间戳或状态建立唯一索引
A: 恩，目前就是这样做的，看来只能重建索引了

看到这个报错，“老司机”应该会心一笑，因为有这部分开发经验的同学应该经常遇到这个问题，而我对于上面 B 提到的使用 原字段 和 状态 建立唯一索引的方法更是觉得不妥，这几乎是每一位后端开发者在进阶路上都会踩到的坑——软删除（Soft Delete）与唯一索引（Unique Index）的冲突。

在现代 Web 开发中，为了数据安全、审计追踪以及防止手误，我们很少直接从数据库中 DELETE 物理删除数据，而是习惯使用一个标记位（如 is_deleted）来做“软删除”。然而，当业务需求遇上“用户名全局唯一”、“手机号全局唯一”这类硬性指标时，软删除和数据库层面的唯一约束就会发生剧烈的化学反应。

今天，我们就来聊聊这个经典话题：在软删除模式下，如何优雅地设计唯一索引？

软删除状态标记会出现什么问题？

下面均以 username 作为唯一字段

首先，如果单独给 username加唯一索引肯定不行，因为软删除的情况下，用户名还在，如果删除的用户名再次添加，就会报错。可能会出现后台查不到，用户也用不了的情况。既然单纯索引 username 不行，那把删除状态也加进去呢？

  • 做法：建立联合索引 UNIQUE KEY (username, is_deleted)。
  • 结果：
    • ('Alice', 0) 和 ('Alice', 1) 可以共存。看似解决了问题。
  • 致命缺陷：
    • 如果 Alice 注销（变成 1），重新注册（变成 0），再次注销（变成 1）……
    • 第二次注销时，数据库里将会有两条 ('Alice', 1)。
    • 报错！ 唯一索引再次生效。这意味着一个用户名只能被“软删除”一次。这显然不符合现实业务需求。

时间戳

然后就有人会很容易想到使用时间作为删除的标记，那么因为每次删除的时间大概率不一样，所以使用删除时间和用户名作为联合唯一索引的话就能解决这个问题。不过坑点也有，那就是 delete_at 初始值不能设置为 NULL，因为 MySQL 对于带有 NULL 值的联合索引是有特别处理的，在 5.7 里面两个相同用户名均为 NULL 删除时间的情况是允许存在的。这也是为什么想要写这篇博客最大的原因，因为这个 bug 可能存在但可能一直不会被发现。而且我还遇到过，因为数据库的限制往往是最后一道防线，是为了避免并发条件下不会出现脏数据而设计的。而前置业务代码中肯定也会做用户名唯一性的校验和判断，所以大多数情况下会测不到，特别是注册还需要验证什么的。只有大量并发的时候才会出现。

  • 做法：
    • 字段改为 delete_at (建议用 BigInt)。
    • 未删除：默认为 0（这也是关键点，尽量避免使用 NULL）。
    • 已删除：填入当前的毫秒级时间戳。
    • 索引：UNIQUE KEY (username, delete_at)。
  • 演练：
    1. Alice 注册：('Alice', 0)。 -> 成功
    2. Alice 注销：更新为 ('Alice', 1678888888)。 -> 成功
    3. Alice 重新注册：插入 ('Alice', 0)。 -> 成功（因为 0 != 1678888888）
    4. Alice 再次注销：更新为 ('Alice', 1679999999)。 -> 成功（时间戳不同）
  • 评价：简单、有效、能保留完整的删除历史。适合绝大多数业务场景。

归档表

当数据量极大，或者“历史数据”完全不需要参与日常业务查询时，物理隔离也是一种的选择。我曾经就也遇到过这样的情况，然后后续发现业务调整这部分的删除数据直接转移到另一张归档表中，一方面这部分数据可以被直接减少，极大的可以减轻主表的压力，而且这部分的归档数据往往只有在特殊情况下才会被审计，通常情况下可能永远就用不到了。

  • 做法：
    • users 表：只存活跃用户。直接加 UNIQUE KEY (username)。
    • users_history 表：存删除的用户，不加唯一约束。
    • 删除动作（只是做一个样例，实际可能是在业务代码中实现的）：
      1
      2
      3
      4
      START TRANSACTION;
      INSERT INTO users_history SELECT * FROM users WHERE id = 1;
      DELETE FROM users WHERE id = 1;
      COMMIT;
  • 评价：主表极度瘦身，查询飞快。但代价是“删除”操作变重了，且如果要恢复数据（Undo），逻辑会比较麻烦。（当前这种恢复操作可能这辈子出现不了一次）

修改唯一字段

其实还有一种业务上也会用到的方法，那就是直接修改唯一字段，让唯一字段永远唯一。比如删除的时候，在软删除的基础之上，直接修改用户名，在用户名后面拼接上一个时间戳，这样也可以保证用户名的唯一，不过这也是建立在以后不需要查询删除和恢复数据的基础上。因为处理简单，所以也会被使用到。只要业务上允许就可以。

总结

之前我也会觉得一个软删除能有什么？但是实际中其实这种小的坑还是特别多的。而且特别是在国内的环境上，要求每个数据不能被永久删除，万一审计亦或是安全审查核对的时候，还有可能是排查意外问题等等情况下，所以在软删除被更多用到的时候，这样的问题就会容易出现。而正因为有朋友在群里面问了，我就发现这种坑特别容易坑到人。当然解决方案多种多样，我们只要选择自己合适的就好。