MySQL学习入门教程：使用WITH CHECK OPTION子句确保视图一致性

学习如何使用WITH CHECK OPTION子句确保视图的一致性。

WITH CHECK OPTION子句简介

有时候，创建一个视图来显示表的部分数据。然而，简单视图是可更新的，因此可以更新通过视图不可见的数据。此更新使视图不一致。为了确保视图的一致性，在创建或修改视图时使用WITH CHECK OPTION子句。

下面说明了WITH CHECK OPTION子句的语法 -

CREATE OR REPLACE VIEW view_name 
AS
  select_statement
  WITH CHECK OPTION;

SQL

请注意，将分号(;)放在WITH CHECK OPTION子句的末尾，而不是在SELECT语句的末尾来定义视图。

我们来看一下使用WITH CHECK OPTION子句的例子。

MySQL WITH CHECK OPTION子句示例

首先，我们根据employees表创建一个名为vps的视图，以显示其职位为VP的员工，例如VP Marketing和 VP Sales。

CREATE OR REPLACE VIEW vps AS
    SELECT 
        employeeNumber,
        lastname,
        firstname,
        jobtitle,
        extension,
        email,
        officeCode,
        reportsTo
    FROM
        employees
    WHERE
        jobTitle LIKE '%VP%';

SQL

接下来，使用以下语句从vps视图中查询数据：

SELECT * FROM vps;

SQL

执行上面查询语句，得到以下结果 -

mysql> SELECT * FROM vps;
+----------------+----------+-----------+--------------+-----------+----------------------+------------+-----------+
| employeeNumber | lastname | firstname | jobtitle     | extension | email                | officeCode | reportsTo |
+----------------+----------+-----------+--------------+-----------+----------------------+------------+-----------+
|           1056 | Hill     | Mary      | VP Sales     | x4611     | mary.hill@yiibai.com | 1          |      1002 |
|           1076 | Firrelli | Jeff      | VP Marketing | x9273     | jfirrelli@yiibai.com | 1          |      1002 |
+----------------+----------+-----------+--------------+-----------+----------------------+------------+-----------+
2 rows in set

SQL

因为vps是一个简单的视图，因此它是可更新的。

然后，我们通过vps视图将一行员工数据信息插入。

INSERT INTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo)
values(1703,'Lily','Bush','IT Manager','x9111','lilybush@yiiibai.com',1,1002);

SQL

请注意，新创建的员工通过vps视图不可见，因为她的职位是IT经理，而不是VP。使用以下SELECT语句来验证它。

SELECT * FROM employees WHERE employeeNumber=1703;

SQL

执行上面语句，得到以下结果 -

+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
| employeeNumber | lastName  | firstName | extension | email                 | officeCode | reportsTo | jobTitle             |
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
|           1703 | Bush      | Lily      | x9111     | lilybush@yiiibai.com  | 1          |      1002 | IT Manager           |
|           1702 | Gerard    | Martin    | x2312     | mgerard@gmail.com     | 4          |      1102 | Sales Rep            |
|           1625 | Kato      | Yoshimi   | x102      | ykato@gmail.com       | 5          |      1621 | Sales Rep            |
|           1621 | Nishi     | Mami      | x101      | mnishi@gmail.com      | 5          |      1056 | Sales Rep            |

SQL

但这可能不是我们想要的，因为通过vps视图暴露VP员工，而不是其他员工。

为了确保视图的一致性，用户只能显示或更新通过视图可见的数据，则在创建或修改视图时使用WITH CHECK OPTION。

让我们修改视图以包括WITH CHECK OPTION选项。

CREATE OR REPLACE VIEW vps AS
    SELECT 
        employeeNumber,
        lastname,
        firstname,
        jobtitle,
        extension,
        email,
        officeCode,
        reportsTo
    FROM
        employees
    WHERE
        jobTitle LIKE '%VP%' 
WITH CHECK OPTION;

SQL

请注意在CREATE OR REPLACE语句的结尾处加上WITH CHECK OPTION子句。

之后，再次通过vps视图将一行插入employees表中，如下所示：

INSERT INTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo)
VALUES(1704,'John','Minsu','IT Staff','x9112','johnminsu@yiibai.com',1,1703);

SQL

这次MySQL拒绝插入并发出以下错误消息：

Error Code: 1369 - CHECK OPTION failed 'yiibaidb.vps'

SQL

最后，我们通过vps视图将一个职位为SVP Marketing的员工插入employees表，看看MySQL是否允许这样做。

INSERT INTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo)
VALUES(1704,'John','Minsu','SVP Marketing','x9112','johnminsu@classicmodelcars.com',1,1076);

SQL

MySQL发出1行受影响(Query OK, 1 row affected)。

可以通过根据vps视图查询数据来再次验证插入操作。

SELECT * FROM vps;

SQL

如上查询结果所示，它的确按预期工作了。

mysql> SELECT  * FROM vps;
+----------------+----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
| employeeNumber | lastname | firstname | jobtitle      | extension | email                          | officeCode | reportsTo |
+----------------+----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
|           1056 | Hill     | Mary      | VP Sales      | x4611     | mary.hill@yiibai.com           | 1          |      1002 |
|           1076 | Firrelli | Jeff      | VP Marketing  | x9273     | jfirrelli@yiibai.com           | 1          |      1002 |
|           1704 | Minsu    | John      | SVP Marketing | x9112     | johnminsu@classicmodelcars.com | 1          |      1076 |
+----------------+----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
3 rows in set

SQL

在本教程中，您学习了如何使用WITH CHECK OPTION子句来确保视图的一致性。