MySQL中含BETWEEN运算符的CHECK约束不生效问题求解

嘿，这个问题我碰到过好几次！你添加了CHECK约束却没触发报错，大概率是数据库本身的特性或者约束设置的问题，分两种常见场景给你解决思路：

场景一：你用的是MySQL数据库（5.7及更早版本） #

MySQL在这些旧版本里有个坑：它允许你写CHECK约束的语法，但实际上根本不会强制执行，只是把约束定义存在数据字典里而已，这就是为啥你插20000能成功。

解决办法有两个：

• 升级到MySQL 8.0+：从8.0版本开始，MySQL才真正支持并强制执行CHECK约束，升级后你的原有约束就能正常工作了。
• 用触发器替代CHECK约束：如果没法升级，就写个触发器在插入/更新时检查薪资范围，示例代码如下：

  DELIMITER //
  -- 插入前检查
  CREATE TRIGGER check_emp_sal_insert
  BEFORE INSERT ON EMP
  FOR EACH ROW
  BEGIN
      IF NEW.SAL NOT BETWEEN 50000 AND 100000 THEN
          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '薪资必须在50000到100000之间';
      END IF;
  END //
  DELIMITER ;
  
  -- 更新前检查
  DELIMITER //
  CREATE TRIGGER check_emp_sal_update
  BEFORE UPDATE ON EMP
  FOR EACH ROW
  BEGIN
      IF NEW.SAL NOT BETWEEN 50000 AND 100000 THEN
          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '薪资必须在50000到100000之间';
      END IF;
  END //
  DELIMITER ;
  

场景二：你用的是Oracle数据库 #

如果是Oracle，那可能是约束的检查时机被延迟了，或者约束被禁用了：

• 检查约束是否延迟验证：如果创建约束时没指定NOT DEFERRABLE，Oracle默认是DEFERRABLE INITIALLY DEFERRED，也就是要等到事务提交时才检查约束。你可以试试提交事务，这时候应该会报错。如果想让约束立即生效，重新创建约束时加上NOT DEFERRABLE：

  ALTER TABLE EMP ADD CONSTRAINT chk_emp_sal CHECK(SAL BETWEEN 50000 AND 100000) NOT DEFERRABLE;
  

• 检查约束状态：也有可能约束被意外禁用了，执行下面的SQL查看状态：

  SELECT constraint_name, status FROM user_constraints WHERE table_name = 'EMP' AND constraint_type = 'C';
  

  如果结果里status是DISABLED，就执行下面的语句启用约束：

  ALTER TABLE EMP ENABLE CONSTRAINT chk_emp_sal;
  

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