MySQL字段设置UNIQUE约束时如何允许ANONYMOUS与NULL例外？

嘿，这个需求挺常见的——既要保证特定字段的唯一性，又要给ANONYMOUS和NULL开“绿灯”。我给你分享几种在MySQL里实现的方案，你可以根据自己的MySQL版本选最合适的：

方案一：部分唯一索引（MySQL 8.0.13+ 推荐） #

从MySQL 8.0.13开始，支持创建部分唯一索引，也就是只对符合特定条件的行强制执行唯一约束，正好能精准匹配你的需求：

CREATE UNIQUE INDEX idx_unique_mail_except_anon_null 
ON your_table (field_mail)
WHERE field_mail IS NOT NULL AND field_mail != 'ANONYMOUS';

原理说明： #

这个索引只会对field_mail既不是NULL也不是ANONYMOUS的行生效——这类行的邮箱值必须唯一；而NULL或ANONYMOUS的行可以随意重复插入，完全不受约束。这是最原生、性能最优的方案。

方案二：函数式唯一索引（MySQL 8.0+ 适用） #

如果你的MySQL版本是8.0但低于8.0.13，还不支持部分索引，可以用函数式唯一索引，通过函数把例外值统一映射成NULL（MySQL的唯一索引允许多个NULL共存）：

CREATE UNIQUE INDEX idx_unique_mail_functional
ON your_table (
  IF(field_mail IS NOT NULL AND field_mail != 'ANONYMOUS', field_mail, NULL)
);

原理说明： #

• 正常的邮箱值会被直接保留在索引里，必须唯一；
• ANONYMOUS和NULL会被转成NULL，多个NULL在唯一索引里不会触发冲突，完美满足重复插入的需求。

方案三：触发器（兼容MySQL 5.x 及更低版本） #

如果你的MySQL版本比较老，不支持上面两种索引，那就用BEFORE INSERT/BEFORE UPDATE触发器手动检查约束：

第一步：创建插入前检查触发器 #

DELIMITER //
CREATE TRIGGER trg_check_unique_mail_insert
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
  IF NEW.field_mail IS NOT NULL AND NEW.field_mail != 'ANONYMOUS' THEN
    IF EXISTS (
      SELECT 1 FROM your_table 
      WHERE field_mail = NEW.field_mail
    ) THEN
      SIGNAL SQLSTATE '45000' 
      SET MESSAGE_TEXT = 'field_mail必须唯一（ANONYMOUS或NULL除外）';
    END IF;
  END IF;
END //
DELIMITER ;

第二步：创建更新前检查触发器 #

DELIMITER //
CREATE TRIGGER trg_check_unique_mail_update
BEFORE UPDATE ON your_table
FOR EACH ROW
BEGIN
  IF NEW.field_mail IS NOT NULL AND NEW.field_mail != 'ANONYMOUS' THEN
    IF EXISTS (
      SELECT 1 FROM your_table 
      WHERE field_mail = NEW.field_mail
      AND id != NEW.id -- 排除当前行，避免更新时自己和自己冲突
    ) THEN
      SIGNAL SQLSTATE '45000' 
      SET MESSAGE_TEXT = 'field_mail必须唯一（ANONYMOUS或NULL除外）';
    END IF;
  END IF;
END //
DELIMITER ;

原理说明： #

触发器会在插入或更新行之前，先判断新的field_mail是否属于需要唯一约束的范围（非NULL且非ANONYMOUS），如果是，就检查表中是否已存在相同值，存在则抛出错误阻止操作。

小提示 #

• 优先选方案一，原生索引的性能比触发器好很多，维护起来也更简单；
• 用函数式索引时，要确保映射逻辑不会把正常邮箱值转成重复内容；
• 触发器记得同时处理插入和更新操作，更新时一定要排除当前行哦。

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