在邏輯刪除表加唯一索引
我們都知道唯一索引非常簡單好用,但有時候,在表中它並不好加。
不信,我們一起往下看。
通常情況下,要刪除表的某條記錄的話,如果用delete
語句操作的話。
例如:
delete from product where id=123;
這種 delete 操作是物理刪除
,即該記錄被刪除之後,後續通過 sql 語句基本查不出來。(不過通過其他技術手段可以找回,那是後話了)
還有另外一種是邏輯刪除
,主要是通過update
語句操作的。
例如:
update product set delete_status=1,edit_time=now(3)
where id=123;
邏輯刪除需要在表中額外增加一個刪除狀態欄位,用於記錄資料是否被刪除。在所有的業務查詢的地方,都需要過濾掉已經刪除的資料。
通過這種方式刪除資料之後,資料任然還在表中,只是從邏輯上過濾了刪除狀態的資料而已。
其實對於這種邏輯刪除的表,是沒法加唯一索引的。
為什麼呢?
假設之前給商品表中的name
和model
加了唯一索引,如果使用者把某條記錄刪除了,delete_status 設定成 1 了。後來,該使用者發現不對,又重新添加了一模一樣的商品。
由於唯一索引的存在,該使用者第二次新增商品會失敗,即使該商品已經被刪除了,也沒法再添加了。
這個問題顯然有點嚴重。
有人可能會說:把name
、model
和delete_status
三個欄位同時做成唯一索引
不就行了?
答:這樣做確實可以解決使用者邏輯刪除了某個商品,後來又重新新增相同的商品時,新增不了的問題。但如果第二次新增的商品,又被刪除了。該使用者第三次新增相同的商品,不也出現問題了?
由此可見,如果表中有邏輯刪除功能,是不方便建立唯一索引的。
但如果真的想給包含邏輯刪除的表,增加唯一索引,該怎麼辦呢?
增加時間戳欄位
導致邏輯刪除表,不好加唯一索引最根本的地方在邏輯刪除那裡。
我們為什麼不加個欄位,專門處理邏輯刪除的功能呢?
答:可以增加時間戳
欄位。
把 name、model、delete_status 和 timeStamp,四個欄位同時做成唯一索引
在新增資料時,timeStamp 欄位寫入預設值1
。
然後一旦有邏輯刪除操作,則自動往該欄位寫入時間戳。
這樣即使是同一條記錄,邏輯刪除多次,每次生成的時間戳也不一樣,也能保證資料的唯一性。
時間戳一般精確到秒
。
除非在那種極限併發的場景下,對同一條記錄,兩次不同的邏輯刪除操作,產生了相同的時間戳。
這時可以將時間戳精確到毫秒
。
該方案的優點是:可以在不改變已有程式碼邏輯的基礎上,通過增加新欄位實現了資料的唯一性。
缺點是:在極限的情況下,可能還是會產生重複資料。
增加 id 欄位
其實,增加時間戳欄位基本可以解決問題。但在在極限的情況下,可能還是會產生重複資料。
有沒有辦法解決這個問題呢?
答:增加主鍵
欄位:delete_id。
該方案的思路跟增加時間戳欄位一致,即在新增資料時給 delete_id 設定預設值 1,然後在邏輯刪除時,給 delete_id 賦值成當前記錄的主鍵 id。
把 name、model、delete_status 和 delete_id,四個欄位同時做成唯一索引。
這可能是最優方案,無需修改已有刪除邏輯,也能保證資料的唯一性。