數據軟刪除時保持字段值唯一性的問題
俗話說：脫離了業務場景的技術面試就是耍流氓。筆者今天(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)
