MySQL高阶讲座之：`MySQL`的`Check Constraints`：其在数据完整性中的作用与实现原理。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷们，掌声在哪里？今天咱们聊点硬核的，关于MySQL里一个可能被你忽略，但关键时刻能救你一命的东西：Check Constraints！这玩意儿就像数据库里的门卫，负责检查你往数据库里塞的数据是不是符合规矩，不合规矩的一律轰出去！

一、啥是Check Constraints？为啥需要它？

想象一下，你设计了一个用户表，其中有个字段叫age（年龄）。按照常理，年龄应该是正数，而且不太可能超过150岁吧？（除非你存储的是吸血鬼或者千年老妖的数据）。如果没有Check Constraints，你就可以随便往age字段里塞数据，比如-10，或者999。这会导致什么后果？数据混乱，程序出错，老板拍桌子骂娘！

Check Constraints就是用来解决这个问题的。它可以让你定义一些规则，只有符合这些规则的数据才能被插入或更新到数据库中。

简单来说，Check Constraints就是数据库的“数据校验员”，确保数据的准确性和一致性。

二、 MySQL对Check Constraints的态度：爱你在心口难开

这里有个小尴尬要说清楚。 MySQL从5.1.17版本开始支持Check Constraints的语法，但是！重点来了！ 它仅仅是解析了Check Constraints的语法，但实际上并没有真正强制执行这些约束！ 也就是说，你写了Check Constraints，MySQL会“哦，知道了”，但实际上并不会管你输入的数据是否符合约束。这也太坑爹了吧！

为啥会这样？历史原因，涉及到MySQL内部架构和一些设计上的考虑，这里就不展开说了。但别灰心，虽然MySQL内核不给力，但我们可以通过其他方式来实现类似Check Constraints的功能。

三、如何在MySQL中实现Check Constraints？（曲线救国方案）

既然MySQL本身不强制执行Check Constraints，我们就需要借助其他手段来实现类似的功能。主要有以下几种方式：

触发器（Triggers）：最靠谱的替代方案

触发器是MySQL中一种特殊的存储过程，它会在特定的事件发生时自动执行。我们可以利用触发器在数据插入或更新之前，检查数据是否符合约束，如果不符合，就阻止操作。

举个栗子：

假设我们有个products表，其中price字段必须大于0。
```
-- 创建表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- 创建触发器，在插入数据之前检查price是否大于0
DELIMITER //
CREATE TRIGGER check_price_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Price must be greater than 0';
    END IF;
END//
DELIMITER ;

-- 创建触发器，在更新数据之前检查price是否大于0
DELIMITER //
CREATE TRIGGER check_price_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Price must be greater than 0';
    END IF;
END//
DELIMITER ;

-- 插入数据，price小于等于0，会报错
INSERT INTO products (name, price) VALUES ('Invalid Product', -10);

-- 插入数据，price大于0，成功
INSERT INTO products (name, price) VALUES ('Valid Product', 10.50);

-- 更新数据，price小于等于0，会报错
UPDATE products SET price = -5 WHERE id = 1;

-- 更新数据，price大于0，成功
UPDATE products SET price = 12.75 WHERE id = 1;
```
代码解释：
- DELIMITER //：修改分隔符，因为触发器内部需要使用分号，所以需要先修改分隔符，避免MySQL提前结束语句。
- CREATE TRIGGER check_price_insert BEFORE INSERT ON products FOR EACH ROW：创建一个名为check_price_insert的触发器，在products表插入数据之前触发，FOR EACH ROW表示每一行数据都会触发。
- IF NEW.price <= 0 THEN：判断新插入的行的price字段是否小于等于0。 NEW是一个特殊变量，代表新插入的行。
- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price must be greater than 0';：如果price小于等于0，就抛出一个SQLSTATE为45000的错误，并设置错误信息。 SQLSTATE '45000'是一个通用的错误代码，表示违反了约束。
- END IF;：结束IF语句。
- END//：结束触发器定义。
- DELIMITER ;：恢复分隔符。
优点： 功能强大，可以实现各种复杂的约束逻辑。

缺点： 会增加数据库的负担，尤其是当数据量很大时。调试起来可能比较麻烦。

应用层校验：最后的防线

将数据校验的逻辑放在应用程序中。在将数据提交到数据库之前，先在应用程序中进行校验，如果不符合约束，就阻止提交。

举个栗子（Java代码）：

public class Product {
    private String name;
    private double price;

    public Product(String name, double price) {
        this.name = name;
        this.price = price;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        if (price <= 0) {
            throw new IllegalArgumentException("Price must be greater than 0");
        }
        this.price = price;
    }

    // 其他方法...
}

public class ProductService {
    public void saveProduct(Product product) {
        // 在保存到数据库之前，先校验price
        if (product.getPrice() <= 0) {
            throw new IllegalArgumentException("Price must be greater than 0");
        }

        // 保存到数据库...
    }
}

代码解释：

在Product类的setPrice方法中，判断price是否小于等于0，如果是，就抛出一个IllegalArgumentException异常。
在ProductService类的saveProduct方法中，在保存到数据库之前，再次校验price。

优点： 可以减轻数据库的负担。方便修改和维护。

缺点： 需要在每个应用程序中都实现校验逻辑，容易出现遗漏。如果有多个应用程序访问同一个数据库，需要保证每个应用程序的校验逻辑一致。

存储过程（Stored Procedures）：封装校验逻辑

将数据校验的逻辑封装在存储过程中。应用程序调用存储过程来插入或更新数据，存储过程负责校验数据，如果不符合约束，就阻止操作。

举个栗子：
```
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE insert_product(
    IN p_name VARCHAR(255),
    IN p_price DECIMAL(10, 2)
)
BEGIN
    IF p_price <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Price must be greater than 0';
    ELSE
        INSERT INTO products (name, price) VALUES (p_name, p_price);
    END IF;
END//
DELIMITER ;

-- 调用存储过程
CALL insert_product('New Product', 15.75);

-- 调用存储过程，price小于等于0，会报错
CALL insert_product('Invalid Product', -8);
```
代码解释：
- CREATE PROCEDURE insert_product(...)：创建一个名为insert_product的存储过程，接受p_name和p_price两个参数。
- IF p_price <= 0 THEN：判断p_price是否小于等于0。
- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price must be greater than 0';：如果p_price小于等于0，就抛出一个SQLSTATE为45000的错误，并设置错误信息。
- ELSE INSERT INTO products (name, price) VALUES (p_name, p_price);：如果p_price大于0，就将数据插入到products表中。
- END IF;：结束IF语句。
- END//：结束存储过程定义。
优点： 将校验逻辑集中管理，方便维护。可以提高性能，因为存储过程在数据库服务器上执行。

缺点： 调试起来可能比较麻烦。需要熟悉存储过程的语法。
Generated Columns (MySQL 5.7.6+): 有点像虚拟字段

MySQL 5.7.6引入了Generated Columns，它可以根据其他列的值自动计算生成。我们可以利用Generated Columns来实现一些简单的约束。

举个栗子：
```
-- 创建表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    total DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * price) VIRTUAL,
    CHECK (quantity > 0) -- 这是一个真正的CHECK constraint，但作用有限
);

-- 插入数据
INSERT INTO orders (quantity, price) VALUES (2, 10.50);

-- 查看数据
SELECT * FROM orders;
```
代码解释：
- total DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * price) VIRTUAL：创建一个名为total的Generated Column，它的值等于quantity乘以price。 VIRTUAL表示这个列的值不会实际存储在磁盘上，而是在查询时动态计算。
- CHECK (quantity > 0)：这是一个真正的CHECK constraint，但是MySQL并不会强制执行它。但是，我们可以利用Generated Columns来实现一些简单的约束，比如确保quantity大于0。虽然MySQL不会阻止你插入quantity小于等于0的数据，但是Generated Columns可以帮助你发现这些问题。
优点： 可以实现一些简单的约束。

缺点： 只能实现一些简单的计算逻辑。 MySQL并不会强制执行CHECK constraints。

四、选择哪种方案？根据你的情况来！

方案	优点	缺点	适用场景
触发器	功能强大，可以实现各种复杂的约束逻辑。	会增加数据库的负担，尤其是当数据量很大时。调试起来可能比较麻烦。	对数据完整性要求非常高，需要实现各种复杂的约束逻辑，并且能够接受一定的性能损失。
应用层校验	可以减轻数据库的负担。方便修改和维护。	需要在每个应用程序中都实现校验逻辑，容易出现遗漏。如果有多个应用程序访问同一个数据库，需要保证每个应用程序的校验逻辑一致。	对数据完整性要求不是特别高，并且有多个应用程序访问同一个数据库，需要保证每个应用程序的校验逻辑一致。
存储过程	将校验逻辑集中管理，方便维护。可以提高性能，因为存储过程在数据库服务器上执行。	调试起来可能比较麻烦。需要熟悉存储过程的语法。	对数据完整性要求比较高，需要将校验逻辑集中管理，并且能够接受一定的学习成本。
Generated Columns	可以实现一些简单的约束。	只能实现一些简单的计算逻辑。 MySQL并不会强制执行CHECK constraints。	需要实现一些简单的约束，并且不需要强制执行CHECK constraints。

五、一点小建议

不要完全依赖MySQL的CHECK constraints。 虽然它存在，但实际上并没有用。
优先考虑使用触发器或应用层校验。 这两种方案是比较靠谱的替代方案。
根据实际情况选择合适的方案。 没有最好的方案，只有最适合你的方案。
做好数据校验是保证数据完整性的关键。 不要偷懒，认真对待每一个数据校验的环节。

六、总结

虽然MySQL对Check Constraints的支持有点让人失望，但是我们仍然可以通过其他方式来实现类似的功能。触发器、应用层校验、存储过程和Generated Columns都是不错的选择。选择哪种方案取决于你的具体需求和实际情况。记住，数据校验是保证数据完整性的关键，不要偷懒，认真对待每一个数据校验的环节。

好了，今天的讲座就到这里，希望大家有所收获！散会！记得点赞！

发表回复 取消回复

发表回复取消回复