MySQL高级特性之：`MySQL`的`CHECK`约束：其在`MySQL 8.0.16`后的行为和应用。

好的，我们开始。

MySQL CHECK 约束：8.0.16 后的行为和应用

大家好，今天我们来深入探讨 MySQL 中的 CHECK 约束，特别是它在 MySQL 8.0.16 版本之后的变化和应用。在早期版本中，CHECK 约束虽然可以被定义，但实际上会被 MySQL 解析器忽略，不会真正强制执行约束。而从 8.0.16 开始，CHECK 约束终于真正发挥了作用，为数据完整性提供了更强的保障。

历史回顾：8.0.16 之前的 CHECK 约束

在 MySQL 8.0.16 之前，你可以定义 CHECK 约束，语法上没有问题，但是 MySQL 服务器会忽略这些约束。这意味着即使违反了 CHECK 约束，数据仍然可以被插入或更新到表中，而不会引发任何错误或警告。

例如：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18)
);

-- 在 MySQL 8.0.16 之前，即使插入 age < 18 的数据，也不会报错
INSERT INTO employees (id, age) VALUES (1, 15);
SELECT * FROM employees; -- 可以看到 age 为 15 的数据被成功插入

这使得 CHECK 约束在早期 MySQL 版本中几乎没有实际用处，开发者需要依赖应用层逻辑或者触发器来确保数据满足特定的约束条件。

8.0.16 之后的 CHECK 约束：真正的数据验证

从 MySQL 8.0.16 开始，CHECK 约束会被 MySQL 服务器真正地强制执行。这意味着如果插入或更新的数据违反了定义的 CHECK 约束，MySQL 会返回一个错误，阻止数据被写入表中，从而保证了数据的完整性。

让我们回到之前的例子，在 MySQL 8.0.16 及以后的版本中，执行相同的操作：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18)
);

-- 在 MySQL 8.0.16 及以后，插入 age < 18 的数据会报错
INSERT INTO employees (id, age) VALUES (1, 15);
-- 错误信息: ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.

现在，当试图插入 age 小于 18 的数据时，MySQL 会抛出一个错误，指示 CHECK 约束被违反。这使得 CHECK 约束成为一个非常有用的工具，可以帮助开发者在数据库层面强制执行数据验证规则。

CHECK 约束的语法

CHECK 约束可以在创建表时定义，也可以在之后使用 ALTER TABLE 语句添加。

• 在 CREATE TABLE 语句中定义：

  CREATE TABLE table_name (
      column1 datatype constraints,
      column2 datatype constraints,
      ...,
      CONSTRAINT constraint_name CHECK (condition)
  );

• 使用 ALTER TABLE 语句添加：

  ALTER TABLE table_name
  ADD CONSTRAINT constraint_name CHECK (condition);

• 使用 ALTER TABLE 语句删除：

  ALTER TABLE table_name
  DROP CONSTRAINT constraint_name;

CHECK 约束的类型

CHECK 约束可以基于各种条件来验证数据，包括：

• 范围检查： 检查值是否在指定的范围内。

  CREATE TABLE products (
      product_id INT PRIMARY KEY,
      price DECIMAL(10, 2) CHECK (price >= 0 AND price <= 1000)
  );

• 列表检查： 检查值是否在指定的列表中。

  CREATE TABLE orders (
      order_id INT PRIMARY KEY,
      status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
  );

• 模式匹配： 检查值是否符合指定的模式。

  CREATE TABLE users (
      user_id INT PRIMARY KEY,
      email VARCHAR(255) CHECK (email LIKE '%@%.%')
  );

• 与其他列的比较： 检查值是否满足与其他列的比较关系。

  CREATE TABLE events (
      start_time DATETIME,
      end_time DATETIME,
      CHECK (end_time >= start_time)
  );

• 自定义函数： 可以调用自定义函数来执行更复杂的验证逻辑。

  -- 创建一个自定义函数，检查电话号码格式
  DELIMITER //
  CREATE FUNCTION validate_phone_number(phone_number VARCHAR(20))
  RETURNS BOOLEAN
  DETERMINISTIC
  BEGIN
      -- 这里可以使用正则表达式或者其他逻辑来验证电话号码
      RETURN phone_number REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';
  END //
  DELIMITER ;
  
  CREATE TABLE customers (
      customer_id INT PRIMARY KEY,
      phone_number VARCHAR(20),
      CHECK (validate_phone_number(phone_number))
  );

CHECK 约束的应用场景

CHECK 约束在很多场景下都非常有用，例如：

• 数据验证： 确保输入的数据符合特定的规则，例如年龄必须大于等于 18，价格必须为正数，电子邮件地址必须符合特定的格式。
• 业务规则： 强制执行业务规则，例如订单金额必须大于等于最低消费金额，库存数量不能为负数。
• 数据一致性： 维护数据的一致性，例如结束时间必须大于等于开始时间，员工的职称必须与其工资级别相符。
• 减少应用层代码： 将数据验证逻辑从应用层转移到数据库层，可以减少应用层代码的复杂性，提高代码的可维护性。

CHECK 约束的局限性

虽然 CHECK 约束非常有用，但也存在一些局限性：

• 不支持子查询： CHECK 约束不能包含子查询，这意味着不能直接引用其他表的数据来进行验证。例如，不能使用 CHECK 约束来验证一个产品的价格是否小于同类型产品的平均价格。
• 不支持用户自定义变量： CHECK 约束不能使用用户自定义变量。
• 性能影响： 过多的 CHECK 约束可能会对数据库的性能产生一定的影响，因为每次插入或更新数据时，都需要对这些约束进行评估。因此，需要谨慎地使用 CHECK 约束，避免过度约束。

示例：一个复杂的 CHECK 约束场景

假设我们有一个 orders 表和一个 products 表，我们需要确保订单中的产品数量不能超过产品的库存数量，并且订单的总金额不能小于 10 元。

-- 创建 products 表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL
);

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    order_date DATETIME NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    CHECK (total_amount >= 10) -- 订单总金额不能小于 10 元
);

-- 创建触发器来检查库存数量
DELIMITER //
CREATE TRIGGER check_stock_quantity
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE current_stock INT;
    SELECT stock_quantity INTO current_stock FROM products WHERE product_id = NEW.product_id;
    IF NEW.quantity > current_stock THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单数量超过库存数量';
    END IF;
END //
DELIMITER ;

-- 插入一些测试数据
INSERT INTO products (product_id, product_name, price, stock_quantity) VALUES
(1, 'Product A', 20.00, 100),
(2, 'Product B', 30.00, 50);

-- 成功的插入
INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date, total_amount) VALUES
(1, 1, 1, 5, NOW(), 100.00);

-- 违反库存数量的插入 (触发器会阻止插入)
-- ERROR 1644 (45000): 订单数量超过库存数量
-- INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date, total_amount) VALUES
-- (2, 1, 1, 150, NOW(), 3000.00);

-- 违反订单总金额的插入 (CHECK 约束会阻止插入)
-- ERROR 3819 (HY000): Check constraint 'orders_chk_1' is violated.
-- INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date, total_amount) VALUES
-- (3, 1, 1, 1, NOW(), 5.00);

在这个例子中，我们使用 CHECK 约束来确保订单总金额不小于 10 元。 由于 CHECK 约束不支持子查询，我们使用触发器来检查订单数量是否超过库存数量。触发器在插入之前执行，如果订单数量超过库存数量，则会抛出一个错误，阻止插入操作。

INFORMATION_SCHEMA 中的 CHECK 约束信息

MySQL 提供了一个 INFORMATION_SCHEMA 数据库，其中包含了关于数据库元数据的信息，包括 CHECK 约束的信息。可以使用以下查询来查看 CHECK 约束的信息：

SELECT
    CONSTRAINT_SCHEMA,
    CONSTRAINT_NAME,
    TABLE_NAME,
    CHECK_CLAUSE
FROM
    INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE
    CONSTRAINT_SCHEMA = 'your_database_name'; -- 将 your_database_name 替换为你的数据库名称

这个查询会返回数据库中所有 CHECK 约束的信息，包括约束所在的数据库、约束的名称、约束所在的表以及约束的表达式。

CHECK 约束与触发器的比较

CHECK 约束和触发器都可以用来强制执行数据验证规则，但它们之间有一些重要的区别：

特性	CHECK 约束	触发器
复杂性	相对简单，只能基于单个表中的列进行验证	可以执行更复杂的逻辑，可以访问其他表的数据
支持子查询	不支持	可以使用子查询
性能	通常比触发器更快	可能会对性能产生较大的影响
可读性	通常比触发器更易于阅读和理解	代码可能比较复杂，难以维护
适用场景	简单的验证规则	复杂的验证规则，需要访问其他表的数据

一般来说，如果验证规则比较简单，并且只需要基于单个表中的列进行验证，那么 CHECK 约束是更好的选择。如果验证规则比较复杂，或者需要访问其他表的数据，那么触发器可能更合适。

CHECK 约束在实际项目中的应用

在实际项目中，CHECK 约束可以被广泛应用于各种场景，例如：

• 电商平台： 可以使用 CHECK 约束来验证商品价格是否为正数，订单金额是否大于等于最低消费金额，以及优惠券的有效期是否有效。
• 金融系统： 可以使用 CHECK 约束来验证账户余额是否为负数，交易金额是否超过账户余额，以及贷款利率是否在允许的范围内。
• 医疗系统： 可以使用 CHECK 约束来验证患者的年龄是否大于等于 0，血压值是否在正常范围内，以及药物剂量是否超过最大剂量。
• 社交网络： 可以使用 CHECK 约束来验证用户名的长度是否在允许的范围内，密码是否符合安全要求，以及电子邮件地址是否有效。

总结一下，CHECK 约束的特点与重要性

MySQL 8.0.16 之后，CHECK 约束变得真正有用，可以有效保证数据完整性。 理解 CHECK 约束的语法、类型、应用场景和局限性对于编写高质量的数据库应用程序至关重要。 在实际项目中，需要根据具体的业务需求选择合适的验证方法，合理使用 CHECK 约束和触发器，以确保数据的完整性和一致性。