数据库逻辑删除，唯一性约束究极解决方案

一、写在前面

平常开发中，有些表数据需要记录历史，如果物理删除就再也无法查到了，这个时候通常是创建一个删除标识字段（未删除：0，已删除：1）用于标识数据是否删除：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COMMENT '姓名',
  `gender` varchar(20) COMMENT '性别',
  `address` varchar(255) COMMENT '地址',
  `id_card` varchar(255) NOT NULL COMMENT '身份证',
  `remark` varchar(255),
  `is_delete` varchar(255) COMMENT '是否删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

但是通常数据库中的数据是需要进行兜底的，比如说身份证需要唯一，但是如果加上删除标志，就无法保证唯一性了。
但是如果身份证和删除标识，这两个字段加上唯一索引，用户多次删除的时候，也是不行的。
删除标识这个时候需要怎么做呢？

二、解决方案

1、业务逻辑层面控制

数据库不做唯一性索引限制，在业务代码中控制。

def create_user(username):
    # 检查未删除的记录中是否存在相同username
    existing = db.query("SELECT id FROM user WHERE username = %s AND is_deleted = 0", username)
    if existing:
        raise ValueError("用户名已存在")
    db.execute("INSERT INTO user (username, is_deleted) VALUES (%s, 0)", username)

这样数据库无法兜底，并发操作需要加锁。

2、物理删除+数据归档

简单的方案就是做数据归档或者离线表。
就是说再创建一张user_record_his表，然后在用户删除时，在同一个事务中把user表中的数据插入到user_record_his中，然后再把user表中的记录删除：

insert into user_record_his;
delete from user where id=1;

还有一种归档方式是基于离线数仓，定时将用户信息进行同步，其中delete操作不同步，只同步insert、update。
这样的话，用户同一天删除又创建是有问题的，所以业务上通常会限制用户创建和删除的频率（3天一次）。

3、is_delete !=0的都认为是删除（推荐）

将id_card和is_delete设置为联合唯一索引。
正常数据的is_delete字段为0，如果执行删除操作，可以将该字段置为时间戳或者id。

update user set is_delete = '时间戳';
-- 或者
update user set is_delete = id;

该字段使用uuid、时间戳、递增都可以。

4、MySQL 函数索引（表达式索引）（需 MySQL 8.0+）（推荐）

创建基于表达式的唯一索引，仅对有效记录生效。

-- 原理：索引仅包含is_deleted=0时的username值，已删除记录的表达式结果为NULL（不参与唯一约束）。
-- 限制：MySQL 的函数索引要求表达式结果非NULL时才会被索引，需确保业务字段非空。
-- 或者 if((is_deleted = 1),NULL,1)
ALTER TABLE user 
ADD UNIQUE INDEX idx_unique_valid_idcard ((CASE WHEN is_delete = 0 THEN id_card END));

-- 数据测试
INSERT INTO `user`(`name`, `gender`, `address`, `id_card`, `remark`, `is_delete`) VALUES ('张三', '男', '山东', '123', NULL, '0');
update user set is_delete=1 where id_card='123' and is_delete=0;

5、部分索引（Partial Index）（需 MySQL 8.0.13+）（推荐）

这个需要mysql版本支持，我不知道我的8.0.23版本为什么不支持

ALTER TABLE user
ADD UNIQUE INDEX idx_idcard_not_deleted (id_card) WHERE is_deleted = 0;

原理：索引仅覆盖is_deleted=0的记录，已删除记录不参与唯一性校验。
注意：MySQL 的部分索引功能在 8.0.13 + 版本支持，但实际使用时需验证兼容性（部分云数据库可能未完全开放）。