問題背景

在MySQL數據庫設計中，邏輯刪除（軟刪除）是一種常見的實踐，它通過設置標志位（如is_delete）來標記記錄被"刪除"，而不是實際刪除數據。然而，當表中存在唯一約束時，如在用戶表中我們要求用戶名必須唯一，并且用戶數據不要物理刪除，那這個時候可能會產生一個問題：

• 用戶A（username=“Tom”)被邏輯刪除（is_delete=1）
• 新用戶嘗試使用username="Tom"注冊時
• 唯一約束阻止創建新記錄，即使原始用戶已被"刪除"

本文將介紹解決此問題的方案。

問題復現

1.創建用戶表

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY ,username VARCHAR(50) NOT NULL COMMENT '用戶名',email VARCHAR(100) NOT NULL COMMENT '用戶郵箱',is_delete TINYINT(1) NOT NULL DEFAULT 0 COMMENT '邏輯刪除標記，用于標記當前記錄是否已刪除，0：否，1：是'
);

2.執行腳本：

-- 插入一條測試數據，用戶名為：tom
INSERT INTO users (username, email) VALUES ('tom', 'tom@example.com');-- 邏輯刪除tom用戶（is_delet設置為1）
UPDATE users SET is_delete = 1 WHERE username = 'tom';-- 創建同名用戶（is_delete不同）
INSERT INTO users (username, email) VALUES ('tom', 'new_tom@example.com');

在執行第三步時，會報錯如下：

[23000][1062] Duplicate entry 'tom' for key 'users.idx_uq_username'

原因分析：從結果可以看到，在插入相同名字的記錄時，違反了唯一約束idx_uq_username，但實際上用戶tom已經刪除了，唯一索引阻止了用戶名=tom的記錄插入。

解決方案

解決方案1.復合唯一索引 + 時間戳刪除字段

改動點：
1）添加一個字段delete_time，用于記錄被刪除的時間，默認值為NULL，當刪除該記錄時將該字段設置為當前時間
2）新建復合唯一索引，將用戶名username和刪除時間delete_time字段包含在復合唯一索引中

-- 方案1
CREATE TABLE users_test1 (id INT AUTO_INCREMENT PRIMARY KEY ,username VARCHAR(50) NOT NULL COMMENT '用戶名',email VARCHAR(100) NOT NULL COMMENT '用戶郵箱',is_delete TINYINT(1) NOT NULL DEFAULT 0 COMMENT '邏輯刪除標記，用于標記當前記錄是否已刪除，0：否，1：是',delete_time DATETIME NULL DEFAULT NULL COMMENT '邏輯刪除時間，默認為NULL'
);-- 添加復合唯一索引
ALTER TABLE users_test1
ADD UNIQUE INDEX idx_unique_username_dt (username, delete_time);-- 插入初始用戶
INSERT INTO users_test1 (username, email) VALUES ('tom', 'tom@example.com');-- 邏輯刪除用戶（設置刪除時間）
UPDATE users_test1 SET is_delete = 1,delete_time = NOW() WHERE username = 'tom';-- 創建同名新用戶（delete_time為NULL）
INSERT INTO users_test1 (username, email) VALUES ('tom', 'new_tom@example.com');

執行完上面腳本發現并沒有報錯，執行查詢sql

select * from users_test1;

結果如下：

+--+--------+-------------------+---------+-------------------+
|id|username|email              |is_delete|delete_time        |
+--+--------+-------------------+---------+-------------------+
|1 |tom     |tom@example.com    |1        |2025-07-13 14:55:59|
|2 |tom     |new_tom@example.com|0        |null               |
+--+--------+-------------------+---------+-------------------+

解決方案2：刪除后修改唯一字段值

改動點：
在邏輯刪除時，為唯一字段添加特定前綴/后綴，使其不再與原有值沖突

-- 方案2 刪除后修改唯一字段值
CREATE TABLE users_test2 (id INT AUTO_INCREMENT PRIMARY KEY ,username VARCHAR(50) NOT NULL COMMENT '用戶名',email VARCHAR(100) NOT NULL COMMENT '用戶郵箱',is_delete INT(1) NULL DEFAULT 0 COMMENT '邏輯刪除標記，用于標記當前記錄是否已刪除，0：否，1：是'
);-- 添加復合唯一索引
ALTER TABLE users_test2
ADD UNIQUE INDEX idx_unique_username (username);-- 插入初始用戶
INSERT INTO users_test2 (username, email) VALUES ('tom', 'tom@example.com');-- 邏輯刪除用戶,修改用戶名
UPDATE users_test2 SET is_delete = 1,username = CONCAT(username, '_deleted_', UUID_SHORT()) WHERE username = 'tom';-- 創建同名新用戶
INSERT INTO users_test2 (username, email) VALUES ('tom', 'new_tom@example.com');select * from users_test2;

查詢結果示例：

+--+------------------------------+-------------------+---------+
|id|username                      |email              |is_delete|
+--+------------------------------+-------------------+---------+
|1 |tom_deleted_100950808475992064|tom@example.com    |1        |
|2 |tom                           |new_tom@example.com|0        |
+--+------------------------------+-------------------+---------+

解決方案3. 使用歷史表

修改點：將刪除的記錄移動到專門的歷史表，主表只保留有效記錄

-- 方案3
-- 主表（活躍用戶）
CREATE TABLE users_test3 (id INT AUTO_INCREMENT PRIMARY KEY ,username VARCHAR(50) NOT NULL COMMENT '用戶名',email VARCHAR(100) NOT NULL COMMENT '用戶郵箱',is_delete INT(1) NULL DEFAULT 0 COMMENT '邏輯刪除標記，用于標記當前記錄是否已刪除，0：否，1：是'
);-- 添加復合唯一索引
ALTER TABLE users_test3
ADD UNIQUE INDEX idx_unique_username (username);-- 歷史表（已刪除用戶）
CREATE TABLE users_test3_deleted (id INT AUTO_INCREMENT PRIMARY KEY ,username VARCHAR(50) NOT NULL COMMENT '用戶名',email VARCHAR(100) NOT NULL COMMENT '用戶郵箱',is_delete INT(1) NULL DEFAULT 0 COMMENT '邏輯刪除標記，用于標記當前記錄是否已刪除，0：否，1：是'
);-- 添加歷史表復合唯一索引
ALTER TABLE users_test3_deleted 
ADD UNIQUE INDEX idx_unique_username (username);-- 插入測試數據
INSERT INTO users_test3 (username, email) VALUES ('tom', 'tom@example.com');-- 邏輯刪除：移動到歷史表
INSERT INTO users_test3_deleted (id, username, email,is_delete)
SELECT id, username, email,1 FROM users_test3 WHERE username = 'tom';
-- 刪除原紀錄
DELETE FROM users_test3 WHERE username = 'tom';-- 可以重新創建原用戶名
INSERT INTO users_test3 (username, email) VALUES ('tom', 'new_tom@example.com');select * from users_test3;
select * from users_test3_deleted;

解決方案4. 業務層校驗+更新記錄

修改點：
保持唯一索引不變，在業務層處理沖突

-- 創建表（普通唯一索引）
CREATE TABLE users_test4 (id INT AUTO_INCREMENT PRIMARY KEY ,username VARCHAR(50) NOT NULL COMMENT '用戶名',email VARCHAR(100) NOT NULL COMMENT '用戶郵箱',is_delete INT(1) NULL DEFAULT 0 COMMENT '邏輯刪除標記，用于標記當前記錄是否已刪除，0：否，1：是'
);-- 業務層邏輯示例偽代碼：
/*
1. 先查詢是否存在已刪除的同名用戶SELECT id FROM users_test4 WHERE username = ? AND is_delete = 1
2. 如果存在，則更新原記錄（恢復）UPDATE users_test4 SET is_delete = 0, email = ? WHERE username = ?
3. 如果不存在，則新建記錄INSERT INTO users_test4 (username, email) VALUES (?, ?)
*/

總結

• 復合唯一索引 + 時間戳刪除字段：需要需修改表結構，適用于新項目設計，需保留完整數據歷史且查詢頻繁的系統；
• 刪除后修改唯一字段值：需要修改業務字段（如用戶名），可能影響日志或歷史記錄追溯，適用于臨時解決方案；
• 使用歷史表：需同步維護兩個表結構，備份恢復方便，查詢主表的數據量比較小，查詢效率高，適用于數據量大、刪除頻繁且需要嚴格區分活躍/歷史數據的系統；
• 業務層校驗+更新記錄：不用修改字段，需編寫額外校驗和恢復邏輯，可能存在并發問題風險；

綜合以上，建議采用方案1和方案3