【重学 MySQL】六十七、解锁检查约束，守护数据完整性

在MySQL中，检查约束（CHECK）是一种用于确保表中数据满足特定条件的约束。

检查约束的基本概念

检查约束用于限制表中列的值，以确保它们满足特定的条件。这些条件通常通过SQL表达式来定义，当插入或更新数据时，MySQL会验证这些表达式是否为真。如果表达式为假，则操作会被拒绝，从而保证了数据的完整性和有效性。

检查约束的语法

1. 在创建表时添加检查约束

   • 语法格式：

     CREATE TABLE 表名 (
     	列名 数据类型 CHECK (条件表达式),
     	...
     );
     

   • 示例：

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

   在这个示例中，age列被添加了一个检查约束，确保age的值大于或等于18。

2. 在修改表时添加检查约束

   • 语法格式：

     ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK (条件表达式);
     

   • 示例：

     ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age < 100);
     

   在这个示例中，向users表添加了一个名为chk_age的检查约束，确保age的值小于100。

3. 删除检查约束

   • 语法格式：

     ALTER TABLE 表名 DROP CONSTRAINT 约束名;
     

   • 示例：

     ALTER TABLE users DROP CONSTRAINT chk_age;
     

   在这个示例中，从users表中删除了名为chk_age的检查约束。

检查约束的使用场景

检查约束通常用于以下场景：

1. 限制列的值范围：例如，确保年龄字段的值在合理范围内（如18到100之间）。
2. 确保数据的有效性：例如，确保电子邮件字段的值符合电子邮件的格式。
3. 维护数据的一致性：例如，确保两个相关字段之间的值满足特定关系（如一个字段的值必须大于另一个字段的值）。

注意事项

1. MySQL版本：检查约束在MySQL 8.0.16及更高版本中才得到全面支持。在之前的版本中，虽然可以添加检查约束，但MySQL不会对其进行验证。
2. 复杂表达式：检查约束可以使用简单的表达式，也可以包含复杂的逻辑和子查询。但是，请注意性能问题，因为复杂的表达式可能会增加数据插入和更新的开销。
3. 命名规范：为检查约束指定一个有意义的名称是一个好习惯，这有助于在需要时更容易地识别和管理约束。

示例

以下是一个综合示例，展示了如何在创建和修改表时使用检查约束：

-- 创建表时添加检查约束
CREATE TABLE employees (
	id INT PRIMARY KEY,
	name VARCHAR(100),
	salary DECIMAL(10, 2) CHECK (salary > 0),
	hire_date DATE CHECK (hire_date >= '2000-01-01')
);

-- 修改表时添加检查约束
ALTER TABLE employees ADD CONSTRAINT chk_salary_max CHECK (salary <= 100000);

-- 尝试插入数据（合法）
INSERT INTO employees (id, name, salary, hire_date) VALUES (1, 'Alice', 50000, '2020-05-15');

-- 尝试插入数据（不合法，salary小于0）
INSERT INTO employees (id, name, salary, hire_date) VALUES (2, 'Bob', -1000, '2021-06-20'); -- 会失败

-- 尝试插入数据（不合法，hire_date小于2000-01-01）
INSERT INTO employees (id, name, salary, hire_date) VALUES (3, 'Charlie', 60000, '1999-12-31'); -- 会失败

在这个示例中，employees表在创建时添加了两个检查约束：一个确保salary大于0，另一个确保hire_date大于或等于2000-01-01。然后，通过ALTER TABLE语句添加了一个额外的检查约束，确保salary小于或等于100000。最后，尝试插入一些数据来演示检查约束的效果。