上一篇文章講了表設計的18條軍規(guī)，其中講到了唯一索引的坑，今天就來細說一下。

閉眼建表所需的18條軍規(guī)

在之前的工作中，遇到過一次唯一索引的 Bug，今天就分享分享，省的有同兄弟踩坑里。

為眾人抱薪者，不可使其凍斃于風雪。兄弟們一鍵三連啊?。?！感謝！

一、現場還原

先看表結構，其中 name、age、city 三個字段創(chuàng)建一個聯(lián)合唯一索引。

CREATE TABLE `test` (
  `id` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `city` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`age`,`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

如果在這三個字段上創(chuàng)建一個聯(lián)合唯一索引，那么就不會存在兩行數據在這三個字段上的值完全相同。

下面來看一組數據。

通過上圖可以看到，北京的張三有多個，畢竟年齡可以不一樣嗎，其中有兩個 age 為空的，同樣上海的李四也有多個。

有的同學可能會說了，是不是唯一索引沒生效啊，那我們現在試一下唯一索引的生效情況。

可以看到已經報錯，對于這個唯一索引已經出現了重復的數據，那么究竟是什么造成了唯一索引的失效呢？

二、原因分析

就是因為我們插入的數據中 age 為 Null 導致的。在 MySQL 官方文檔中也有說明，Null 與任何值都不相等，包括與另一個 Null 比較。

MySQL 關于Null 描述地址：https://dev.mysql.com/doc/refman/8.4/en/problems-with-null.html

所以結論就是：當多個字段一起創(chuàng)建唯一索引時，需要設置每一項字段非空，如果其中一項出現 Null 值，MySQL 的唯一索引會失效。

三、邏輯刪除

說到唯一索引，就要想到有邏輯刪除這個東西，對于現在的系統(tǒng)來說，10個系統(tǒng)里面8個是用邏輯刪除。

對于系統(tǒng)中記錄的刪除，一般是兩種，一種是物理刪除，也就是使用delete語句進行刪除。另一種就是使用邏輯刪除，在表中增加一個刪除標記字段，比如deleted，默認0，當需要刪除時改為1。

通過物理刪除的數據，在表中是查詢不出來的，不過可以通過 binlog 進行恢復，如果你感興趣，歡迎評論區(qū)留言，下一篇咱就講一下如何還原數據。

通過邏輯刪除的數據，在表中還是存在的，只是刪除標記deleted變成了1。

我們還拿上面那張表舉例，假如我們刪除了張三、18、北京這條記錄，如果后面再次插入張三、18、北京這條數據是無法插入的，因為我們創(chuàng)建的唯一索引是name、`age、city三個字段。

那么這種情況我們可以怎么解決呢？

有的同學可能就會說了，在上面name、`age、city唯一索引的基礎上，增加deleted，創(chuàng)建4個字段的唯一索引不就行了。

那么真的可以嗎？

還是那句話，用上面的表舉個栗子：

下面跟我一起來看下如何解決？

四、刪除狀態(tài)加1

第一種方式就是刪除狀態(tài)加1，現在我們表中deleted默認0，如果刪除了之后就是1，那么我們更改為，deleted默認0，刪除之后獲取當前相同記錄的最大刪除狀態(tài)，然后加1。

舉個栗子：

通過上面的例子可以看出來，每次刪除記錄，當前記錄的刪除標記deleted都會獲取當前相同記錄的最大刪除狀態(tài)，然后加1進行刪除。

這樣deleted 每次刪除的時候都是不一樣的，所以可以保證唯一索引的生效。

使用這種方案的缺點就是需要修改代碼中的sql邏輯，比如查詢deleted為1的刪除數據時需要改為deleted>1。

五、增加時間戳

除了上面這種對刪除狀態(tài)進行加1的方式外，還可以增加一個時間戳字段，創(chuàng)建name、`age、city、timestamp四個字段的聯(lián)合唯一索引。

時間戳一般精確到秒，如果并發(fā)高，還是可能生成重復數據，那么時間戳的話可以精確到毫秒。

然后設置時間戳字段默認值為1，當進行邏輯刪除刪除時，直接插入當前時間的時間戳。

這種方案的優(yōu)點就是不用修改原來代碼邏輯，缺點就是極限情況下還是可能會產生重復數據。

六、增加刪除ID

我們還可以使用增加刪除ID的方法來進行去重。

創(chuàng)建唯一索引name、`age、city、deleted_id。

插入數據時deleted_id默認1，當進行邏輯刪除時修改為當前記錄的主鍵ID。

這種方法與增加時間戳字段類似，優(yōu)點就是可以解決時間戳字段的重復數據問題，并且無需修改現有系統(tǒng)的刪除邏輯，也可以保證數據的唯一，所以如果再有邏輯刪除的表中，推薦使用這種方式。

七、歷史數據加唯一索引

在上面的幾個方案中，都是對新表添加的唯一索引，現在有一張歷史數據表，其中還有重復數據，那么我們該如何添加索引呢？

我們使用deleted_id的方案，首先獲取出相同記錄的最大ID，然后將這些記錄的deleted_id設置為1，然后其他的記錄deleted_id就是當前記錄的主鍵，這樣我們就可以區(qū)分表中的重復數據了。

當表中的deleted_id都有值之后，創(chuàng)建唯一索引name、`age、city、deleted_id。

八、大字段添加唯一索引

創(chuàng)建索引的要求大家應該都知道，字段不可以太大，因為索引本身大了之后檢索的效率也是很低的。

關于MySQL 中 InnoDB 引擎的限制可以查看這個鏈接：https://dev.mysql.com/doc/refman/8.4/en/innodb-limits.html

對于大的字段添加唯一索引，可以使用hash算法，創(chuàng)建一個hash字段，將大字段進行Hash運算之后的結果保存到 hash 中，然后創(chuàng)建唯一索引name、age、city、hash。

用到Hash算法，肯定就會有Hash沖突，所以這種方案會帶來一個問題就是不同的值Hash卻相同。

所以創(chuàng)建多列的聯(lián)合唯一索引時需要在增加一個其他的字段進行區(qū)分。

還有一種方案就是不使用唯一索引，使用唯一索引的目的就是去重，直接代碼層面MQ、單線程處理等。

總結

本文通過還原唯一索引失效的場景，得出當多列唯一索引中的某一列有為Null的值時，唯一索引會失效。

總結出在有邏輯刪除的業(yè)務表中，可以通過刪除狀態(tài)值加1、增加時間戳字段、增加刪除ID字段三種方式進行添加多列唯一索引。

最后還給出了如何對歷史重復數據添加唯一索引以及使用hash給大字段添加唯一索引的方式。