MySQL 限制某列只接受两个值

阅读更多：MySQL 教程

背景介绍

在MySQL中，我们经常会遇到需要限制某列只能输入特定值的需求。这时候，我们可以使用约束(constraint)来实现。约束是一个数据库对象，主要用来保证数据库中数据的完整性和正确性。

常见的约束有：主键约束、唯一约束、非空约束、检查约束等。而限制某列只接受两个值，可以使用check约束实现。

使用check约束限制列只接受两个值

check约束用来限制列的取值范围，只有满足条件的数据才能插入或更新到表中。这个条件可以是一个简单的表达式，也可以是一个由多个逻辑操作符组成的复杂表达式。

在MySQL中，使用check约束可以限制某列只接受两个值的方法如下：

CREATE TABLE table_name (
    column_name datatype 
        CHECK (column_name IN ('value1', 'value2'))
);

其中，’table_name’ 表示表名，’column_name’ 表示要限制的列名，’datatype’ 表示该列的数据类型，’value1′ 和 ‘value2’ 则是我们要限制的两个值。

例如，创建一个students表，其中gender列只能输入’male’或’female’两个值，可以这样写：

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(10) 
        CHECK (gender IN ('male', 'female'))
);

这样一来，当我们试图往gender列插入除’male’和’female’之外的值时，就会报错。

针对特定情况的约束设置

除了限制只接受两个值以外，有时我们还需要对特定情况下的约束做出更为详细的设置。以下是一些常见的情况及其解决方法：

大小写不敏感

如果我们希望限制列只能输入两个值，但不考虑大小写，可以使用LOWER()函数将输入的值转换为小写再进行比较，比如：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    employment_status VARCHAR(20)
        CHECK (LOWER(employment_status) IN ('full-time', 'part-time'))
);

这样，无论用户输入的是’full-time’还是’FULL-TIME’，都会被认为是符合要求的值。

范围限制

有时我们需要限制列只能输入一定范围内的值，而不是具体的值。在这种情况下，我们可以使用BETWEEN操作符。

CREATE TABLE salary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    amount DECIMAL(12,2)
        CHECK (amount BETWEEN 1000 AND 5000)
);

这里，我们限制amount列只能输入1000到5000之间的数值。

参考另一列的值

有时候我们需要限制某列的取值范围与另一列的值有关联。这时候，我们可以使用子查询(subquery)来完成此操作。

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    total DECIMAL(12,2) 
        CHECK (total >= amount AND total <= (
            SELECT SUM(amount) 
            FROM orders 
            WHERE date = DATE('2021-08-15')
        ))
);

在这个例子中，我们限制total列的取值范围不仅与amount列有关，还与当前的日期有关。这里我们使用了子查询获取当日总销售额，并将其作为上限来限制total列的取值范围。总的来说，通过这种方式我们可以实现更加复杂的约束设置。