数据表的唯一约束和逻辑删除冲突

技术交流
1

数据表的唯一约束和逻辑删除冲突

逻辑删除，是一种常见的操作。
一些数据比较重要，不能直接 删除，于是通过一个字段，来标识这条记录是否被删除了。

 `deleted` tinyint(1) NOT NULL COMMENT '逻辑删除。0：未删除，1：已经删除',

对表中的记录进行操作的时候，都要根据 deleted 判断，记录是否已经被删除了。

与唯一约束的冲突

假设，存在一张表。用户名字段 name，是唯一的。并且使用逻辑删除字段 deleted，来标识是否已经删除。

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(20) COLLATE utf8mb4_croatian_ci DEFAULT NULL COMMENT '用户名',
  `deleted` tinyint(1) NOT NULL COMMENT '逻辑删除。0：未删除，1：已经删除',
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_croatian_ci;

唯一约束的问题

先尝试插入一条记录。名字为：KevinBlandy，未删除状态

INSERT INTO `user` (`name`, `deleted`, `create_date`) VALUES('KevinBlandy', 0, NOW());

然后逻辑删除这条记录

UPDATE `user` SET `deleted` = 1 WHERE `id` = 1;

再尝试插入一条记录。名字为：KevinBlandy，未删除状态

INSERT INTO `user` (`name`, `deleted`, `create_date`) VALUES('KevinBlandy', 0, NOW());

唯一约束冲突异常

1062 - Duplicate entry 'KevinBlandy' for key 'name'

对于业务逻辑来说，这条记录已经删除了。所以名字为：KevinBlandy 可以被重新插入。但是对于数据来说，旧数据还是存在。于是导致了冲突

使用时间戳来表示记录是否被删除

并且把唯一约束的字段和逻辑删除字段，合并为联合唯一索引。

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(20) COLLATE utf8mb4_croatian_ci DEFAULT NULL COMMENT '用户名',
  `deleted_date` int(11) NOT NULL COMMENT '逻辑删除时间。0：未删除，非0：删除的时间',
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`deleted_date`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_croatian_ci;

这样就可以解决逻辑删除和唯一约束的冲突，并且还可以通过逻辑删除字段知道，记录被删除的时间。