Skip to main content

MySQL检查约束仿真

MySQL 8.0.16实现了SQL检查约束。如果在早期版本中使用MySQL,可以使用带有检查选项的视图或触发器来模拟检查约束。

MySQL检查约束简介

在MySQL 8.0.16之前,创建表允许包含表检查约束。但是,检查约束只是被解析并忽略:

CHECK(expression)

从MySQL 8.0.16开始,CREATE TABLE支持所有存储引擎的表和列检查约束的基本功能。

以下是语法:

[CONSTRAINT [constraint_name]] CHECK (expression) [[NOT] ENFORCED]

在这种语法中:

首先,指定要创建的检查约束的名称。如果省略约束名称,MySQL会自动生成一个具有以下约定的名称:

table_name_chk_n

其中n是序号1,2,3…例如,零件表的检查约束的名称将是parts_chk_1,parts_chk_2…

其次,为表的每一行指定一个布尔表达式,该表达式的计算结果必须为TRUE或UNKNOWN。如果表达式的计算结果为FALSE,则值违反约束或发生约束违反。

第三,可以选择指定强制子句,以指示是否强制执行检查约束:

如前所述,可以将检查约束指定为表约束或列约束。

表检查约束可以引用多个列,而列检查约束可以引用定义它的唯一列。

MySQL检查约束示例

让我们举一些使用检查约束的例子。

1) MySQL检查约束–列约束示例

此语句将创建一个新零件表:

CREATE TABLE parts (
part_no VARCHAR(18) PRIMARY KEY,
description VARCHAR(40),
cost DECIMAL(10,2 ) NOT NULL CHECK (cost >= 0),
price DECIMAL(10,2) NOT NULL CHECK (price >= 0)
);

在这个语句中,我们有两个列检查约束:一个用于成本列,另一个用于价格列。

因为我们没有明确指定检查约束的名称,MySQL会自动为它们生成名称。

要查看带有检查约束名称的表定义,请使用SHOW CREATE table语句:

SHOW CREATE TABLE parts;

以下是输出:

mysql check constraint

从输出中可以清楚地看到,MySQL生成了检查约束parts_chk_1和parts_chk_2。

一旦检查约束就位,无论何时插入或更新导致布尔表达式计算结果为false的值,MySQL都会拒绝更改并发出错误。

此语句在零件表中插入新行:

INSERT INTO parts(part_no, description,cost,price) 
VALUES('A-001','Cooler',0,-100);

MySQL发布了一个错误:

Error Code: 3819. Check constraint 'parts_chk_2' is violated.

因为price列的值为负值,这会导致表达式price>0的计算结果为FALSE,从而导致约束冲突。

2) MySQL检查约束–表约束示例

首先,删除零件表:

DROP TABLE IF EXISTS parts;

然后,使用另一个表检查约束创建一个新零件表:

CREATE TABLE parts (
part_no VARCHAR(18) PRIMARY KEY,
description VARCHAR(40),
cost DECIMAL(10,2 ) NOT NULL CHECK (cost >= 0),
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
CONSTRAINT parts_chk_price_gt_cost
CHECK(price >= cost)
);

以下新条款定义了一个表检查约束,以确保价格始终大于或等于成本:

CONSTRAINT parts_chk_price_gt_cost CHECK(price >= cost)

因为我们显式地指定了CHECK约束的名称,所以MySQL只使用指定的名称创建新约束。

以下是零件表的定义:

SHOW CREATE TABLE parts;

表检查约束出现在列列表后的表定义末尾。

本声明试图插入价格低于成本的新零件:

INSERT INTO parts(part_no, description,cost,price) 
VALUES('A-001','Cooler',200,100);

以下是由于违反约束而导致的错误:

Error Code: 3819. Check constraint 'parts_chk_price_gt_cost' is violated.

在本教程中,您了解了MySQL检查约束,以确保存储在列中的值满足布尔条件。