✅数据库逻辑删除后，怎么做唯一性约束？

典型回答

假设我们有一张开通表，里面记录了用户的服务开通记录 service_record，主要有以下几个字段

字段	类型	是否可空
id	long	否
gmt_create	date	否
gmt_modified	date	否
user_id	varchar	否
product_code	varchar	否
state	varchar	否

然后如果有用户退出的时候，我们需要记录用户的退出状态，这时候一般我们都不能做物理删除的，因为数据库的物理删除意味着数据都没有了，那么后续的各种数据分析，报表等都没办法做了，所以大多数选择逻辑删除，那么就是引入一个新的字段：

字段	类型	是否可空
id	long	否
gmt_create	date	否
gmt_modified	date	否
user_id	varchar	否
product_code	varchar	否
state	varchar	否
is_deleted	int	否

这个字段一般定义为deleted或者is_deleted，常见的做法是默认是0，表示未删除，如果逻辑删除的话则把他设置为1。

那么问题来了，我们怎么避免用户在开通的时候因为并发导致插入多条开通记录呢？

当然，我们可以做各种分布式锁，但是我们也提到过（如https://www.yuque.com/hollis666/xkm7k3/gz2qwl），不管怎么做，最终都得在数据库层面做兜底，而一般这种情况，就需要做一个唯一性约束。

可是同一个用户是可以有多条开通记录的，唯一性约束如果用userid+productcode 那么就肯定是不行的，会导致用户无法多次开通。

但是如果再加上is_deleted，是可以区分开通和退出了，但是一个用户如果多次退出又不行了，那么该如何支持这种业务需求呢？

后面一共有四种方案，前两个方案是如果直接做物理删除如何实现业务需求！后面两个是如果就是不能做物理删除该怎么办？

如果是题目中的问题的解答，可以直接看3、4两个方案，但是前两个也建议大家看一下，有的时候，面试的时候不只是可以回答面试官的问题，也可以想办法从跟上让他的问题不会发生。

方案一，物理删除+数据归档

最简单的方案就是直接做物理删除了，但是物理删除怎么实现数据分析、报表等功能呢，就是说如何做数据留痕呢？

简单的方案就是做数据归档或者离线表。

就是说再创建一张servicerecordhis表，然后再用户退出时，在同一个事务中把servicerecord表中的数据插入到servicerecordhis中，然后再把servicerecord表中的记录删除：

INSERT INTO service_record_his
SELECT * FROM service_record WHERE user_id = 'xxx' and product_code = "";

DELETED FROM service_record WHERE user_id = 'xxx' and product_code = "";

这样我们就可以基于servicerecordhis进行数据分析和做报表了。

还有一种归档方式，就是通过离线数仓来做，比如我们的业务是这样做的：

1、在线表每天凌晨都向离线数仓做一次数据同步

2、同一个用户当天退出后不能立刻开通（一般业务上都有这个限制，有的业务时3天，有的是15天，避免用户刷新人优惠之类的，对于我们的金融业务，也避免用户挑保，这个就不展开说了。。。）

3、数仓同步时，数据的insert、update做同步，但是delete不做同步

以上其实并不是我们为了做这个需求特意做的约束，而是我们的业务和数仓同步逻辑他就是这样的，所以这样的话不需要一张servicerecordhis表了，直接做物理删除就行了。

当然，这个方案存在一个小问题，一般离线数仓都是每天陵城做一次整体的数据同步，所以，如果当天开通后马上退出，其实是有问题的（因为离线数仓还没来得及同步），所以我们在业务上限制当天不能退出。还有的是限制当天退出后不能再开通，然后当天的退出记录不立即delete，而是当天凌晨数仓同步后再delete。还有的就是可以在用户注册之后马上触发一次离线表同步（这个方案用的很少，不建议）。

方案二，复用一条开通记录+记录开通流水

为了解决我们的数据留痕的问题，不一定非要基于servicerecord这张主表，像我们的业务来说，基本上一些核心操作都是有流水表的，如servicerecord_stream，用户相关的各种操作，开通、退出、冻结、解冻等等都插入一条流水。

这样我们就可以保证service_record表中针对同一个用户+产品，只会有一条记录，用户开通后，状态为ACTIVE，用户退出后，状态为QUIT。用户重新开通，再更新为ACTIVE。

然后所以数据的分析需求，都基于流水表来做，流水表一般比主表多几个字段。如：

字段	类型	是否可空
id	long	否
gmt_create	date	否
gmt_modified	date	否
user_id	varchar	否
product_code	varchar	否
state	varchar	否
stream_type	varchar	否

方案三，>0都表示逻辑删除

前面两个方案都是绕开了我们的问题，那么能不能直接通过加唯一性约束的方式解决？

有一个办法，那就是之所以我们没办法把userid+productcode+isdeleted作为一个联合的唯一索引，主要是因为isdeleted只有0和1两种情况

那只要解决这个问题就行了，核心的思想就是0表示未删除，只要大于0都表示删除。

那么就可以在每次删除之后，isdeleted都+1，isdeleted=1表示第一次退出，is_deleted=2表示第2次退出。。。。

这么做还是有点麻烦，而且也可能有一个并发情况下导致这个id重复了，虽然概率很低很低。但是我们前面说了，数据库唯一性约束用于兜底，该有的分布式锁还是有的，所以并发问题倒也还好。

不过还是可以简单点，那就是在用户退出的时候，直接把这条servicerecord的主键（前提需要时自增的ID）设置到isdeleted中。

i，isdeleted=43532表示用户退出，isdeleted=64532也表示退出了。。。。

方案四，引入一个新的字段

除了上面的方式是改造isdeleted以外，还有一个办法，那就是引入一个新的字段，如deletedtime表示这条记录被删除的时间戳

然后userid+productcode+isdeleted+deletedtime一起，做一个联合的唯一索引。

当然，你也可以把isdeleted去掉，然后是否被逻辑删除通过deletedtime是否为空来判断，那么就又和第三个方案有点像了。

总结

以上提了几个方案，其实各有优缺点，有的需要引入一张新表，有的需要自己控制并发，有的需要依赖数仓能力，有的需要增加冗余字段。

总之都各自有优缺点，反正这几个方案我们都用过，不同的业务逻辑可能用的也不太一样。有的也多个方案做组合，比如：历史表+流水表+数仓、流水表+>0逻辑删除。

原文: https://www.yuque.com/hollis666/xkm7k3/uwumrs9gs9x422k7