有没有遇到这种场景用户在系统里注册过一个手机号后来注销走了你给用户表做了逻辑删除deleted 字段置为 1。隔了半年又一个新用户拿同一个手机号来注册结果插入失败报个 “Duplicate entry”怎么想都不对——数据库里明明没有这条“活”数据了为什么唯一索引还拦着我这就是逻辑删除和唯一索引的经典冲突。近两年我在好几个项目里都踩过这个坑每次都要把表结构、历史数据翻出来折腾一遍。今天就把这个问题的完整解决方案、我踩过的坑、以及不同业务场景下的选型思路一次性说清楚。先说结论冲突的根源在于逻辑删除的记录还占着唯一索引的位置导致新插入的数据无法复用原来的唯一键。解决办法要么让唯一索引对“已删除”的记录失效要么让已删除记录在唯一索引里不冲突。具体怎么做下面分别拆解。1. 问题重现逻辑删除和唯一索引是怎么打起来的先把这个场景说透。你有一张用户表手机号做了唯一索引防止同一个人注册多个账号。表结构大致长这样CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT, phone VARCHAR(20) NOT NULL, deleted TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;业务上用户申请注销你执行的是“软删除”UPDATE user SET deleted 1 WHERE id 123;注意此时数据还在表里phone 字段值还是原来的 13800138000唯一索引uk_phone上的记录也没消失。过段时间另一个用户想用同一手机号注册执行INSERT INTO user (phone, deleted) VALUES (13800138000, 0);数据库直接告诉你Duplicate entry 13800138000 for key uk_phone。从业务视角看这条数据已经被删了应该允许复用从数据库视角看这个唯一键还存在你不该插重复值。用户的预期和数据的存储状态不一致这就是冲突的核心。类似场景非常多用户名、身份证号、邮箱、订单号、仓库编码、关联设备ID凡是带“唯一”校验的字段只要做了逻辑删除都会遇到这个问题。可能有人会说那我不用逻辑删除直接物理删掉不就行了但现实中很多业务不允许物理删除要留审计记录、要避免外键关联断裂、要从回收站恢复数据。所以逻辑删除本身没有问题问题在于你没有处理好它和唯一索引的共存关系。2. 方案一复合唯一索引——最常用的处理方式最自然的想法既然唯一索引只针对 phone 一个字段那我把 deleted 也加进唯一索引里变成联合唯一键这样同一个 phone一条记录 deleted0另一条 deleted1在唯一索引里是不同的值就不冲突了。具体做法ALTER TABLE user DROP INDEX uk_phone; ALTER TABLE user ADD UNIQUE KEY uk_phone_deleted (phone, deleted);插入和删除操作不变。新注册用户插入 (phone13800138000, deleted0)已注销用户是 (phone13800138000, deleted1)。两者在唯一索引中不重复插入成功。这个方案看起来很简单但有一个致命细节deleted 字段如果只存 0 和 1那同一条数据只能被删除一次。比如第一个用户注销后是 (phone13800138000, deleted1)。业务上如果支持“注销后重新注册再再次注销”这条记录就要从 deleted1 变成 deleted0再变成 deleted1此时表中已经有一条 deleted1 的记录了你再次 UPDATE 时会直接报唯一键冲突甚至更早——刚把 deleted1 改回 deleted0 时如果已经有一个新用户占用 phone13800138000 且 deleted0根本改不过去。所以在实际项目里我基本不会用 0/1 这样的值。我采用的是让 deleted 存主键 ID或者存一个时间戳。删除时这样更新UPDATE user SET deleted id WHERE id 123;这样每条已删除记录的 deleted 都对应自己的主键 id不同记录之间一定不会重复复合唯一键 (phone, deleted) 也一定不会因重复删除而冲突。新插入记录时 deleted0和已有的任何 deleted 值都不冲突。这个方案的改动量很小查询语句几乎不需要变只要记住所有业务查询都要带上 deleted0 条件即可。另外如果将来要做数据恢复也能清楚地知道这条记录原来对应的 ID。3. 方案二删除标记填充时间戳或UUID——更稳妥的变体用主键 id 填充 deleted有个小限制deleted 字段类型需要和 id 保持一致。如果 deleted 是 tinyint那就装不下 bigint 的主键。很多老表里 deleted 字段从一开始就设计成了TINYINT这时候再改类型可能涉及存储引擎、应用代码的兼容风险不小。另一种解法也常用删除时把 deleted 字段更新成一个唯一的时间戳或 UUID。时间戳可以精确到微秒或者直接用REPLACE(UUID(), -, )生成一串不重复的字符串。表结构调整成CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT, phone VARCHAR(20) NOT NULL, deleted VARCHAR(32) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uk_phone_deleted (phone, deleted) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;删除时UPDATE user SET deleted REPLACE(UUID(), -, ) WHERE id 123;插入时 deleted 默认为 0未删除的记录唯一索引键是 (phone, 0)已删除的记录是 (phone, 随机串)。理论上对同一个手机号可以无限次删除再注册因为每次删除生成的 UUID 都不同。这个方案的优点是通用性强不管什么业务场景都可以用不会出现 0/1 那种“只允许删一次”的问题。缺点是 deleted 字段会从 1 字节膨胀到 32 字节表体积略有增长以及删除操作的成本比简单的 UPDATE 稍高一点。但在绝大多数业务系统里这点性能差异可以忽略。我在一个社区项目里用的就是 UUID 方案因为用户昵称要求唯一用户可以多次改名、注销、重新注册用 UUID 填充最省心不用担心生成的值在当前数据里撞车。4. 方案三部分索引和生成列——更精巧的数据库特性前面两种方案本质上都是通过“制造不同值”来绕过唯一索引冲突。还有一类思路是“让唯一索引只约束未删除的记录”已删除的记录根本不进入索引。这样就不需要去改 deleted 的值了业务上直观得多。4.1 PostgreSQL 的部分索引PostgreSQL 原生支持部分索引Partial Index它是最贴切的做法CREATE UNIQUE INDEX uk_phone_active ON user (phone) WHERE deleted 0;这个索引只包含未删除的数据已删除的数据不进入唯一索引所以同一手机号可以存在多条 deleted1 的记录。插入新用户时只要当前没有 deleted0 的同手机号记录就能成功。这种方式代码改得最少索引体积也小查询未删除数据时还能命中索引性能很好。缺点是它只在 PostgreSQL 里原生可用在 MySQL、SQL Server 里没有直接的等价物。4.2 MySQL 的生成列思路MySQL 不支持部分索引但我们可以用生成列Generated Column加一个“可空唯一列”来模拟类似效果。思路是这样的新增一列active_phone当 deleted0 时它的值等于 phone 本身当 deleted1 时它的值为 NULL。然后在active_phone上建唯一索引。MySQL 的唯一索引对 NULL 有特殊宽容NULL 值之间可以重复不会触发唯一冲突。表结构大概是CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT, phone VARCHAR(20) NOT NULL, deleted TINYINT NOT NULL DEFAULT 0, active_phone VARCHAR(20) GENERATED ALWAYS AS ( IF(deleted 0, phone, NULL) ) STORED, PRIMARY KEY (id), UNIQUE KEY uk_active_phone (active_phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;之后插入两条 deleted1、phone 相同的记录它们的 active_phone 都是 NULL唯一索引不冲突。插入一条 deleted0、phone 相同的记录时只要当前没有另一条未删除的同号记录就不冲突。这里我特意用了 STORED 生成列因为要在它上面建索引MySQL 对索引生成列的要求是必须可存储。如果业务需求不想增加物理存储也可以用虚拟列但虚拟列无法直接建唯一索引所以实用性受限。这个方案的优点是应用代码改动极少插入和删除 SQL 基本不用动缺点是生成列会占额外存储表结构变更时会重建表数据和索引量大时耗时较长最好在低峰期操作。5. 方案四应用层检查 兜底策略——临时救急方案有时候表结构不好改、DBA 审批流程很长或者业务紧急要求先上线这时会有人想我在应用层先查一下有没有未删除的同手机号记录没有再插入不就行了从原理上这确实能解决一部分问题但它天然存在并发漏洞。两个请求同时进来都查询到“没有未删除的同手机号记录”然后都执行 INSERT其中一个就会被唯一索引拦住。如果没有唯一索引兜底甚至会出现两条“活”数据这是更严重的脏数据。所以应用层检查只能作为一个辅助过滤不能作为唯一保障。如果你决定用它必须同时满足两个条件唯一索引依然要存在作为终极防线。插入时要捕获数据库的唯一键冲突异常比如 MySQL 的DuplicateKeyException遇到冲突时返回“已存在”提示而不是把异常直接抛给用户。具体伪代码长这样try { // 查询当前是否存在未删除记录 if (userMapper.countByPhoneAndDeleted(phone, 0) 0) { return 该手机号已被注册; } userMapper.insert(new User(phone)); return 注册成功; } catch (DuplicateKeyException e) { return 该手机号已被注册; }之所以说它是“临时救急方案”是因为它把数据完整性依赖于应用层逻辑而不是数据库约束。一旦代码里某个分支忘记检查、或者事务边界没控制好脏数据的风险就会上升。对于复杂的分布式系统如果还要做多实例部署应用层检查就更不可靠了必须引入分布式锁才能勉强保证安全。如果你现在正在赶工期来不及改表结构可以先这么顶着但后续一定要回到前三种方案中的任何一种把防线落在数据库上。6. 方案对比与选型一张表看清利弊每次我给别人讲这几个方案最常被问的一句话是“到底用哪种”这里直接给出我自己的选择逻辑。方案核心思路优点缺点适用场景复合唯一索引deleted 存 id/UUID让唯一索引中的 deleted 值各不相同通用性强MySQL、PostgreSQL 都能用语义清晰删除操作要改 deleted 值deleted 字段可能变大大多数中小型业务系统推荐优先考虑0/1 复合唯一索引简单把 deleted 加进唯一索引改动最小一条记录只能删除一次再次删除会冲突仅适用于同一手机号/用户名只会出现一次的极简场景PostgreSQL 部分索引索引只包含未删除记录最符合直觉索引体积小代码改动最少仅 PostgreSQL 原生支持使用 PostgreSQL 的新项目MySQL 生成列已删除记录对应列为 NULL不依赖特定数据库版本修改代码少增加存储开销重建表耗时MySQL 5.7 且表数据量不是特别大的场景应用层检查兜底业务代码过滤 异常捕获不动表结构上线快并发下有漏洞依赖代码完整性临时方案或唯一约束不是硬性诉求的场景选型上我个人的经验顺序是如果项目用 PostgreSQL优先考虑部分索引没有之一它就是为这种需求设计的。如果项目用 MySQL且主键是 bigint那么复合唯一索引 deleted 存主键 id 是最省事的方案改动小、思维负担低。如果 deleted 字段已经是字符串类型或者删除频率很高就用 UUID 填充方案。如果表里有大量历史已删除数据又不想迁移修改 deleted 的值那 MySQL 生成列方案更合适因为它不需要回填历史数据。7. 实操记录我在实名认证模块里的完整修复案例空讲理论容易飘我拿一个真实项目里的修复过程做个复盘。背景很简单用户 APP 里做实名认证一张user_identity表身份证号做了唯一索引用户注销后走软删除。上线半年后客服反馈有用户重新注册时提示“身份证已存在”查出来的原因就是逻辑删除和唯一索引冲突。当时的表长这样CREATE TABLE user_identity ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, id_card VARCHAR(18) NOT NULL, deleted TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uk_id_card (id_card) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这个场景有个特殊性一个身份证号理论上应该只对应一个真实自然人的账号体系但允许删除后重新绑定到新账号。所以不能简单用 0/1 复合索引。我最终选的是复合唯一索引方案deleted 字段改成存主键 id。具体步骤第一步修改表结构。把 deleted 从 tinyint 改成 bigint默认值保持 0并把唯一索引从 (id_card) 改成 (id_card, deleted)。ALTER TABLE user_identity MODIFY COLUMN deleted BIGINT NOT NULL DEFAULT 0, DROP INDEX uk_id_card, ADD UNIQUE KEY uk_id_card_deleted (id_card, deleted);第二步回填存量已删除数据。先看有哪些已删除的数据把它们的 deleted 字段改成主键 id避免存量数据自身就撞索引UPDATE user_identity SET deleted id WHERE deleted 1;执行前一定要先确认数据量我当时是几万条已删除记录直接 UPDATE 很快就完成了。如果表特别大建议分批更新锁范围小一些。第三步修改删除业务代码。将原来“注销”逻辑里的直接置 1改成置为主键 id// 原来 identity.setDeleted(1); identityMapper.updateById(identity); // 改为 identityMapper.updateDeletedToId(identity.getId());对应的 SQL 是UPDATE user_identity SET deleted id WHERE id #{id};这里有个经验直接在 SQL 里写deleted id比应用层先查 id 再拼接 UPDATE 语句更稳定还能省一次查询。第四步检查新增和查询逻辑。所有业务查询都要求加上deleted 0条件。我的 MAPPER 里的 SQL 全部使用统一的TableLogic扫描软删除条件MyBatis-Plus 默认会拼上这点当时省了不少事。第五步上线前跑回归测试。我重点测了这几个场景新用户正常注册手机号/身份证号不存在插入成功用户注销后再注册同一个号先删除再插入成功同一账号重复注销两次第二次删除时 deleted 已经是 id再次 UPDATE 不会冲突两个不同账号同时用一个手机号注册模拟并发 INSERT只有一个成功另一个拿到 DuplicateKeyException接口返回友好提示。整个改动从评估到上线大约花了一个工作日核心就是表结构调整和删除逻辑的小改动没有动到查询接口的对外协议风险可控。8. 常见问题与排查技巧实录这类问题在实际排查时总会碰到几个非典型情况。我整理几个高频问题给出排查思路顺手把我试过的“偏方”也列出来。问题一逻辑删除后重新插入相同数据依然报 Duplicate entry。这种情况十有八九是唯一索引没有调整到位。排查步骤用SHOW INDEX FROM 表名查看当前索引确认唯一索引是否包含了 deleted 字段。看 deleted 字段的值如果已删除记录的 deleted 还是 0 或 1说明删除逻辑没有把 deleted 改成 id/时间戳/UUID。如果删除逻辑明明已经更新了 deleted插入时还是冲突那就要查是不是还有其他未被发现的标准唯一索引比如之前项目里建过uk_phone_deleted后又保留了uk_phone没删两个索引同时存在后者依然会拦截新的插入。问题二复合唯一索引下同一记录删除第二次报错。这个就是典型的 0/1 复合索引问题。如果删过一次的记录 deleted1第二次删除前要先 UPDATE 到 deleted0而表里可能已经存在 deleted0 的同号记录于是更新失败。解决办法就是把 deleted 改成唯一值不要再重复用 0/1。问题三已删除的历史数据存量导致新索引建不上去。修改表结构时如果表里已经有重复的 (id_card, deleted) 组合ADD UNIQUE KEY 会直接失败。比如已删数据有两条 id_card 相同、deleted1 的记录它们俩在复合唯一索引里也重复所以必须先把这些历史数据的 deleted 值改成不同的值。我常用的一种原子化操作是先加一个普通索引再分步把 deleted 改成 id最后再改成唯一索引。虽然慢一点但不会卡死。问题四逻辑删除字段为 NULL 时唯一索引失效。很多同学喜欢把删除标记设计成 NULL 表示未删除非 NULL 表示已删除。这里我提醒一下如果 deleted 是 NULL而唯一索引又建在 (phone, deleted) 上MySQL 会认为所有 (phone, NULL) 都不重复于是同一手机号可以插入多条 deletedNULL 的记录唯一约束就彻底没用了。所以字段默认值一定要给 0不要用 NULL 表示“正常”。问题五如何批量检查线上库有没有隐藏的重复数据。可以用这条 SQL 快速找出疑似重复SELECT id_card, COUNT(*) FROM user_identity WHERE deleted 0 GROUP BY id_card HAVING COUNT(*) 1;如果查出结果说明同一批未删除数据存在两个同身份证号的记录这往往是早期没有唯一索引兜底时留下的坑需要人工介入确认保留哪条。这里有个“删除并保留一条”的通用处理思路先选出最小的 id其余逻辑删除再回填 deletedid这样后续新数据插入不会再冲突。9. 给项目组的落地建议如果你正在设计新表提前把逻辑删除和唯一索引的关系想清楚一定比事后补救轻松。我的建议是新项目直接建复合唯一索引deleted 字段用 bigint默认 0删除时更新为主键 id。这是目前我在 MySQL 项目里最推荐的默认配置因为改动最小不需要生成列也不需要特殊数据库特性。要是团队明确用 PostgreSQL那就用部分索引代码层面你都感觉不到还存在删除标记这个字段。老项目改造时先花点时间把所有可能涉及“唯一键”的表梳理一遍重点观察那些“唯一冲突”被业务频繁触发的表。不必一上来全量改可以先把问题最集中的表调整掉再逐步推广。最后再分享一个小技巧线上排查时不要只看业务日志里的报错把数据库的 general log 在低峰期开一阵子直接搜 Duplicate entry 附近的 SQL很快就能定位到哪些表、哪些唯一索引是热点冲突源。我第一次排查那个实名认证模块时就是靠这个方式发现还有个早期遗留的唯一索引没删干净浪费了大半个小时才找到真正的干扰项。逻辑删除和唯一索引本是两个好用的数据库工具但放在一起就会闹脾气。搞清楚它们冲突的底层逻辑再按业务场景选择合适的方案这个坑其实非常好填。