MySQL 8.0 新特性之检查约束的实现

2023-07-07 14:24:04 168

大家好，我是只谈技术不剪发的Tony老师。这次我们来介绍一个MySQL8.0增加的新功能：检查约束（CHECK）。

SQL中的检查约束属于完整性约束的一种，可以用于约束表中的某个字段或者一些字段必须满足某个条件。例如用户名必须大写、余额不能小于零等。

我们常见的数据库都实现了检查约束，例如Oracle、SQLServer、PostgreSQL以及SQLite；然而MySQL一直以来没有真正实现该功能，直到最新的MySQL8.0.16。

MySQL8.0.15之前

在MySQL8.0.15以及之前的版本中，虽然CREATETABLE语句允许CHECK(expr)形式的检查约束语法，但实际上解析之后会忽略该子句。例如

mysql>selectversion();
+-----------+
|version()|
+-----------+
|8.0.15|
+-----------+
1rowinset(0.00sec)

mysql>CREATETABLEt1
->(
->c1INTCHECK(c1>10),
->c2INT,
->c3INTCHECK(c3<100),
->CONSTRAINTc2_positiveCHECK(c2>0),
->CHECK(c1>c3)
->);
QueryOK,0rowsaffected(0.33sec)

mysql>showcreatetablet1\G
***************************1.row***************************
Table:t1
CreateTable:CREATETABLE`t1`(
`c1`int(11)DEFAULTNULL,
`c2`int(11)DEFAULTNULL,
`c3`int(11)DEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci
1rowinset(0.00sec)

虽然我们在定义时指定了各种CHECK选项，但最终的表结构中不包含任何检查约束。这也意味着我们可以插入非法的数据：

mysql>insertintot1(c1,c2,c3)values(1,-1,100);
QueryOK,1rowaffected(0.06sec)

如果我们想要在MySQL8.0.15之前实现类似的检查约束，可以使用触发器；或者创建一个包含WITHCHECKOPTION选项的视图，然后通过视图插入或修改数据。

MySQL8.0.16之后

MySQL8.0.16于2019年4月25日发布，终于带来了我们期待已久的CHECK约束功能，而且对于所有的存储引擎都有效。CREATETABLE语句允许以下形式的CHECK约束语法，可以指定列级约束和表级约束：

[CONSTRAINT[symbol]]CHECK(expr)[[NOT]ENFORCED]

其中，可选的symbol参数用于给约束指定一个名称。如果省略该选项，MySQL将会产生一个以表名开头、加上_chk_以及一个数字编号（1、2、3…）组成的名字（table_name_chk_n）。约束名称最大长度为64个字符，而且区分大小写。

expr是一个布尔表达式，用于指定约束的条件；表中的每行数据都必须满足expr的结果为TRUE或者UNKNOWN（NULL）。如果表达式的结果为FALSE，将会违反约束。

可选的ENFORCED子句用于指定是否强制该约束：

如果忽略或者指定了ENFORCED，创建并强制该约束；
如果指定了NOTENFORCED，创建但是不强制该约束。这也意味着约束不会生效。

CHECK约束可以在列级指定，也可以在表级指定。

列级检查约束

列级约束只能出现在字段定义之后，而且只能针对该字段进行约束。例如：

mysql>selectversion();
+-----------+
|version()|
+-----------+
|8.0.16|
+-----------+
1rowinset(0.00sec)

mysql>CREATETABLEt1
->(
->c1INTCHECK(c1>10),
->c2INTCONSTRAINTc2_positiveCHECK(c2>0),
->c3INTCHECK(c3<100)
->);
QueryOK,0rowsaffected(0.04sec)

mysql>showcreatetablet1\G
***************************1.row***************************
Table:t1
CreateTable:CREATETABLE`t1`(
`c1`intDEFAULTNULL,
`c2`intDEFAULTNULL,
`c3`intDEFAULTNULL,
CONSTRAINT`c2_positive`CHECK((`c2`>0)),
CONSTRAINT`t1_chk_1`CHECK((`c1`>10)),
CONSTRAINT`t1_chk_2`CHECK((`c3`<100))
)ENGINE=InnoDBDEFAULTCHARSET=utf8
1rowinset(0.00sec)

其中，字段c1和c3上的检查约束使用了系统生成的名称；c2上的检查约束使用了自定义名称。

SQL标准中所有的约束（主键、唯一约束、外键、检查约束等）都属于相同的命名空间，意味着它们相互不能重名。但在MySQL中，每个数据库中的约束类型属于自己的命名空间；因此，主键和检查约束可以重名，但是两个检查约束不能重名。

我们插入一条测试数据：

mysql>insertintot1(c1,c2,c3)values(1,-1,100);
ERROR3819(HY000):Checkconstraint'c2_positive'isviolated.

插入数据的三个字段都违反了约束，结果显示的是违反了c2_positive；因为它按照名字排在第一，由此也可以看出MySQL按照约束的名字排序依次进行检查。

我们再插入一条测试数据：

mysql>insertintot1(c1,c2,c3)values(null,null,null);
QueryOK,1rowaffected(0.00sec)

数据插入成功，所以NULL值并不会违反检查约束。

表级检查约束

表级约束独立于字段的定义，而且可以针对多个字段进行约束，甚至可以出现在字段定义之前。例如：

mysql>droptablet1;
QueryOK,0rowsaffected(0.04sec)

mysql>CREATETABLEt1
->(
->CHECK(c1<>c2),
->c1INT,
->c2INT,
->c3INT,
->CONSTRAINTc1_nonzeroCHECK(c1<>0),
->CHECK(c1>c3)
->);
QueryOK,0rowsaffected(0.04sec)

mysql>showcreatetablet1\G
***************************1.row***************************
Table:t1
CreateTable:CREATETABLE`t1`(
`c1`intDEFAULTNULL,
`c2`intDEFAULTNULL,
`c3`intDEFAULTNULL,
CONSTRAINT`c1_nonzero`CHECK((`c1`<>0)),
CONSTRAINT`t1_chk_1`CHECK((`c1`<>`c2`)),
CONSTRAINT`t1_chk_2`CHECK((`c1`>`c3`))
)ENGINE=InnoDBDEFAULTCHARSET=utf8
1rowinset(0.00sec)

第一个约束t1_chk_1出现在字段定义之前，但是仍然可以引用c1和c2；第二个约束c1_nonzero使用了自定义的名称；第三个约束t1_chk_2在所有字段定义之后。

我们同样插入一些测试数据：

mysql>insertintot1(c1,c2,c3)values(1,2,3);
ERROR3819(HY000):Checkconstraint't1_chk_2'isviolated.

mysql>insertintot1(c1,c2,c3)values(null,2,3);
QueryOK,1rowaffected(0.01sec)

第一条记录中的c1小于c3，违反了检查约束t1_chk_2；第二条记录中的c1为NULL，检查约束t1_chk_2的结果为UNKNOWN，不违法约束。

强制选项

使用默认方式或者ENFORCED选项创建的约束处于强制检查状态，我们也可以将其修改为NOTENFORCED，从而忽略检查：

ALTERTABLEtbl_name
ALTER{CHECK|CONSTRAINT}symbol[NOT]ENFORCED

修改之后的检查约束仍然存在，但是不会执行检查。例如：

mysql>altertablet1
->altercheckt1_chk_1notenforced;
QueryOK,0rowsaffected(0.02sec)
Records:0Duplicates:0Warnings:0

mysql>showcreatetablet1\G
***************************1.row***************************
Table:t1
CreateTable:CREATETABLE`t1`(
`c1`intDEFAULTNULL,
`c2`intDEFAULTNULL,
`c3`intDEFAULTNULL,
CONSTRAINT`c1_nonzero`CHECK((`c1`<>0)),
CONSTRAINT`t1_chk_1`CHECK((`c1`<>`c2`))/*!80016NOTENFORCED*/,
CONSTRAINT`t1_chk_2`CHECK((`c1`>`c3`))
)ENGINE=InnoDBDEFAULTCHARSET=utf8
1rowinset(0.00sec)

从最新的定义可以看出，t1_chk_1处于NOTENFORCED状态。我们插入一条违反该约束的数据：

mysql>insertintot1(c1,c2,c3)values(1,1,0);
QueryOK,1rowaffected(0.01sec)

该记录的c1和c2相等，但是插入成功。

如果我们需要迁移一些低版本的历史数据时，它们可能会违反新的检查约束；此时可以先将该约束禁用，等数据迁移并处理完成之后，再次启用强制选项。

检查约束限制

MySQL中的CHECK条件表达式必须满足以下规则，否则无法创建检查约束：

允许使用非计算列和计算列，但是不允许使用AUTO_INCREMENT字段或者其他表中的字段。
允许使用字面值、确定性内置函数（即使不同用户，多次调用该函数，只要输入相同结果就相同）以及运算符。非确定性函数包括：CONNECTION_ID()、CURRENT_USER()、NOW()等等，它们不能用于检查约束。
不允许使用存储函数或者自定义函数。
不允许使用存储过程和函数参数。
不允许使用变量，包括系统变量、用户定义变量和存储程序的局部变量。
不允许使用子查询。

另外，禁用在CHECK约束字段上定义外键约束的参照操作（ONUPDATE、ONDELETE）；同理，存在外键约束参照操作的字段上也不允许创建CHECK约束。

对于INSERT、UPDATE、REPLACE、LOADDATA以及LOADXML语句，如果违反检查约束将会返回错误。此时，对于已经修改的数据处理取决于存储引擎是否支持事务，以及是否使用了严格SQL模式。

对于INSERTIGNORE、UPDATEIGNORE、REPLACE、LOADDATA…IGNORE以及LOADXML…IGNORE语句，如果违反检查约束将会返回警告并且跳过存在问题的数据行。

如果约束表达式的结果类型和字段的数据类型不同，MySQL将会执行隐式类型转换；如果类型转换失败或者丢失精度，将会返回错误。

总结

MySQL8.0.16新增的检查约束提高了MySQL实现业务完整性约束的能力，也使得MySQL更加遵循SQL标准。

到此这篇关于MySQL8.0新特性之检查约束的实现的文章就介绍到这了,更多相关MySQL8.0检查约束内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！