俗话说：脱离了业务场景的技术面试就是耍流氓。笔者今天(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)
