MySQL 7中使用复杂CHECK约束实现bmod对应id范围校验失败如何解决

• 语法错误：原约束语句中CASE判断缺少等于号，且嵌套CASE未正确闭合，导致SQL语法不合法无法执行
• 版本兼容问题：MySQL不存在7.x正式版本，你大概率使用的是MySQL 5.7，该版本及更早版本仅支持CHECK约束的语法书写，但不会实际触发校验逻辑，CHECK约束的实际生效支持从MySQL 8.0.16版本开始提供

方案1：CHECK约束（适配MySQL 8.0.16及以上版本） #

直接用逻辑运算符拼接校验条件，可读性更高，语法正确的写法如下：

ALTER TABLE src_branches
ADD CONSTRAINT bmod_to_id_check CHECK (
    (bmod = 7 AND id BETWEEN 0 AND 2)
    OR (bmod = 6 AND id BETWEEN 600 AND 699)
    OR (bmod = 5 AND id BETWEEN 500 AND 599)
    OR (bmod = 4 AND id BETWEEN 400 AND 499)
    OR (bmod = 3 AND id BETWEEN 300 AND 499)
    OR (bmod IN (1,2) AND id BETWEEN 2 AND 300)
    OR bmod NOT IN (1,2,3,4,5,6,7)
);

方案2：触发器实现（全版本兼容） #

如果使用的是MySQL 5.7及更低版本，可以通过BEFORE INSERT和BEFORE UPDATE触发器实现相同的校验逻辑：

插入数据校验触发器 #

DELIMITER //
CREATE TRIGGER check_bmod_id_before_insert BEFORE INSERT ON src_branches
FOR EACH ROW
BEGIN
    DECLARE is_valid BOOLEAN DEFAULT FALSE;
    CASE NEW.bmod
        WHEN 7 THEN SET is_valid = NEW.id BETWEEN 0 AND 2;
        WHEN 6 THEN SET is_valid = NEW.id BETWEEN 600 AND 699;
        WHEN 5 THEN SET is_valid = NEW.id BETWEEN 500 AND 599;
        WHEN 4 THEN SET is_valid = NEW.id BETWEEN 400 AND 499;
        WHEN 3 THEN SET is_valid = NEW.id BETWEEN 300 AND 499;
        WHEN 1,2 THEN SET is_valid = NEW.id BETWEEN 2 AND 300;
        ELSE SET is_valid = TRUE;
    END CASE;
    IF NOT is_valid THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'id取值不符合当前bmod对应的区间规则';
    END IF;
END //
DELIMITER ;

更新数据校验触发器 #

DELIMITER //
CREATE TRIGGER check_bmod_id_before_update BEFORE UPDATE ON src_branches
FOR EACH ROW
BEGIN
    DECLARE is_valid BOOLEAN DEFAULT FALSE;
    CASE NEW.bmod
        WHEN 7 THEN SET is_valid = NEW.id BETWEEN 0 AND 2;
        WHEN 6 THEN SET is_valid = NEW.id BETWEEN 600 AND 699;
        WHEN 5 THEN SET is_valid = NEW.id BETWEEN 500 AND 599;
        WHEN 4 THEN SET is_valid = NEW.id BETWEEN 400 AND 499;
        WHEN 3 THEN SET is_valid = NEW.id BETWEEN 300 AND 499;
        WHEN 1,2 THEN SET is_valid = NEW.id BETWEEN 2 AND 300;
        ELSE SET is_valid = TRUE;
    END CASE;
    IF NOT is_valid THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'id取值不符合当前bmod对应的区间规则';
    END IF;
END //
DELIMITER ;

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