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