逻辑删除和唯一索引冲突

工作中进行项目开发时，我们通常会给一些表设置逻辑删除字段，目的不仅让sql操作更快，并且在运营人员误操作时，也能快速进行数据恢复。

引入逻辑删除后，同时也会带来一些问题：

1. 自定义sql编写会更加复杂：编写sql是我们需要在查询条件上添加上逻辑删除字段
2. 会和唯一索引发生冲突；

引入逻辑删除后，我们删除数据时，操作变为更新某条数据项，让其逻辑删除字段置为1。但如果表中存在唯一索引，之后我们在插入一条已被删除的 数据项，那么将会报错；究其原因是应为表中唯一索引冲突，逻辑删除并为真正删除数据项，而是通过业务逻辑控制，标识为已经删除了。

那开发中，如果需要引入逻辑删除，我们该怎么设计来避免冲突呢？

在MySQL中，当我们创建复合唯一索引时，如果某个字段为 null，是允许重复的，举个例子：

存在一张学生表入下表所示，要求同一个班级下学生人名不能重复。

-- id --	-- name --	-- gender --	-- class --
1	张三	男	三年一班
2	李四	女	三年一班
3	王五	女	三年一班
4	张三	男	三年二班
5	张三	男	null
6	张三	女	null

为上示表创建唯一索引 alter table student add unique index uk_name_class(name, class)

依次按需插入数据，我们发现表中存在两条 name 为 张三 但 class 为 null 的数据项，但是命令却不会报错。 原因：MySQL 对 NULL 值有一些特别的处理逻辑。

首先，在MySQL中, NULL 值表示不存在和未知。

1. 进行比较运算（=, <, >, !=）：比较操作返回的结果都是 NULL， 如需对NULL检测，需要使用 IS NULL(IS NOT NULL)
2. 进行聚合运算（count, sum, avg, min 函数后加列名）：不会计算NULL值
3. 进行逻辑运算：AND和OR，结果可能是 NULL， NOT NULL 返回NULL
4. 进行排序和分组：NULL排序默认被视为最小值；NULL分组被视为一个单独的组；
5. 相关函数： IFNULL-第一个表达式为空返回第二个； COALESCE-返回第一个不为NULL的表达式
6. 相关索引：
   • 唯一索引：唯一索引可以包含多个NULL值
   • 普通索引：普通索引可以包含多个NULL值

MySQL 中 唯一索引是可以包含多个NULL值；那么我们可以使用这条规则，设计逻辑删除：

1. 逻辑删除字段默认值为0
2. 为唯一索引字段和逻辑删除字段创建一个复合索引 uk_student(name, delete_flag)
3. 删除操作时，将该字段更新为NULL，数据库中存在多个相同唯一索引字段（name）不会发送冲突，因为逻辑删除字段为NULL，组合起来 (name, NULL)
4. 插入重复数据，(name, 0)
5. 再次插入重复数据 (name, 0) 将发送冲突，存在相同的非空字段。

总结

解决唯一索引冲突操作： alter table student add unique index uk_name_class(name, class, delete_flag) alter table student add unique index uk_name_class(name, class, delete_flag)