检查约束
在MySQL 8.0.16之前,CREATE TABLE
仅允许以下有限的表CHECK
约束语法版本被解析和忽略:
CHECK (expr)
从MySQL 8.0.16开始,CREATE TABLE
允许CHECK
对所有存储引擎使用表和列约束的核心功能。对于表约束和列约束,都CREATE TABLE
允许使用以下CHECK
约束语法:
[CONSTRAINT [symbol]]CHECK (expr) [[NOT]ENFORCED ]
可选symbol
为约束指定名称。如果省略,MySQL将根据表名称,文字_chk_
和序数(1、2、3,...)生成名称。约束名称的最大长度为64个字符。它们区分大小写,但不区分重音。
expr
将约束条件指定为布尔表达式,对于表的每一行,其值必须等于TRUE
或UNKNOWN
(对于NULL
值)。如果条件的计算结果为FALSE
,则失败,并且发生约束冲突。违反的影响取决于正在执行的语句,如本节后面所述。
可选的执行子句指示是否强制执行约束:
- 如果省略或指定为
ENFORCED
,则创建并强制执行约束。 - 如果指定为
NOT ENFORCED
,则创建约束但不强制约束。
将CHECK
约束指定为表约束或列约束:
- 表约束不会出现在列定义中,并且可以引用任何表列。允许对表定义后面出现的列进行前向引用。
- 列约束出现在列定义中,并且只能引用该列。
考虑此表定义:
CREATE TABLE t1 (CHECK (c1 <> c2), c1 INTCHECK (c1 > 10), c2 INTCONSTRAINT c2_positiveCHECK (c2 > 0), c3 INTCHECK (c3 < 100),CONSTRAINT c1_nonzeroCHECK (c1 <> 0),CHECK (c1 > c3) );
该定义包括表约束和列约束,格式为已命名和未命名:
- 第一个约束是表约束:它发生在任何列定义之外,因此它可以(并且确实)引用多个表列。此约束包含对尚未定义的列的前向引用。没有指定约束名称,因此MySQL会生成一个名称。
- 接下来的三个约束是列约束:每个约束都出现在列定义中,因此只能引用所定义的列。约束之一被明确命名。MySQL为其他两个生成一个名称。
- 最后两个约束是表约束。其中之一被明确命名。MySQL为另一个生成一个名称。
如前所述,MySQL为CHECK
指定的任何约束生成一个名称,而没有一个约束。要参见为前面的表定义生成的名称,请使用SHOW CREATE TABLE
:
mysql>SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table : t1 Create Table : CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, `c3` int(11) DEFAULT NULL, CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)), CONSTRAINT `c2_positive` CHECK ((`c2` > 0)), CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)), CONSTRAINT `t1_chk_2` CHECK ((`c1` > 10)), CONSTRAINT `t1_chk_3` CHECK ((`c3` < 100)), CONSTRAINT `t1_chk_4` CHECK ((`c1` > `c3`)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SQL标准指定所有类型的约束(主键,唯一索引,外键,检查)都属于同一名称空间。在MySQL中,每个约束类型在每个架构(数据库)中都有自己的名称空间。因此,CHECK
约束名称对于每个模式必须是唯一的;同一模式中的两个表都不能共享CHECK
约束名称。(例外:TEMPORARY
表隐藏具有TEMPORARY
相同名称的非表,因此它也可以具有相同的CHECK
约束名称。)
以表名开头生成的约束名称有助于确保架构唯一性,因为表名在架构中也必须是唯一的。
CHECK
条件表达式必须遵守以下规则。如果表达式包含不允许的构造,则会发生错误。
- 除了具有
AUTO_INCREMENT
属性的列和其他表中的列之外,允许非生成列和生成列。 - 允许使用文字,确定性内置函数和运算符。如果给定表中的相同数据,则独立于所连接的用户,如果多次调用产生相同的结果,则该函数为确定性函数。的是不确定性和失败,这个定义函数的例子:
CONNECTION_ID()
,CURRENT_USER()
,NOW()
。 - 不允许使用存储的函数和用户定义的函数。
- 不允许存储过程和函数参数。
- 不允许使用变量(系统变量,用户定义的变量和存储的程序局部变量)。
- 不允许子查询。
外键参照动作(ON UPDATE
,ON DELETE
)被禁止在使用的列CHECK
约束。同样,CHECK
禁止对外键引用操作中使用的列进行约束。
CHECK
约束评估INSERT
,UPDATE
,REPLACE
,LOAD DATA
,和LOAD XML
语句和发生,如果约束评估为一个错误FALSE
。如果发生错误,则对于事务性存储引擎和非事务性存储引擎,已应用的更改处理将有所不同,并且还取决于严格的SQL模式是否有效,如严格SQL模式中所述。
CHECK
约束评估INSERT IGNORE
,UPDATE IGNORE
,LOAD DATA ... IGNORE
,和LOAD XML ... IGNORE
报表,如果出现一个约束评估为警告FALSE
。跳过任何有问题的行的插入或更新。
如果约束表达式计算出的数据类型与声明的列类型不同,则根据通常的MySQL类型转换规则,对声明的类型进行隐式强制转换。请参见“表达式评估中的类型转换”。如果类型转换失败或导致精度损失,则会发生错误。
注意约束表达式评估使用评估时有效的SQL模式。如果表达式的任何部分取决于SQL模式,则表的不同用法可能会产生不同的结果,除非在所有用法中SQL模式都相同。