软删除与唯一约束共存的解决方案

软删除与唯一约束共存的解决方案

今天一个已经上线了的项目被测出来一个bug，那就是创建了一个用户以后，再把这个用户删了，再创建一个同一个手机号的用户的时候，由于手机号有唯一约束，又用了软删除，就报了手机号码重复的 bug，问题可以简化为：

DROP TABLE IF EXISTS tmp_table;
CREATE TABLE `tmp_table` (
    id bigint primary key auto_increment,
    phone varchar(20) unique not null,
    del_flag int default 0
);

-- 创建一个新用户
INSERT INTO tmp_table (phone) values (15100000000); 

-- 软删除该用户
UPDATE tmp_table SET del_flag = 1 where phone = 15100000000;

-- 创新创建账号
INSERT INTO tmp_table (phone) values (15100000000); 
-- 报错：Duplicate entry '15100000000' for key 'tmp_table.phone'

为了解决这个问题，很容易想到把唯一索引去掉，或者硬删除，但这些都不够优雅。优雅的方式是使用联合约束来进行处理。在有些ORM框架中，软删除是以deleted_at是否为空来做判断的，非空则填上删除时间。这样的话可以在创表的时候创建一个 (phone, deleted_at) 的联合唯一约束，这样就可以解决这个问题了。

DROP TABLE IF EXISTS tmp_table;
CREATE TABLE `tmp_table` (
    id bigint primary key auto_increment,
    phone varchar(20) not null,
    deleted_at timestamp default null
);
ALTER TABLE tmp_tableadd unique key `phone_deleted_at_unique`(`phone`, `deleted_at`);

如果对于一个已经上线了的应用，重新创表不太现实，还是需要进行数据库的迁移，有两种策略，一是替换掉del_flag 软删除改用 deleted_at 软删除的方式，然后添加联合唯一约束，二是再加一个 deleted_at，当要删除一个数据前，先调 update 修改 deleted_at，再删。由于这个项目使用的ORM是Mybatis Plus，目前还没调研怎么替换使用 deleted_at 来进行软删除，所以暂时只是新加了一列 deleted_at 来辅助创建联合唯一约束。数据库迁移步骤如下：

-- 首先删除 phone 的唯一约束
DROP INDEX phone_unique on users;

-- 添加一列 deleted_at
ALTER TABLE tmp_table ADD COLUMN deleted_at timestamp DEFAULT NULL AFTER updated_at;

-- 添加联合唯一约束
ALTER TABLE tmp_table add unique key `users_phone_deleted_at_unique`(`phone`, `deleted_at`);

-- 将已经软删除的数据添加上 deleted_at 的数据
UPDATE tmp_table SET users.deleted_at = users.updated_at where users.del_flag = 1;

这样就可以解决软删除和唯一约束共存时会导致的问题啦，不过这里还留了一个坑待填，那就是 Mybatis plus 怎么用 deleted_at 来进行软删除，这个在后续调研后再写一篇文章。