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

閉眼建表所需的18條軍規

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

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

一、現場還原

先看表結構，其中 name、age、city 三個字段創建一個聯合唯一索引。

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;

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

下面來看一組數據。

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

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

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

二、原因分析

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

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

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

三、邏輯刪除

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

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

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

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

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

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

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

那么真的可以嗎？

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

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

四、刪除狀態加1

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

舉個栗子：

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

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

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

五、增加時間戳

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

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

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

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

六、增加刪除ID

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

創建唯一索引name、`age、city、deleted_id。

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

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

七、歷史數據加唯一索引

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

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

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

八、大字段添加唯一索引

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

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

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

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

所以創建多列的聯合唯一索引時需要在增加一個其他的字段進行區分。

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

總結

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

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

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