MySQL邏輯刪除與唯一索引沖突解決方案

更新時間：2025年07月16日 14:27:00 作者：yifanghub

本文探討MySQL邏輯刪除與唯一索引沖突問題,提出四種解決方案：復(fù)合索引+時間戳、修改唯一字段、歷史表、業(yè)務(wù)層校驗(yàn),推薦方案1和方案3,適用于不同場景,感興趣的朋友一起看看吧

問題背景

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

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

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

問題復(fù)現(xiàn)

1.創(chuàng)建用戶表

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 '邏輯刪除標(biāo)記，用于標(biāo)記當(dāng)前記錄是否已刪除，0：否，1：是'
);

2.執(zhí)行腳本：

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

在執(zhí)行第三步時，會報錯如下：

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

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

解決方案

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

改動點(diǎn)：
1）添加一個字段delete_time，用于記錄被刪除的時間，默認(rèn)值為NULL，當(dāng)刪除該記錄時將該字段設(shè)置為當(dāng)前時間
2）新建復(fù)合唯一索引，將用戶名username和刪除時間delete_time字段包含在復(fù)合唯一索引中

-- 方案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 '邏輯刪除標(biāo)記，用于標(biāo)記當(dāng)前記錄是否已刪除，0：否，1：是',
    delete_time DATETIME NULL DEFAULT NULL COMMENT '邏輯刪除時間，默認(rèn)為NULL'
);
-- 添加復(fù)合唯一索引
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');
-- 邏輯刪除用戶（設(shè)置刪除時間）
UPDATE users_test1 SET is_delete = 1,delete_time = NOW() WHERE username = 'tom';
-- 創(chuàng)建同名新用戶（delete_time為NULL）
INSERT INTO users_test1 (username, email) VALUES ('tom', 'new_tom@example.com');

執(zhí)行完上面腳本發(fā)現(xiàn)并沒有報錯，執(zhí)行查詢sql

select * from users_test1;

結(jié)果如下：

+--+--------+-------------------+---------+-------------------+
|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：刪除后修改唯一字段值

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

-- 方案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 '邏輯刪除標(biāo)記，用于標(biāo)記當(dāng)前記錄是否已刪除，0：否，1：是'
);
-- 添加復(fù)合唯一索引
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';
-- 創(chuàng)建同名新用戶
INSERT INTO users_test2 (username, email) VALUES ('tom', 'new_tom@example.com');
select * from users_test2;

查詢結(jié)果示例：

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

解決方案3. 使用歷史表

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

-- 方案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 '邏輯刪除標(biāo)記，用于標(biāo)記當(dāng)前記錄是否已刪除，0：否，1：是'
);
-- 添加復(fù)合唯一索引
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 '邏輯刪除標(biāo)記，用于標(biāo)記當(dāng)前記錄是否已刪除，0：否，1：是'
);
-- 添加歷史表復(fù)合唯一索引
ALTER TABLE users_test3_deleted 
ADD UNIQUE INDEX idx_unique_username (username);
-- 插入測試數(shù)據(jù)
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';
-- 刪除原紀(jì)錄
DELETE FROM users_test3 WHERE username = 'tom';
-- 可以重新創(chuàng)建原用戶名
INSERT INTO users_test3 (username, email) VALUES ('tom', 'new_tom@example.com');
select * from users_test3;
select * from users_test3_deleted;

解決方案4. 業(yè)務(wù)層校驗(yàn)+更新記錄

修改點(diǎn)：
保持唯一索引不變，在業(yè)務(wù)層處理沖突

-- 創(chuàng)建表（普通唯一索引）
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 '邏輯刪除標(biāo)記，用于標(biāo)記當(dāng)前記錄是否已刪除，0：否，1：是'
);
-- 業(yè)務(wù)層邏輯示例偽代碼：
/*
1. 先查詢是否存在已刪除的同名用戶
   SELECT id FROM users_test4 WHERE username = ? AND is_delete = 1
2. 如果存在，則更新原記錄（恢復(fù)）
   UPDATE users_test4 SET is_delete = 0, email = ? WHERE username = ?
3. 如果不存在，則新建記錄
   INSERT INTO users_test4 (username, email) VALUES (?, ?)
*/

總結(jié)

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

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

到此這篇關(guān)于MySQL邏輯刪除與唯一索引沖突解決的文章就介紹到這了,更多相關(guān)mysql邏輯刪除與唯一索引沖突內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: