MySQL中针对多条件分支的表如何添加Unique约束？

根据你的业务规则，需要针对不同的Field2、Field4取值组合，应用不同的唯一性校验逻辑。以下是两种可行的解决方案：

方案一：使用部分唯一索引（MySQL 8.0.13及以上版本） #

MySQL 8.0.13开始支持带WHERE子句的部分唯一索引，可以精准匹配每个业务场景，性能最优且是数据库原生约束：

1. 当Field2='No'时：仅校验(field1, field2)的唯一性

CREATE UNIQUE INDEX idx_unique_field2_no ON channels (field1, field2)
WHERE field2 = 'No';

2. 当Field2='Yes'且Field4='No'时：校验(field1, field2, field3, field4)的唯一性

CREATE UNIQUE INDEX idx_unique_field2_yes_field4_no ON channels (field1, field2, field3, field4)
WHERE field2 = 'Yes' AND field4 = 'No';

3. 当Field2='Yes'且Field4='Yes'时：校验(field1, field2, field3, field4, field5)的唯一性

CREATE UNIQUE INDEX idx_unique_field2_yes_field4_yes ON channels (field1, field2, field3, field4, field5)
WHERE field2 = 'Yes' AND field4 = 'Yes';

说明 #

• 部分索引只会对满足WHERE条件的行生效，不同场景的索引互不干扰
• 注意字段存储值的一致性：如果Field4是TINYINT类型（0代表No，1代表Yes），需要把条件里的'No'/'Yes'改成对应的数值

方案二：使用触发器（兼容低版本MySQL） #

如果你的MySQL版本低于8.0.13，不支持部分索引，可以通过触发器实现自定义唯一性校验：

插入前校验触发器 #

DELIMITER //
CREATE TRIGGER check_unique_before_insert
BEFORE INSERT ON channels
FOR EACH ROW
BEGIN
    -- 场景1：Field2为No时
    IF NEW.field2 = 'No' THEN
        IF EXISTS (SELECT 1 FROM channels WHERE field1 = NEW.field1 AND field2 = 'No') THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '重复的(field1, field2)组合（Field2为No时）';
        END IF;
    -- 场景2：Field2为Yes且Field4为No时
    ELSEIF NEW.field2 = 'Yes' AND NEW.field4 = 'No' THEN
        IF EXISTS (SELECT 1 FROM channels WHERE field1 = NEW.field1 AND field2 = 'Yes' AND field3 = NEW.field3 AND field4 = 'No') THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '重复的(field1, field2, field3, field4)组合（Field2为Yes且Field4为No时）';
        END IF;
    -- 场景3：Field2为Yes且Field4为Yes时
    ELSEIF NEW.field2 = 'Yes' AND NEW.field4 = 'Yes' THEN
        IF EXISTS (SELECT 1 FROM channels WHERE field1 = NEW.field1 AND field2 = 'Yes' AND field3 = NEW.field3 AND field4 = 'Yes' AND field5 = NEW.field5) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '重复的(field1, field2, field3, field4, field5)组合（Field2为Yes且Field4为Yes时）';
        END IF;
    END IF;
END //
DELIMITER ;

更新前校验触发器 #

DELIMITER //
CREATE TRIGGER check_unique_before_update
BEFORE UPDATE ON channels
FOR EACH ROW
BEGIN
    -- 场景1：Field2为No时
    IF NEW.field2 = 'No' THEN
        IF EXISTS (SELECT 1 FROM channels WHERE field1 = NEW.field1 AND field2 = 'No' AND id != NEW.id) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '重复的(field1, field2)组合（Field2为No时）';
        END IF;
    -- 场景2：Field2为Yes且Field4为No时
    ELSEIF NEW.field2 = 'Yes' AND NEW.field4 = 'No' THEN
        IF EXISTS (SELECT 1 FROM channels WHERE field1 = NEW.field1 AND field2 = 'Yes' AND field3 = NEW.field3 AND field4 = 'No' AND id != NEW.id) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '重复的(field1, field2, field3, field4)组合（Field2为Yes且Field4为No时）';
        END IF;
    -- 场景3：Field2为Yes且Field4为Yes时
    ELSEIF NEW.field2 = 'Yes' AND NEW.field4 = 'Yes' THEN
        IF EXISTS (SELECT 1 FROM channels WHERE field1 = NEW.field1 AND field2 = 'Yes' AND field3 = NEW.field3 AND field4 = 'Yes' AND field5 = NEW.field5 AND id != NEW.id) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '重复的(field1, field2, field3, field4, field5)组合（Field2为Yes且Field4为Yes时）';
        END IF;
    END IF;
END //
DELIMITER ;

说明 #

• 触发器中的id是表的主键字段，用于排除当前更新的行自身，若主键名称不同请替换
• 触发器会在插入/更新前执行校验，违反规则时抛出自定义错误信息

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