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

ivansli2021-05-262023-10-31

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

问题

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

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

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

CREATE TABLE  `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分钟之后，我说：“我尽力了，还是你来当科学家吧。”

理想方案

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

方案3

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

最终的表结构为：

CREATE TABLE  `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;