如何阻止小数存入MySQL的pricePerNight列？解决CHECK约束无效问题

问题场景 #

现有Cabin表结构如下：

CREATE TABLE IF NOT EXISTS Cabin (
cabinID BIGINT AUTO_INCREMENT PRIMARY KEY,
cabinType VARCHAR(150) NOT NULL,
cabinDescription VARCHAR(255),
pricePerNight BIGINT NOT NULL,
pricePerWeek DECIMAL(10,2) NOT NULL,
photo VARCHAR(50)
);

执行以下插入语句时，pricePerNight字段传入了小数100.87，预期应该触发错误，但实际数据被自动取整为101存入数据库：

INSERT INTO Cabin (cabinType, cabinDescription, pricePerNight, pricePerWeek, photo)
VALUES ('test cabin 2', 'test cabin with invalid data', 100.87, 625, 'insertCabin1.jpg');

尝试添加CHECK约束后仍无法阻止该行为：

pricePerNight BIGINT(10) NOT NULL CHECK (pricePerNight >= 0 AND pricePerNight % 1 = 0)

需求：向pricePerNight插入小数时直接触发错误，而非自动取整保存。

解决方法 #

1. 启用MySQL严格模式 #

MySQL默认的宽松模式会自动转换不符合列类型的数据（如将小数转为整数）且不报错，需修改sql_mode启用严格模式：

• 临时生效（重启MySQL后失效）：

-- 全局生效
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
-- 当前会话生效
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

• 永久生效：
  找到MySQL配置文件（如my.cnf或my.ini），在[mysqld]段添加：

sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

保存后重启MySQL服务即可。

启用严格模式后，向BIGINT列插入小数时会直接抛出Data truncation错误，阻止数据插入。

2. 用触发器校验（适配MySQL 8.0.16以下版本） #

MySQL 8.0.16之前的版本不会强制执行CHECK约束，若使用旧版本，可通过触发器实现校验：

DELIMITER //
CREATE TRIGGER validate_pricePerNight_before_insert
BEFORE INSERT ON Cabin
FOR EACH ROW
BEGIN
    IF NEW.pricePerNight <> FLOOR(NEW.pricePerNight) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'pricePerNight必须是整数';
    END IF;
END //
DELIMITER ;

该触发器会在插入数据前检查pricePerNight是否为整数，若不是则抛出错误，阻止插入。

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