数据软删除时保持字段值唯一性的问题今天（2021-05-19）面试一家安全公司的“科学家”职位时，被问到一个关于数据库的问题。觉得很有代表性，所以记录下来分享给大家。1、问题是在对数据库进行数据软删除操作时，如何保证数据行中要求唯一性的字段数据的唯一性。也就是说，要求唯一性的字段数据在软删除状态下可以出现多次，要求唯一性的字段数据在未删除状态下只能出现一次。别告诉我你不知道软删除是什么？软删除是指数据表中不会删除该行数据，会有一个status字段记录该行数据已被删除CREATETABLE`userinfo1`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENT,`name`varchar(50)DEFAULT"",`status`bigint(20)DEFAULT0COMMENT"删除状态（默认0）表示未删除",PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;假设现在有一张userinfo1表，要求name字段的值在未删除状态下是唯一的，同名字段在删除状态下可以出现多次。对于上述表结构，可以进行索引变换等操作，但不允许增加新的字段。我给出的方案就是针对这个问题的，当时脑子里闪过两套方案。方案一：将userinfo1表的name字段设置为唯一索引。同时创建另一个相同的表结构userinfo2，表中的name字段不设置为唯一索引。删除数据时，实际上删除了userinfo1表中的数据，删除的数据保存在userinfo2的副本中。优点：未删除数据和已删除数据分开存储，可以解决name字段只在不删除时存在，删除后可以重复的问题。缺陷：多建了一张不满足topic软删除要求的表，增加了维护成本。在userinfo1表Delete中，将删除的数据插入到userinfo2表中，这两个操作对应两个不同的SQL，在同一个东西里面操作比较复杂。当然，这个方案被面试官否决了。面试官说：“你面试的是科学家的职位，再想想。”方案二：为userinfo1表的name和status字段设置联合唯一索引，删除数据时同时更新status和name字段。status字段更新为非零（比如1），name字段后缀为当前毫秒时间戳（方案参考雪花算法实现的分布式系统的唯一ID，只要必填字段是唯一存在的）。优点：没有使用新的数据表，新字段软删除只需要更新两个字段就可以满足题目的要求缺点：更新数据时，给原名称字段添加后缀，造成数据污染（改变原数据）面试官听完后说：“已经很接近理想的答案了，虽然可以解决问题，但是加上后缀后，原始数据会被污染。作为一个想成为‘科学家’的人，是有什么新的解决办法吗？”我想了想说：“我暂时没有想到新的解决办法，你能给我提示一下吗？”面试官说：“name和status建立一个联合唯一索引是没有问题的，关键是status怎么处理？再想想。”3分钟后，我说：“我已经尽力了，你应该是个科学家。”2.理想的计划。面试官怕打消我的热情，说：“小伙子，别这么浮躁，我给你提点建议。”status的两个字段设置了一个联合唯一索引。更新数据时，将软删除数据行对应的id值赋给status字段（status等于0表示未删除，非0表示已删除）。最终表结构为：CREATETABLE`userinfo1`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENT,`name`varchar(50)DEFAULT"",`status`bigint(20)DEFAULT0COMMENT"删除状态(默认0)表示没有0表示删除",PRIMARYKEY(`id`),UNIQUEKEY`name_status`(`name`,`status`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;我听完，一拍脑袋道：“唉，距离科学家只有一步之遥，可惜了。》总结大多数脱离实际场景的问题都是耍流氓，只有结合具体场景才能有针对性地分析问题，从而得出可行的最优解。本文开头，但最优??的只有两点：①为需要保持唯一的数据创建联合唯一索引②软删除时，状态字段更新为行数据的唯一值（即,主键id)
