MySQL实现列值唯一但允许‘Deleted’例外的方法

MySQL支持带WHERE子句的唯一索引吗？ #

没错，MySQL不支持在CREATE UNIQUE INDEX语句中使用WHERE子句来创建带条件的唯一索引——也就是你想要的那种“除了'Deleted'之外都唯一”的部分索引。这种特性在PostgreSQL这类数据库中存在，但MySQL目前还没有提供该功能，这就是你收到报错的原因。

实现“列值唯一但允许一个例外”的替代方法 #

方法1：触发器+唯一索引 #

先创建普通的唯一索引，再通过触发器处理特殊逻辑：

1. 建立基础唯一索引：

CREATE UNIQUE INDEX unique_user ON User(Username);

2. 编写两个触发器，分别处理插入和更新场景：当用户名不是'Deleted'时校验唯一性，是'Deleted'则跳过校验

DELIMITER //
CREATE TRIGGER check_username_insert BEFORE INSERT ON User
FOR EACH ROW
BEGIN
    IF NEW.Username != 'Deleted' AND EXISTS (SELECT 1 FROM User WHERE Username = NEW.Username) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该用户名已存在（Deleted除外）';
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER check_username_update BEFORE UPDATE ON User
FOR EACH ROW
BEGIN
    IF NEW.Username != 'Deleted' AND EXISTS (SELECT 1 FROM User WHERE Username = NEW.Username AND ID != NEW.ID) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该用户名已存在（Deleted除外）';
    END IF;
END //
DELIMITER ;

方法2：生成列+唯一索引 #

利用MySQL的生成列特性，生成一个带区分逻辑的辅助列，再对该列加唯一索引：

ALTER TABLE User ADD COLUMN unique_username VARCHAR(255) AS (
    CASE WHEN Username = 'Deleted' THEN CONCAT('Deleted_', ID) ELSE Username END
) STORED;

CREATE UNIQUE INDEX unique_user ON User(unique_username);

被标记为'Deleted'的用户，会生成Deleted_用户ID格式的唯一值；正常用户名则直接使用原内容，既满足删除用户的需求，又保证了非Deleted用户名的唯一性。

方法3：联合唯一索引 #

如果用户表有主键（比如ID），可以创建包含条件表达式的联合唯一索引：

CREATE UNIQUE INDEX unique_user ON User(Username, (CASE WHEN Username = 'Deleted' THEN ID ELSE NULL END));

MySQL中联合索引的NULL值不会触发唯一校验：当用户名是'Deleted'时，第二个字段为NULL，允许多条记录存在；当用户名不是'Deleted'时，第二个字段同样为NULL，但第一个字段会被唯一约束，确保不会重复。

内容的提问来源于stack exchange，提问作者chiaseed