業務場景

在實際工作當中，遇到一個場景，就是在使用者註冊時，名字要全域性唯一，當然，我們是可以對使用者進行刪除的，你會怎麼去做？

分析

一般來說，我們可以在使用者註冊請求時，進行查庫校驗，看看名字是否已經存在，如果存在就拋異常給提示；否則，就落庫。
除此之外，還可以直接給資料庫欄位加唯一索引

UNIQUE KEY `name_index` (`name`) USING BTREE

當前這種需要根據實際情況分析：

• 如果我們刪除使用者是物理刪除，就是直接delete，沒問題
• 如果我們刪除使用者是邏輯刪除，相對於update資料的刪除標識為1，這時候你怎麼建唯一索引？
  針對第二種情況，可能很多人會說，把刪除標識欄位也加到索引裡面，類似

NIQUE KEY `name_index` (`name`,`is_deleted`) USING BTREE

這裡會有問題，當我們進行相同使用者第二次刪除之後，把id=3的資料刪除（邏輯），修改is_deleted=1，此時就會報錯，如下圖

+----+---------+-----------+
| id | name    | is_deleted |
+----+---------+-----------+
|  1 | forlan0 |         0 |
|  2 | forlan1 |         1 |
|  3 | forlan1 |         0 |
+----+---------+-----------+

那麼，針對邏輯刪除這種情況，怎麼處理？

解決

1、刪除時，修改is_deleted=主鍵

UPDATE forlan SET `is_deleted` = id WHERE `id` = 3;
--修改後的資料如下
+----+---------+------------+
| id | name    | is_deleted |
+----+---------+------------+
|  1 | forlan0 |          0 |
|  2 | forlan1 |          2 |
|  3 | forlan1 |          3 |
+----+---------+------------+

2、刪除時，修改is_deleted=null
這種做法，不是會有兩條相同的資料？下面的情況允許存在？

UPDATE forlan SET `is_deleted` = NULL WHERE `id` = 3;
--修改後的資料如下
+----+---------+------------+
| id | name    | is_deleted |
+----+---------+------------+
|  1 | forlan0 |          0 |
|  2 | forlan1 | NULL       |
|  3 | forlan1 | NULL       |
+----+---------+------------+

Mysql官方檔案的解釋

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.

其實大概意思就是，除BDB儲存引擎外，此約束不適用於NULL值。對於其他引擎，UNIQUE索引允許包含NULL的列有多個NULL值

為什麼允許這麼搞？
我的理解是，NULL其實就表示未知，未知的東西，無法進行判斷；如果NULL對唯一索引起作用，那麼就會導致只能有1行資料為空，我們的業務場景，可能需要用NULL去表示未知或不確定的值。

當前，還是不太建議使用NULL，可能存在一些其它問題，比如：

• 資料丟失
  阿里巴巴規範裡面也說了，count(*) 會統計值為 NULL 的行，而 count(列名) 不會統計此列為 NULL 值的行
  WHERE條件!=不會查到NULL的值
• 程式空指標報錯，比如我們使用SUM(cloumn)，如果欄位都為NULL，最終返回NULL
• 增加查詢難度
  查詢時，語法需要使用IS NULL 、IS NOT NULL、IFNULL(cloumn) 而傳統的 =、!=等就不能使用了

3、新建一個欄位delete_id，刪除時，修改delete_id=主鍵
正常來說，其實1,2種方案已經滿足，為什麼我們要使用這種？
假設我們的表已經上線使用了一段時間，這時我們需要建唯一索引，就可以採取方案，實際上就是在刪除的時候，多更新一個欄位

UPDATE forlan SET `is_deleted` = 1,delete_id = id WHERE `id` = 3;

總結

有3種資料庫層面的解決方案：

• 刪除時，修改is_deleted=主鍵
• 刪除時，修改is_deleted=null
• 新建一個欄位delete_id，刪除時，修改delete_id=主鍵

至於怎麼選擇，看業務場景：
如果是已經投入使用的業務，可以採取方案3，否則可以採取方案1。