MySQL 检查约束 (CHECK)

 检查约束核心特性

1. 数据验证：确保列值满足指定条件

2. 表达式支持：可使用各种SQL表达式

3. 多列验证：支持基于表的约束验证多列关系

4. 版本支持：

    MySQL 8.0.16+ 原生支持并强制执行CHECK约束

    早期版本会解析但不强制执行

检查约束操作详解

创建表时定义检查约束

单列检查约束：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) CHECK (salary > 0),
    age INT CHECK (age >= 18 AND age <= 65)
);

多列组合检查约束：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10,2),
    discount_price DECIMAL(10,2),
    CHECK (discount_price < price)
);

命名约束（推荐）：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    delivery_date DATE,
    CONSTRAINT chk_delivery_date 
        CHECK (delivery_date >= order_date)
);

修改表添加检查约束

ALTER TABLE employees
ADD CONSTRAINT chk_employee_age
CHECK (age >= 18 AND age <= 65);

删除检查约束

ALTER TABLE employees
DROP CONSTRAINT chk_employee_age;

高级检查约束技术

复杂表达式

CREATE TABLE reservations (
    res_id INT PRIMARY KEY,
    start_date DATE,
    end_date DATE,
    room_type VARCHAR(20),
    CHECK (
        end_date > start_date AND
        (room_type IN ('Deluxe', 'Suite') OR 
         DATEDIFF(end_date, start_date) <= 30)
    )
);

正则表达式检查

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100),
    phone VARCHAR(20),
    CONSTRAINT chk_valid_email
        CHECK (email REGEXP '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$'),
    CONSTRAINT chk_valid_phone
        CHECK (phone REGEXP '^[0-9]{10,15}$')
);

条件检查约束

CREATE TABLE inventory (
    item_id INT PRIMARY KEY,
    quantity INT,
    status VARCHAR(20),
    CHECK (
        (status = 'In Stock' AND quantity > 0) OR
        (status = 'Out of Stock' AND quantity = 0) OR
        (status = 'Backorder')
    )
);

检查约束最佳实践

1. 命名规范：使用`chk_表名_列名`的命名约定

2. 业务规则：

    实现领域特定规则（如"折扣价必须低于原价"）

    验证数据格式（邮箱、电话等）

3. 性能考虑：

    复杂表达式可能影响写入性能

    避免在检查约束中使用子查询

4. 与应用层验证结合：

    检查约束作为最后的数据防线

    应用层应提前验证减少数据库压力

实际应用示例

银行账户系统：

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_type VARCHAR(20),
    balance DECIMAL(15,2),
    min_balance DECIMAL(15,2),
    CHECK (
        (account_type = 'Savings' AND balance >= min_balance) OR
        (account_type = 'Checking' AND balance >= -1000)
    )
);

学校管理系统：

CREATE TABLE student_grades (
    record_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    grade VARCHAR(2),
    semester VARCHAR(20),
    CHECK (
        grade IN ('A', 'B', 'C', 'D', 'F', 'I', 'W') AND
        semester REGEXP '^(Spring|Summer|Fall|Winter) 20[0-9]{2}$'
    ),
    UNIQUE (student_id, course_id, semester)
);

版本兼容性处理

对于MySQL 8.0.16之前的版本，可通过以下方式模拟检查约束：

1. 使用触发器：

CREATE TRIGGER check_salary BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary <= 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Salary must be positive';
    END IF;
END;

2. 使用ENUM类型（适用于有限值集合）：

CREATE TABLE tasks (
    task_id INT PRIMARY KEY,
    status ENUM('Pending', 'In Progress', 'Completed')
);