數(shù)據(jù)軟刪除時保持字段值唯一性的問題
俗話說：脫離了業(yè)務(wù)場景的技術(shù)面試就是耍流氓。筆者今天(2021-05-19)面試一家做安全公司的 “科學(xué)家” 崗位時，被問到關(guān)于數(shù)據(jù)庫的一道題，感覺很有代表性，特此記錄下來分享給大家。
1. 問題
在數(shù)據(jù)庫做數(shù)據(jù)軟刪除操作時，怎么保證該行數(shù)據(jù)中要求具有唯一性的字段數(shù)據(jù)的唯一性。也就是說，軟刪除狀態(tài)下要求具有唯一性的字段數(shù)據(jù)可以出現(xiàn)多次，未刪除狀態(tài)下要求具有唯一性的字段數(shù)據(jù)只能出現(xiàn)一次。
不要告訴我你不知道什么是軟刪除?
軟刪除就是該行數(shù)據(jù)不會真正的從數(shù)據(jù)表中被delete掉，會有狀態(tài)字段記錄該行數(shù)據(jù)已經(jīng)刪掉
- CREATETABLE `userinfo1` (
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `name` varchar(50) DEFAULT "",
- `status` bigint(20) DEFAULT 0 COMMENT "刪除狀態(tài)（默認0）表示未刪除",
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
假設(shè)現(xiàn)在存在userinfo1表，要求：在status為非刪除狀態(tài)下name字段值唯一，在刪除狀態(tài)下相同的name字段可以出現(xiàn)多次。對于上面的表結(jié)構(gòu)可以進行索引改造等操作，但是不允許添加新的字段。
我給出的解決方案
針對這個問題，當(dāng)時我的腦海中閃現(xiàn)出兩套方案。
方案1：
對userinfo1表的name字段設(shè)置為唯一索引。同時，創(chuàng)建另外一張相同的表結(jié)構(gòu)userinfo2，表中name字段不設(shè)置為唯一索引。在數(shù)據(jù)刪除時，把userinfo1表中的數(shù)據(jù)真實的刪除掉，同時把刪除的數(shù)據(jù)存儲到userinfo2中一份。
優(yōu)點：
- 未刪除數(shù)據(jù)、刪除數(shù)據(jù)分開存儲
- 可以解決name字段在未被刪除時唯一存在，刪除之后可以重復(fù)的問題
缺陷：
- 不符合題目軟刪除要求
- 多創(chuàng)建了一張表，增加了維護成本
- userinfo1表中刪除，userinfo2表插入被刪除數(shù)據(jù)，兩個操作動作對應(yīng)2條不同SQL，需要在同一個事物中操作
- 操作比較復(fù)雜
當(dāng)然，方案被面試官否決了。面試官說：“你面試的可是科學(xué)家崗位呀，再想想。”
方案2：
對userinfo1表的name、status兩個字段設(shè)置聯(lián)合的唯一索引，在刪除數(shù)據(jù)時對status、name字段同時進行更新，status字段更新為非0(比如1)、name字段加上一個當(dāng)前毫秒時間戳作為后綴(方案參考雪花算法實現(xiàn)的 分布式系統(tǒng)唯一ID，只要保證要求的字段唯一存在即可)。
優(yōu)點：
- 沒有使用新的數(shù)據(jù)表、新的字段
- 軟刪除只需要更新兩個字段即可滿足題目要求
缺點：
- 更新數(shù)據(jù)時對原name字段添加后綴，數(shù)據(jù)造成了污染(改變了原數(shù)據(jù))
面試官聽了聽，說道：“跟理想的答案很接近了，雖然可以解決問題，但是添加后綴后原數(shù)據(jù)被污染了。作為想成為'科學(xué)家'的男人，還有新的方案嗎?”
我想了想說：“暫時沒想到新的方案，可以提示一下嗎?”
面試官說：“name、status創(chuàng)建聯(lián)合的唯一索引沒問題，關(guān)鍵在于status怎么處理?再想想。”
3分鐘后，我說：“我盡力了，還是你來當(dāng)科學(xué)家吧。”
2. 理想方案
面試官怕打消我的積極性，說道：“年輕人，不要這么浮躁，我給你指點一下。”
方案3
對userinfo1表的name、status兩個字段設(shè)置聯(lián)合的唯一索引，在更新數(shù)據(jù)時把被軟刪除的數(shù)據(jù)行對應(yīng)的id值，賦值給status字段(status等于0表示未刪除，非0表示已刪除)。
最終的表結(jié)構(gòu)為：
- CREATETABLE `userinfo1` (
- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
- `name` varchar(50) DEFAULT "",
- `status` bigint(20) DEFAULT 0 COMMENT "刪除狀態(tài)（默認0）表示未刪除 非0表示刪除",
- PRIMARY KEY (`id`),
- UNIQUE KEY `name_status` (`name`, `status`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
聽完之后，我是猛地一拍腦門，說道：“哎呀，距離成為科學(xué)家僅有一步之遙，可惜了。”
總結(jié)
脫離了實際場景的問題大部分都是耍流氓，只有結(jié)合具體場景才能有針對性的對問題進行分析，從而得到一個可行的最優(yōu)案。
解決本文開頭的問題可能有很多方案，但是最優(yōu)的也就兩點：
① 對需要保持唯一的數(shù)據(jù)創(chuàng)建聯(lián)合唯一索引
② 軟刪除時status字段更新為該行數(shù)據(jù)的唯一值(也就是主鍵id)
