MySQL如何设置字段值范围在0到100之间？

Question

在MySQL数据库设计中，如何设置某个字段的取值范围限制在0到100之间？例如，常用于存储百分比、评分等场景。常见的实现方式有哪些？是否可以通过字段类型定义直接限制数值范围？如果使用TINYINT UNSIGNED，其取值范围为0到255，能否进一步限制为0到100？若不能，应如何通过CHECK约束或触发器（Trigger）等方式实现该限制？此外，在MySQL 8.0及以上版本中，是否支持原生的CHECK约束校验？如何编写正确的建表语句以确保字段值只能在0到100之间？这些方法在实际应用中各有哪些优缺点？

Answer 1

一、背景与需求分析

在MySQL数据库设计中，常常需要对某些字段的取值范围进行限制。例如，在存储百分比、评分等场景时，我们希望某个字段只能取0到100之间的整数值。虽然MySQL提供了多种数据类型来表示数字，但是否能直接通过字段定义实现严格的取值范围限制，是值得深入探讨的问题。

二、字段类型的初步选择

常见的整数类型包括：

• TINYINT UNSIGNED：0 ~ 255
• SMALLINT UNSIGNED：0 ~ 65535
• INT UNSIGNED：0 ~ 4294967295

若使用TINYINT UNSIGNED，其理论范围为0~255，无法直接限制为0~100。因此，仅靠字段类型定义不能满足该需求，必须借助其他机制。

三、CHECK约束（MySQL 8.0及以上支持）

从MySQL 8.0开始，正式支持原生的CHECK约束，可以在建表时定义字段的取值范围限制。

CREATE TABLE example_score (
    id INT AUTO_INCREMENT PRIMARY KEY,
    score TINYINT UNSIGNED,
    CHECK (score BETWEEN 0 AND 100)
);

上述语句确保了score字段的值必须在0到100之间。如果插入或更新操作违反此约束，MySQL将拒绝执行并抛出错误。

四、触发器（Trigger）方式实现校验

对于不支持原生CHECK约束的早期版本（如MySQL 5.x），可以使用触发器进行手动校验。

DELIMITER //
CREATE TRIGGER before_insert_score
BEFORE INSERT ON example_score
FOR EACH ROW
BEGIN
    IF NEW.score < 0 OR NEW.score > 100 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Score must be between 0 and 100';
    END IF;
END//
DELIMITER ;

DELIMITER //
CREATE TRIGGER before_update_score
BEFORE UPDATE ON example_score
FOR EACH ROW
BEGIN
    IF NEW.score < 0 OR NEW.score > 100 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Score must be between 0 and 100';
    END IF;
END//
DELIMITER ;

这种方式适用于兼容性要求较高的项目，但会增加系统开销，并且逻辑维护较为复杂。

五、应用层校验（推荐作为补充手段）

除了数据库层面的校验外，通常建议在业务逻辑层（如Java、Python、Node.js等）也加入字段值的合法性判断。这样可以提前拦截非法数据，减少不必要的数据库交互。

六、不同方法对比分析

方法	优点	缺点	适用场景
字段类型定义	简单高效，占用资源少	无法精确控制范围（如TINYINT只能控制到0-255）	仅需大致范围控制
CHECK约束（MySQL 8.0+）	原生支持，语法简洁，易于维护	仅限于MySQL 8.0及以上版本	现代数据库架构，追求代码简洁
触发器	兼容性强，可定制化程度高	性能略差，调试和维护成本较高	老旧系统升级困难时使用
应用层校验	统一处理逻辑，提升整体健壮性	依赖开发规范，容易被忽略	所有系统都应采用的基本做法

七、综合建议与最佳实践

1. 优先使用MySQL 8.0的CHECK约束，确保数据一致性；
2. 对于旧版本，结合触发器与应用层双重校验；
3. 避免单纯依赖字段类型限制，因为它们无法做到精确控制；
4. 在关键业务场景下，推荐使用多层校验策略，提高容错能力。

graph TD
    A[输入分数] --> B{是否在0~100之间?}
    B -- 是 --> C[写入数据库]
    B -- 否 --> D[抛出异常/提示错误]
    C --> E[查询显示]
    D --> F[前端反馈]