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

前言

本文主要分享在实际工作中和面试中遇到的情况，就是如何建立含义逻辑删除字段的唯一索引，实现了多种方式进行比较

一、业务场景

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

二、分析

一般来说，我们可以在用户注册请求时，进行查库校验，看看名字是否已经存在，如果存在就抛异常给提示；否则，就落库。

除此之外，还可以通过建唯一索引来限制

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

当前这种需要根据实际情况分析：

1. 如果我们删除用户是物理删除，直接delete，没问题
2. 如果我们删除用户是逻辑删除，相对于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=主键

这种做法，主要是提升is_deleted列的取值范围

当is_deleted=0时表示记录有效，当is_delete>时表示记录被删除，在删除记录时将is_delete值设置为不同数值，只要确保相同唯一条件的记录使用不同数值即可

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种方案已经满足，为什么我们要使用这种？
假设我们的表已经上线，使用了一段时间，这时我们需要建唯一索引，就可以采取这种方案，
具体操作：

建唯一索引(name,is_deleted,delete_id)，设置is_deleted=0时，delete_id=0，设置is_deleted=1时，delete_id=主键

实际上就是在删除的时候，多更新一个字段

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

四、总结

有3种数据库层面的解决方案：

• 删除时，修改is_deleted=主键
• 删除时，修改is_deleted=null
• 新建一个字段delete_id，删除时，修改delete_id=主键

至于怎么选择，你看业务场景：
如果是已经投入使用的业务，可以采取方案3，否则可以采取方案1。