MySQL :: MySQL 8.4 参考手册 :: 27.5.4 视图 WITH CHECK OPTION 子句

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 27 存储对象 / 27.5 使用视图 / 27.5.4 视图 WITH CHECK OPTION 子句

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

27.5.4 视图的 WITH CHECK OPTION 子句27.5.4 The View WITH CHECK OPTION Clause

可为可更新视图指定WITH CHECK OPTION子句，以防止向select_statement中的WHERE子句不为真的行插入数据。它还防止对WHERE子句为真但更新会导致其不为真的行进行更新（换句话说，它防止将可见行更新为不可见行）。The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts to rows for which the WHERE clause in the select_statement is not true. It also prevents updates to rows for which the WHERE clause is true but the update would cause it to be not true (in other words, it prevents visible rows from being updated to nonvisible rows).

在可更新视图的WITH CHECK OPTION子句中，LOCAL和CASCADED关键字决定在视图以另一个视图定义时检查测试的范围。当未给出任何关键字时，默认值为CASCADED。In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. When neither keyword is given, the default is CASCADED.

WITH CHECK OPTION测试符合标准：WITH CHECK OPTION testing is standard-compliant:

使用 LOCAL 时，会检查视图的 WHERE 子句，然后检查会递归到底层视图，并应用相同的规则。With LOCAL, the view WHERE clause is checked, then checking recurses to underlying views and applies the same rules.
使用 CASCADED 时，会检查视图的 WHERE 子句，然后检查会递归到底层视图，为它们添加 WITH CASCADED CHECK OPTION（仅用于检查目的；它们的定义保持不变），并应用相同的规则。With CASCADED, the view WHERE clause is checked, then checking recurses to underlying views, adds WITH CASCADED CHECK OPTION to them (for purposes of the check; their definitions remain unchanged), and applies the same rules.
如果没有检查选项，则不会检查视图的 WHERE 子句，然后检查会递归到底层视图，并应用相同的规则。With no check option, the view WHERE clause is not checked, then checking recurses to underlying views, and applies the same rules.

考虑以下表和视图集合的定义：Consider the definitions for the following table and set of views:

CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
WITH CHECK OPTION;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
WITH LOCAL CHECK OPTION;
CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
WITH CASCADED CHECK OPTION;

这里 v2 和 v3 视图是基于另一个视图 v1 定义的。Here the v2 and v3 views are defined in terms of another view, v1.

对于 v2 的插入会根据它的 LOCAL 检查选项进行检查，然后检查会递归到 v1 并再次应用规则。v1 的规则会导致检查失败。v3 的检查也失败：Inserts for v2 are checked against its LOCAL check option, then the check recurses to v1 and the rules are applied again. The rules for v1 cause a check failure. The check for v3 also fails:

mysql> INSERT INTO v2 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v2'
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'

首页上一级下一节