如何建立含有逻辑删除字段的唯一索引

业务场景

在实际工作当中，遇到一个场景，就是在用户注册时，名字要全局唯一，当然，我们是可以对用户进行删除的，你会怎么去做？

分析 

一般来说，我们可以在用户注册请求时，进行查库校验，看看名字是否已经存在，如果存在就抛异常给提示；否则，就落库。
除此之外，还可以直接给数据库字段加唯一索引

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。