如果表结构使用标志位进行软删除，比如：
CREATE TABLE `tb_user` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`nickname` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
`age` tinyint unsigned DEFAULT NULL,
`del_flag` tinyint NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
就无法用通常的方式添加唯一索引，如果只是为唯一字段添加索引，比如：
create unique index idx_unique_nickname on tb_user (nickname);
添加两条数据：
insert into `tb_user`(`nickname`, `name`, `age`, `del_flag`) values ('user1', '张三', 18, 0);
insert into `tb_user`(`nickname`, `name`, `age`, `del_flag`) values ('user2', '李四', 19, 0);
如果此时对一条数据进行软删除：
update `tb_user` set del_flag=1 where nickname='user1';
就会出现奇怪的现象，比如重新添加一条昵称为user1的用户信息：
insert into `tb_user`(`nickname`, `name`, `age`, `del_flag`) values ('user1', '张三', 18, 0);
会报错，因为唯一索引不允许。但是用户如果查询当前的有效数据：
select * from tb_user where del_flag=0;
就会发现并不存在一条昵称为user1的数据。
这个问题本质上是因为唯一索引只针对业务字段，并没有考虑删除标识，所以就会有已经被软删除的数据依然受到唯一索引约束，导致新的数据插入时冲突（报错）。
如果同时索引删除标识：
create unique index idx_unique_nickname on tb_user (nickname, del_flag);
同样是有问题的，此时插入不会有问题，但如果重复删除nickname重复的数据，依然会报错。
部分索引
实际上在其它数据库中，解决此类问题的最佳方式是使用部分索引（也被称作条件唯一索引），比如：
create unique index idx_unique_nickname on tb_user(nickname) where del_flag=0;
部分索引的意思是索引的数据并不是全表数据，而是依据 where 条件只索引其中的一部分数据。这样带来的一个额外好处是索引数据集小于全表，性能更好。
但可惜的是 MySQL 到目前（8.0.41）为止，依然不支持部分索引。
奇怪的是，中文互联网上有一些博文显示 MySQL 8.0.13 起支持部分索引，导致 DeepSeek 也会回答支持部分索引，但实际上是不支持的。
虽然 MySQL 不支持部分索引，但是可以用下面介绍的函数索引替代。
函数索引
MySQL 8 开始支持在索引中使用函数，可以利用这一点用函数索引实现部分索引的效果：
create unique index idx_unique_nickname on tb_user((case when del_flag=0 then nickname else null end));
实际上这样做后，MySQL 会创建一个虚拟列来保存被索引的值，按照函数中定义的规则，del_flag=0的数据，虚拟列中会保存nickname用于唯一索引，如果不是，则保存 null。众所周知，MySQL 不会索引 null 值，因此唯一索引只对del_flag=0的数据有效，其最终效果与部分索引类似。
还有需要注意的一点是，函数索引在检索（SELECT）时要能被利用，需要 WHERE 条件与函数完全一致，比如：
SELECT * FROM users WHERE (CASE WHEN del_flag = '0' THEN username ELSE NULL END) = 'alice';显然很难使用，因此函数索引在这里的主要用途是用于限制数据插入时的唯一性，查询优化可以单独创建其它普通索引。
Null 删除标识
如果删除标识可以为 null，可以通过将已软删除的数据的删除标识修改为 null 来解决这个问题。
这同样利用了 MySQL 索引不会索引 Null 值的特性。
表结构：
create table tb_user2
(
id bigint unsigned auto_increment
primary key,
nickname varchar(20) not null,
name varchar(20) not null,
age tinyint unsigned null,
del_flag tinyint
);
创建唯一索引：
create unique index idx_unique_nickname on tb_user2 (nickname, del_flag);
插入两条数据：
insert into `tb_user2`(`nickname`, `name`, `age`, `del_flag`) values ('user1', '张三', 18, 0);
insert into `tb_user2`(`nickname`, `name`, `age`, `del_flag`) values ('user2', '李四', 19, 0);
重复插入：
insert into `tb_user2`(`nickname`, `name`, `age`, `del_flag`) values ('user2', '李四', 19, 0);
会报错，唯一索引有效。
软删除：
update `tb_user2` set del_flag=null where nickname='user1';
注意，这种方案软删除要将删除标识设置为
null，而非常见的1。
重新插入：
insert into `tb_user2`(`nickname`, `name`, `age`, `del_flag`) values ('user1', '张三', 18, 0);
再次删除：
update `tb_user2` set del_flag=null where nickname='user1';
可以看到有两条 nickname 重复的数据都被软删除，说明这个方案是有效的。
del_time
有一些文章会提到可以使用del_time代替del_flag作为删除标识，其类型是时间戳（Timestamp）。为 null 的时候表示未删除，删除时写入当前时间戳，这样字段相同的数据可以被重复删除，因为时间戳不同。但问题是未删除的数据中，因为del_flag=null不会被索引，就会存在出现重复数据的问题，也就是说这种情况下创建的唯一索引是无效的。因此这种方案在 MySQL 中是不可行的。
总结
MySQL 目前不支持部分索引，因此推荐优先使用函数索引解决此类问题。
参考资料
文章评论