唯一索引遇上逻辑删除

本安A
1,078 阅读3分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第3天，点击查看活动详情

准备测试数据，创建一个表

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `is_delete` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_name` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

唯一索引失效场景

唯一索引字段不能为空（基础），没有设置。

插入语句都成功
​
INSERT into t_test(is_delete) VALUES(0);
INSERT into t_test(is_delete) VALUES(0);

在上面的建表语句中我们可以看出，没有设置username不等于null，导致在插入数据的时候仍然可以插入两个相同的null数据，如果是组合的唯一索引只要有一个没有设置都有可能导致相同数据出现。

因为NULL代表未知，未知和未知比较结果仍然是未知，所以Mysql会认为这时候他们没有重复。

唯一索引遇见了逻辑删除

我们需要建立username+is_delete的唯一索引

1、我们把插入的数据的is_delete改为1代表逻辑删除。

2、再次插入一条数据。

3、把这条数据删除再次删除发现就会报错。 Duplicate entry 'my1-1' for key 'test_name'

INSERT into t_test(username,is_delete) VALUES('my1',1);
​
INSERT into t_test(username,is_delete) VALUES('my1',0);
​
#再次插入报错
update t_test set is_delete = 1 where id = 5

原因：明显逻辑删除的数据还是存在，没有真的删除，再次删除新的数据就会报错。

is_delete的NULL代表删除，0代表未删除

is_delete的状态null表示删除时候，和上面介绍的一样：NULL代表未知，未知和未知比较结果仍然是未知，所以再次删除仍然可以。

但是唯一索引字段一般不允许设置空值

is_delete的时间戳为删除，0未删除

修改数据库的表结构

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `is_delete` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_name` (`username`,`is_delete`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

另外一种方法：新增一个时间字段，把他们三个字段建立索引，也是一种解决办法。

添加数据时，time字段写入默认值1

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `is_delete` int(1) NOT NULL,
  `time` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_name` (`username`,`is_delete`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

新增-删除，再新增，在删除操作都能成功。

INSERT into t_test(username,is_delete,time) VALUES('my1',0,1);
update t_test set is_delete = 1,time = unix_timestamp(now()) where id = 9
INSERT into t_test(username,is_delete,time) VALUES('my1',0,1);
update t_test set is_delete = 1,time = unix_timestamp(now()) where id = 10

优点：可以在不改变已有代码逻辑的基础上，通过增加新字段实现了数据的唯一性。

缺点：在极限的情况下，可能还是会产生重复数据。 时间戳一般精确到秒。

is_delete的当前行的id未删除，0未删除

此方法类比时间戳的方法一样。

优点：不存在极限情况，不会出现重复数据。

补充

大字段的唯一索引

1、大字段hash值

我们可以增加一个hash字段，取大字段的hash值，生成一个较短的新值。把该值加上唯一索引。

当然如果还有其他字段可以区分，比如：name，并且业务上允许这种重复的数据，不写入数据库，该方案也是可行的。

2、在Java代码层面判断是否重复，插入或者修改的时候进行单线程操作，判断是否重复。

avatar
文章
阅读
粉丝