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

学习如何使用WITH CHECK OPTION子句确保视图的一致性。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

WITH CHECK OPTION子句简介

有时候，创建一个视图来显示表的部分数据。然而，简单视图是可更新的，因此可以更新通过视图不可见的数据。此更新使视图不一致。为了确保视图的一致性，在创建或修改视图时使用WITH CHECK OPTION子句。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

下面说明了WITH CHECK OPTION子句的语法 -文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

CREATE OR REPLACE VIEW view_name 
AS
  select_statement
  WITH CHECK OPTION;

请注意，将分号(;)放在WITH CHECK OPTION子句的末尾，而不是在SELECT语句的末尾来定义视图。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

我们来看一下使用WITH CHECK OPTION子句的例子。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

MySQL WITH CHECK OPTION子句示例

首先，我们根据employees表创建一个名为vps的视图，以显示其职位为VP的员工，例如VP Marketing和 VP Sales。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

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

接下来，使用以下语句从vps视图中查询数据：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

SELECT * FROM vps;

执行上面查询语句，得到以下结果 -文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

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

因为vps是一个简单的视图，因此它是可更新的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

然后，我们通过vps视图将一行员工数据信息插入。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

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

请注意，新创建的员工通过vps视图不可见，因为她的职位是IT经理，而不是VP。使用以下SELECT语句来验证它。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

SELECT * FROM employees WHERE employeeNumber=1703;

执行上面语句，得到以下结果 -文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
| 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            |

但这可能不是我们想要的，因为通过vps视图暴露VP员工，而不是其他员工。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

为了确保视图的一致性，用户只能显示或更新通过视图可见的数据，则在创建或修改视图时使用WITH CHECK OPTION。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

让我们修改视图以包括WITH CHECK OPTION选项。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

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

请注意在CREATE OR REPLACE语句的结尾处加上WITH CHECK OPTION子句。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

之后，再次通过vps视图将一行插入employees表中，如下所示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

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

这次MySQL拒绝插入并发出以下错误消息：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

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

最后，我们通过vps视图将一个职位为SVP Marketing的员工插入employees表，看看MySQL是否允许这样做。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

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

MySQL发出1行受影响(Query OK, 1 row affected)。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

可以通过根据vps视图查询数据来再次验证插入操作。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

SELECT * FROM vps;

如上查询结果所示，它的确按预期工作了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html

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

在本教程中，您学习了如何使用WITH CHECK OPTION子句来确保视图的一致性。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/8418.html