在逻辑删除的场景下，如何通过唯一索引保证唯一性，一步一步分析，最终实现了3种方案
文章目录
业务场景
分析
解决
总结
业务场景
在实际工作当中，遇到一个场景，就是在用户注册时，名字要全局唯一，当然，我们是可以对用户进行删除的，你会怎么去做？
分析
一般来说，我们可以在用户注册请求时，进行查库校验，看看名字是否已经存在，如果存在就抛异常给提示；否则，就落库。除此之外，还可以直接给数据库字段加唯一索引
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。
发表评论