一、寫在前面

平常開發中，有些表數據需要記錄歷史，如果物理刪除就再也無法查到了，這個時候通常是創建一個刪除標識字段（未刪除：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):# 檢查未刪除的記錄中是否存在相同usernameexisting = 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 + 版本支持，但實際使用時需驗證兼容性（部分云數據庫可能未完全開放）。