创建city_CC表时numberOfNights列CHECK约束失效，是否需触发器？

嘿，我之前也踩过MySQL CHECK约束的坑，尤其是碰到“同语法别的表能用，这张表不行”的情况，特别闹心。咱们一步步来拆解问题，找到解决办法～

先排查约束不生效的可能原因 #

1. 数据库引擎/版本的兼容性问题 #

MySQL的InnoDB引擎在8.0.16版本之前，虽然允许你写CHECK约束的语法，但实际上不会强制执行——相当于只是“摆设”。而MyISAM引擎是一直支持CHECK约束生效的。

你可以先查一下当前表的引擎和数据库版本：

-- 查看表引擎
SHOW CREATE TABLE city_CC;
-- 查看MySQL版本
SELECT VERSION();

如果你的数据库版本低于8.0.16，且city_CC用的是InnoDB，那大概率是这个原因。至于之前的表能生效，可能那张表用的是MyISAM，或者是在升级到8.0.16之后创建的？

2. 约束语法的小疏漏 #

你的代码片段没写完，可能约束的写法有细微错误？正确的CHECK约束写法应该是这两种之一：

-- 写法1：直接跟在字段后
CREATE TABLE city_CC (
    cityName VARCHAR(255) PRIMARY KEY,
    numberOfNights INT CHECK (numberOfNights >= 1 AND numberOfNights <= 3)
);

-- 写法2：命名约束（更清晰，方便后续修改）
CREATE TABLE city_CC (
    cityName VARCHAR(255) PRIMARY KEY,
    numberOfNights INT,
    CONSTRAINT chk_number_of_nights CHECK (numberOfNights >= 1 AND numberOfNights <= 3)
);

检查一下是不是漏了AND，或者括号位置写错了——有时候语法没错但逻辑漏了，也会导致约束不生效。

3. 插入了NULL值 #

CHECK约束对NULL是放行的（因为NULL不满足>=1也不满足<=3，但MySQL认为“未知”的条件不会触发约束）。如果你插入的numberOfNights是NULL，约束不会报错。可以试试插入一个明确超出范围的值（比如4），看数据库会不会拦截：

INSERT INTO city_CC (cityName, numberOfNights) VALUES ('London', 4);

如果这条语句能成功执行，那确实是约束没生效。

对应的解决方案 #

方案1：如果MySQL版本低于8.0.16（InnoDB） #

这时候就得用触发器来替代CHECK约束，确保插入/更新时的数值符合要求。创建两个触发器：

-- 先修改分隔符，避免触发器里的分号冲突
DELIMITER //

-- 插入前检查
CREATE TRIGGER trg_nights_check_insert BEFORE INSERT ON city_CC
FOR EACH ROW
BEGIN
    IF NEW.numberOfNights < 1 OR NEW.numberOfNights > 3 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'numberOfNights必须在1到3之间';
    END IF;
END //

-- 更新前检查
CREATE TRIGGER trg_nights_check_update BEFORE UPDATE ON city_CC
FOR EACH ROW
BEGIN
    IF NEW.numberOfNights < 1 OR NEW.numberOfNights > 3 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'numberOfNights必须在1到3之间';
    END IF;
END //

-- 恢复分隔符
DELIMITER ;

这样不管是插入新数据还是更新现有数据，只要数值不符合要求，都会抛出错误。

方案2：如果MySQL版本≥8.0.16 #

直接用正确的语法重新创建约束就行。如果表已经创建好了，可以用ALTER TABLE添加：

ALTER TABLE city_CC
ADD CONSTRAINT chk_number_of_nights CHECK (numberOfNights >= 1 AND numberOfNights <= 3);

添加完之后再测试插入超出范围的值，应该会直接报错，说明约束生效了。

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