1、问题背景

当前数据库设计基本都是要逻辑删除代替物理删除，有两点优势：

• 逻辑删除，数据可追溯，出现问题也方便回滚回来
• 逻辑删除不对mysql索引物理空间产生影响，防止索引分散不均匀

参考阿里巴巴开发手册：

在需要逻辑删除的前提下，如果当前表某些字段需要保证其唯一性，需要增加唯一索引对数据进行限制

关于为什么一定要增加唯一索引：

在这两个强制的规约中，如果两点都进行满足，则会出现一些问题：

举例：

测试表结构:

针对上面的表，我们需要name+phone保持唯一性

我们允许这样的数据

但不允许出现另一个 name：王二、phone：188 没有删除的数据。对于已经删除的数据我们允许其重复

1、添加uk索引

uk_name_phone unique (name, phone)

我们删除name：王二的数据，逻辑删除，将is_deleted更新为1

这个时候我们再进行插入一条name：王二、phone：188的数据

就会产生uk冲突，不允许插入，那我们是不是要把is_deleted也加到uk索引中呢

2、增加is_deleted到uk索引中

uk_name_phone_delete unique (name, phone, is_deleted)

再次进行插入数据，能够正常插入成功

此时我们再将刚新增的那条数据进行逻辑删除

这个时候就无法进行正常的逻辑删除了，因为会被uk约束

对于这种问题怎么解决比较好呢？怎么才能同时满足两个条件呢？

2、解决思路

2.1、方案一

既然问题是出在is_deleted字段上面的，那我们把这个字段每次删除的值都变成不唯一不就好了

我们可以再删除的时候将is_deleted更新成一个唯一值，比如

• 使用雪花算法生成一个唯一id
• 获取当前表 对于此条数据唯一的最大is_deleted值，进行自增
• 赋值成当前时间戳（并发删除时可能会失败）

需要将is_deleted字段类型修改为bigint，以便存储雪花算法，时间戳等唯一id

update test_2
set is_deleted = 2
where id = 4;
 
update test_2
set is_deleted = 165399880288699493
where id = 5;
 
update test_2
set is_deleted = UNIX_TIMESTAMP(NOW())
where id = 6;

上述方法就可以解决逻辑删除问题，唯一的缺点是会多占用空间存储，比如雪花算法会占用更多的磁盘空间

2.2、方案二

将删除标记仍设置默认值，将唯一字段与删除标记添加唯一键约束。当某一记录需要删除时，将删除标记置为NULL

把is_deleted字段设置成允许为Null

由于NULL不会和其他字段有组合唯一键的效果，所以当记录被删除时(删除标记被置为NULL时)，解除了唯一键的约束。此外该方法能很好地解决批量删除的问题(只要置为NULL就完事了)，消耗的空间也并不多(1位 + 联合索引)

这样我们再进行逻辑删除的时候，只需要将is_deleted字段更新成null就可以了

update test_1
set is_deleted = null
where name ='李四' and phone = '123';

可以存在多个is_deleted is null 的数据