您的位置：首页 > 数据库 > > 正文

mysql8.0配置优化参数（MySQL 8.0 新特性之检查约束的实现）

更多 时间：2022-01-15 01:25:30 类别：数据库 浏览量：1309

mysql8.0配置优化参数

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

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

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

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

MySQL 8.0.15 之前

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

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
    		•
  • mysql> select version();
  • +-----------+
  • | version() |
  • +-----------+
  • | 8.0.15  |
  • +-----------+
  • 1 row in set (0.00 sec)
  •  
  • mysql> CREATE TABLE t1
  •   -> (
  •   ->  c1 INT CHECK (c1 > 10),
  •   ->  c2 INT ,
  •   ->  c3 INT CHECK (c3 < 100),
  •   ->  CONSTRAINT c2_positive CHECK (c2 > 0),
  •   ->  CHECK (c1 > c3)
  •   -> );
  • Query OK, 0 rows affected (0.33 sec)
  •  
  • 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
  • ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  • 1 row in set (0.00 sec)
    		•

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

  • ?
  • 1
  • 2
    		•
  • mysql> insert into t1(c1, c2, c3) values(1, -1, 100);
  • Query OK, 1 row affected (0.06 sec)
    		•

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

    MySQL 8.0.16 之后

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

  • ?
  • 1
    		•
  • [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，创建并强制该约束；
    • 如果指定了 NOT ENFORCED，创建但是不强制该约束。这也意味着约束不会生效。

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

    列级检查约束

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

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
    		•
  • mysql> select version();
  • +-----------+
  • | version() |
  • +-----------+
  • | 8.0.16  |
  • +-----------+
  • 1 row in set (0.00 sec)
  •  
  • mysql> CREATE TABLE t1
  •   -> (
  •   ->  c1 INT CHECK (c1 > 10),
  •   ->  c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),
  •   ->  c3 INT CHECK (c3 < 100)
  •   -> );
  • Query OK, 0 rows affected (0.04 sec)
  •  
  • mysql> show create table t1\G
  • *************************** 1. row ***************************
  •     Table: t1
  • Create Table: CREATE TABLE `t1` (
  •  `c1` int DEFAULT NULL,
  •  `c2` int DEFAULT NULL,
  •  `c3` int DEFAULT NULL,
  •  CONSTRAINT `c2_positive` CHECK ((`c2` > 0)),
  •  CONSTRAINT `t1_chk_1` CHECK ((`c1` > 10)),
  •  CONSTRAINT `t1_chk_2` CHECK ((`c3` < 100))
  • ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • 1 row in set (0.00 sec)
    		•

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

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

    我们插入一条测试数据：

  • ?
  • 1
  • 2
    		•
  • mysql> insert into t1(c1, c2, c3) values(1, -1, 100);
  • ERROR 3819 (HY000): Check constraint 'c2_positive' is violated.
    		•

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

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

  • ?
  • 1
  • 2
    		•
  • mysql> insert into t1(c1, c2, c3) values(null, null, null);
  • Query OK, 1 row affected (0.00 sec)
    		•

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

    表级检查约束

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

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
    		•
  • mysql> drop table t1;
  • Query OK, 0 rows affected (0.04 sec)
  •  
  • mysql> CREATE TABLE t1
  •   -> (
  •   ->  CHECK (c1 <> c2),
  •   ->  c1 INT,
  •   ->  c2 INT,
  •   ->  c3 INT,
  •   ->  CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  •   ->  CHECK (c1 > c3)
  •   -> );
  • Query OK, 0 rows affected (0.04 sec)
  •  
  • mysql> show create table t1\G
  • *************************** 1. row ***************************
  •     Table: t1
  • Create Table: CREATE TABLE `t1` (
  •  `c1` int DEFAULT NULL,
  •  `c2` int DEFAULT NULL,
  •  `c3` int DEFAULT NULL,
  •  CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
  •  CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)),
  •  CONSTRAINT `t1_chk_2` CHECK ((`c1` > `c3`))
  • ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • 1 row in set (0.00 sec)
    		•

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

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

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
    		•
  • mysql> insert into t1(c1, c2, c3) values(1, 2, 3);
  • ERROR 3819 (HY000): Check constraint 't1_chk_2' is violated.
  •  
  • mysql> insert into t1(c1, c2, c3) values(null, 2, 3);
  • Query OK, 1 row affected (0.01 sec)
    		•

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

    强制选项

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

  • ?
  • 1
  • 2
    		•
  • ALTER TABLE tbl_name
  • ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
    		•

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

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
    		•
  • mysql> alter table t1
  •   -> alter check t1_chk_1 not enforced;
  • Query OK, 0 rows affected (0.02 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  •  
  • mysql> show create table t1\G
  • *************************** 1. row ***************************
  •     Table: t1
  • Create Table: CREATE TABLE `t1` (
  •  `c1` int DEFAULT NULL,
  •  `c2` int DEFAULT NULL,
  •  `c3` int DEFAULT NULL,
  •  CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
  •  CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)) /*!80016 NOT ENFORCED */,
  •  CONSTRAINT `t1_chk_2` CHECK ((`c1` > `c3`))
  • ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • 1 row in set (0.00 sec)
    		•

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

  • ?
  • 1
  • 2
    		•
  • mysql> insert into t1(c1, c2, c3) values(1, 1, 0);
  • Query OK, 1 row affected (0.01 sec)
    		•

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

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

    检查约束限制

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

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

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

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

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

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

    总结

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

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

    原文链接：https://blog.csdn.net/horses/article/details/106529341

    标签：mysql 检查约束
  • 上一篇：静态web服务器nginx（WEB服务器该选择 Apache 还是 Nginx？）
  • 下一篇：修改宝塔nginx端口（解决宝塔面板nginx/apache防火墙后无法启动）
    • 您可能感兴趣
    首页 编程学习 Web前端 数据库 软件设计
    开心学习 ©2013-2021 保留所有权利