发布于 由admin

MySQL编程进阶之：如何利用`CHECK`约束（MySQL 8.0）来强制数据完整性。

各位朋友，乡亲们，老铁们，大家好！

我是你们的老朋友，今天咱们来聊聊MySQL 8.0中的CHECK约束，这玩意儿就像数据库的门卫大爷，专门负责检查进出的数据是否符合规矩，保证咱们数据的“三观”正，不跑偏。

为啥要用CHECK约束？

说白了，就是为了数据完整性。你想啊，咱们的数据库里存着各种重要信息，比如客户的年龄、产品的价格、订单的状态等等。如果这些数据乱七八糟，那咱们的业务还怎么玩？

举个栗子，如果客户年龄字段可以填负数，那以后过生日是倒着过吗？如果产品价格可以填0，那老板不得哭死？所以，我们需要一种机制来保证数据的合理性，CHECK约束就是干这个的。

CHECK约束是个啥？

简单来说，CHECK约束就是一个条件表达式，在插入或更新数据时，MySQL会检查数据是否满足这个条件。如果满足，数据就允许进入；如果不满足，MySQL就会毫不留情地拒绝你的操作，并抛出一个错误。

CHECK约束怎么用？

在MySQL 8.0之前，CHECK约束虽然能定义，但是压根儿不生效，MySQL会直接忽略它。这就像你跟孩子说“不许玩手机”，然后转身就看到他抱着手机玩得不亦乐乎一样，简直形同虚设。但是，在MySQL 8.0中，CHECK约束终于可以发挥它的威力了！

咱们来看看怎么用：

  • 创建表时添加CHECK约束：
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    discount DECIMAL(5,2),
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_discount CHECK (discount >= 0 and discount <= 0.9)
);

在这个例子中，我们创建了一个名为products的表，其中：

  • chk_price约束确保price字段的值必须大于0，不能是负数或者0。

  • chk_discount约束确保discount字段的值必须在0到0.9之间（包括0和0.9）。
    这样可以保证折扣不会是负数，也不会超过100%

  • 修改表时添加CHECK约束：

ALTER TABLE products
ADD CONSTRAINT chk_product_name CHECK (LENGTH(product_name) > 3);

这个例子中，我们通过ALTER TABLE语句给products表添加了一个名为chk_product_name的约束，确保product_name字段的长度必须大于3个字符。

  • 命名CHECK约束：

在上面的例子中，我们都给CHECK约束起了名字（比如chk_price、chk_discount）。这样做的好处是，当约束失败时，MySQL会返回约束的名字，方便我们定位问题。如果不给约束起名字，MySQL会自己生成一个名字，但是可读性比较差。

  • 删除CHECK约束：
ALTER TABLE products
DROP CONSTRAINT chk_product_name;

使用DROP CONSTRAINT语句可以删除一个CHECK约束。

CHECK约束的进阶用法

CHECK约束的功能非常强大，它可以实现各种复杂的数据校验逻辑。

  • 使用CASE表达式：
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    order_status VARCHAR(20) NOT NULL,
    ship_date DATE,
    CONSTRAINT chk_ship_date CHECK (
        CASE
            WHEN order_status = 'Shipped' THEN ship_date IS NOT NULL
            ELSE ship_date IS NULL
        END
    )
);

在这个例子中，我们创建了一个名为orders的表，其中chk_ship_date约束使用CASE表达式来判断ship_date字段是否应该为空。如果order_status是’Shipped’，那么ship_date就必须有值；否则，ship_date必须为空。

  • 使用用户自定义函数（UDF）：

如果CHECK约束的逻辑非常复杂，可以使用用户自定义函数（UDF）来实现。

-- 创建一个UDF，用于校验邮箱地址是否合法
DELIMITER //
CREATE FUNCTION is_valid_email(email VARCHAR(255))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
  -- 这里可以使用正则表达式或其他方法来校验邮箱地址
  RETURN email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';
END //
DELIMITER ;

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    CONSTRAINT chk_email CHECK (is_valid_email(email))
);

在这个例子中，我们首先创建了一个名为is_valid_email的UDF，用于校验邮箱地址是否合法。然后，我们在users表中使用了这个UDF作为CHECK约束的条件。

CHECK约束的注意事项

  • CHECK约束只能引用当前表中的列，不能引用其他表中的列。也就是说，不能做跨表约束。
  • CHECK约束的条件表达式必须是确定性的，也就是说，对于相同的数据，条件表达式的结果必须始终相同。不能使用RAND()、NOW()等不确定性的函数。
  • CHECK约束可能会影响性能，特别是当条件表达式非常复杂时。所以，要谨慎使用CHECK约束，避免过度校验。

CHECK约束的错误处理

当CHECK约束失败时，MySQL会抛出一个错误。我们需要捕获这个错误，并进行相应的处理。

例如，在使用Python的pymysql库操作MySQL时，可以这样处理：

import pymysql

try:
    # 连接数据库
    connection = pymysql.connect(host='localhost',
                                 user='root',
                                 password='your_password',
                                 database='your_database')

    # 创建一个游标对象
    cursor = connection.cursor()

    # 插入一条数据，违反了CHECK约束
    sql = "INSERT INTO products (product_id, product_name, price) VALUES (1, 'Test Product', -10);"
    cursor.execute(sql)

    # 提交事务
    connection.commit()

except pymysql.err.IntegrityError as e:
    print("Error inserting data: {}".format(e))
    # 回滚事务
    connection.rollback()

finally:
    # 关闭连接
    if connection:
        connection.close()

在这个例子中，我们尝试插入一条price为-10的数据，这违反了chk_price约束。pymysql会抛出一个IntegrityError异常，我们捕获这个异常，并打印错误信息，然后回滚事务，防止脏数据进入数据库。

CHECK约束与其他数据完整性约束的比较

MySQL中还有其他几种数据完整性约束，比如PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL。它们与CHECK约束有什么区别呢？

约束类型 功能 限制
PRIMARY KEY 唯一标识一条记录 一个表只能有一个主键，主键列的值必须唯一且不能为空。
FOREIGN KEY 建立表与表之间的关系 外键列的值必须在关联表的主键列或唯一键列中存在。
UNIQUE 保证列的值唯一 允许为NULL，但同一列中只能有一个NULL值。
NOT NULL 保证列的值不能为空
CHECK 保证列的值满足指定的条件表达式，提供更细粒度的约束 只能引用当前表中的列，不能引用其他表中的列，条件表达式必须是确定性的，可能会影响性能。

简单来说，PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL是比较通用的约束，而CHECK约束则更加灵活，可以实现各种自定义的数据校验逻辑。

CHECK约束的最佳实践

  • 尽早定义CHECK约束： 最好在创建表的时候就定义好CHECK约束，而不是在后期修改表结构。
  • 命名CHECK约束： 给CHECK约束起一个有意义的名字，方便定位问题。
  • 编写清晰的条件表达式： 尽量使用简单易懂的条件表达式，避免过度复杂的逻辑。
  • 测试CHECK约束： 在生产环境中使用CHECK约束之前，一定要进行充分的测试，确保它能够正常工作。
  • 监控CHECK约束的性能： 定期监控CHECK约束的性能，如果发现性能问题，可以考虑优化条件表达式或者调整数据库配置。

总结

CHECK约束是MySQL 8.0中一个非常强大的功能，它可以帮助我们保证数据的完整性，提高数据库的可靠性。虽然使用CHECK约束需要注意一些问题，但是只要我们掌握了它的用法，并遵循最佳实践，就可以充分利用它的优势，构建更加健壮的数据库应用。

好了，今天的讲座就到这里。希望大家能够掌握CHECK约束的用法，并在实际项目中灵活应用。记住，数据完整性是数据库的生命线，我们要像爱护自己的眼睛一样爱护它！

如果大家还有什么问题，欢迎随时提问。祝大家工作顺利，生活愉快！再见！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注