数据软删除时保持字段值唯一性的问题

数据软删除时保持字段值唯一性的问题

俗话说：脱离了业务场景的技术面试就是耍流氓。笔者今天(2021-05-19)面试一家做安全公司的 “科学家” 岗位时，被问到关于数据库的一道题，感觉很有代表性，特此记录下来分享给大家。

1. 问题

在数据库做数据软删除操作时，怎么保证该行数据中要求具有唯一性的字段数据的唯一性。也就是说，软删除状态下要求具有唯一性的字段数据可以出现多次，未删除状态下要求具有唯一性的字段数据只能出现一次。

不要告诉我你不知道什么是软删除?

软删除就是该行数据不会真正的从数据表中被delete掉，会有状态字段记录该行数据已经删掉

CREATETABLE `userinfo1` (   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   `name` varchar(50) DEFAULT "",   `status` bigint(20) DEFAULT 0 COMMENT "删除状态（默认0）表示未删除",   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设现在存在userinfo1表，要求：在status为非删除状态下name字段值唯一，在删除状态下相同的name字段可以出现多次。对于上面的表结构可以进行索引改造等操作，但是不允许添加新的字段。

我给出的解决方案

针对这个问题，当时我的脑海中闪现出两套方案。

方案1：

对userinfo1表的name字段设置为唯一索引。同时，创建另外一张相同的表结构userinfo2，表中name字段不设置为唯一索引。在数据删除时，把userinfo1表中的数据真实的删除掉，同时把删除的数据存储到userinfo2中一份。

优点：

未删除数据、删除数据分开存储可以解决name字段在未被删除时唯一存在，删除之后可以重复的问题

缺陷：

不符合题目软删除要求多创建了一张表，增加了维护成本userinfo1表中删除，userinfo2表插入被删除数据，两个操作动作对应2条不同SQL，需要在同一个事物中操作操作比较复杂

当然，方案被面试官否决了。面试官说：“你面试的可是科学家岗位呀，再想想。”

方案2：

对userinfo1表的name、status两个字段设置联合的唯一索引，在删除数据时对status、name字段同时进行更新，status字段更新为非0(比如1)、name字段加上一个当前毫秒时间戳作为后缀(方案参考雪花算法实现的 分布式系统唯一ID，只要保证要求的字段唯一存在即可)。

优点：

没有使用新的数据表、新的字段软删除只需要更新两个字段即可满足题目要求

缺点：

更新数据时对原name字段添加后缀，数据造成了污染(改变了原数据)

面试官听了听，说道：“跟理想的答案很接近了，虽然可以解决问题，但是添加后缀后原数据被污染了。作为想成为'科学家'的男人，还有新的方案吗?”

我想了想说：“暂时没想到新的方案，可以提示一下吗?”

面试官说：“name、status创建联合的唯一索引没问题，关键在于status怎么处理?再想想。”

3分钟后，我说：“我尽力了，还是你来当科学家吧。”

2. 理想方案

面试官怕打消我的积极性，说道：“年轻人，不要这么浮躁，我给你指点一下。”

方案3

对userinfo1表的name、status两个字段设置联合的唯一索引，在更新数据时把被软删除的数据行对应的id值，赋值给status字段(status等于0表示未删除，非0表示已删除)。

最终的表结构为：

CREATETABLE `userinfo1` (   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   `name` varchar(50) DEFAULT "",   `status` bigint(20) DEFAULT 0 COMMENT "删除状态（默认0）表示未删除 非0表示删除",   PRIMARY KEY (`id`),   UNIQUE KEY `name_status` (`name`, `status`)   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

听完之后，我是猛地一拍脑门，说道：“哎呀，距离成为科学家仅有一步之遥，可惜了。”

总结

脱离了实际场景的问题大部分都是耍流氓，只有结合具体场景才能有针对性的对问题进行分析，从而得到一个可行的最优案。

解决本文开头的问题可能有很多方案，但是最优的也就两点：

① 对需要保持唯一的数据创建联合唯一索引

② 软删除时status字段更新为该行数据的唯一值(也就是主键id)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。