使用WITH CHECK OPTION确保视图一致性

---

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

---

WITH CHECK OPTION 子句简介

有时，您创建一个视图以仅显示表的部分数据。但是，简单视图是可更新的，因此可以更新通过视图不可见的数据。此更新使视图不一致。要确保视图的一致性，请WITH CHECK OPTION在创建或修改视图时使用子句。 WITH CHECK OPTION子句是CREATE VIEW声明的可选部分。WITH CHECK OPTION子句阻止您  更新或插入通过视图不可见的行。换句话说，每当您通过视图更新或插入基表的一行时，MySQL都会确保插入或更新操作符合视图的定义。 以下说明了WITH CHECK OPTION子句的语法。

CREATE OR REPLACE VIEW view_name 
AS
  select_statement
  WITH CHECK OPTION;

请注意，您将分号（;）放在WITH CHECK OPTION子句的末尾，而不是在定义视图的SELECT语句的末尾。 我们来看一个使用WITH CHECK OPTION子句的例子。

---

MySQL WITH CHECK OPTION子句示例

首先，我们创建一个vps基于employees表命名的视图，以揭示其职称为VP的员工，例如，销售副总裁，营销副总裁。

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

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

SELECT 
    *
FROM
    vps;

+----------------+-----------+-----------+--------------+-----------+----------------------+------------+-----------+
| employeeNumber | lastname  | firstname | jobtitle     | extension | email                | officeCode | reportsTo |
+----------------+-----------+-----------+--------------+-----------+----------------------+------------+-----------+
|           1056 | Patterson | Mary      | VP Sales     | x4611     | mpatterso@yiibai.com | 1          |      1002 |
|           1076 | Firrelli  | Jeff      | VP Marketing | x9273     | jfirrelli@yiibai.com | 1          |      1002 |
+----------------+-----------+-----------+--------------+-----------+----------------------+------------+-----------+
2 rows in set (0.00 sec)

因为它vps 是一个简单的视图因此它是可更新的。 然后，我们插入一行到employees通过vps视图。

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

SELECT 
    *
FROM
    employees
ORDER BY 
    employeeNumber DESC;

+----------------+-----------+-----------+-----------+-------------------------------+------------+-----------+----------------------+
| employeeNumber | lastName  | firstName | extension | email                         | officeCode | reportsTo | jobTitle             |
+----------------+-----------+-----------+-----------+-------------------------------+------------+-----------+----------------------+
|           1703 | Bush      | Lily      | x9111     | lilybush@classicmodelcars.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            |
|           1619 | King      | Tom       | x103      | tking@gmail.com               | 6          |      1088 | Sales Rep            |
...

这可能不是我们想要的，因为我们只通过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;

注意声明的WITH CHECK OPTION在CREATE OR REPLACE末尾。 之后，我们通过视图在employees表中插入一行vps，如下所示：

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

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

ERROR 1369 (HY000): CHECK OPTION failed 'mysqldemo.vps'

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

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

MySQL发布了1行受影响。 我们可以通过vps查看视图中的数据来再次验证插入操作。

SELECT 
    *
FROM
    vps;

+----------------+-----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
| employeeNumber | lastname  | firstname | jobtitle      | extension | email                          | officeCode | reportsTo |
+----------------+-----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
|           1056 | Patterson | Mary      | VP Sales      | x4611     | mpatterso@yiibai.com           | 1          |      1002 |
|           1076 | Firrelli  | Jeff      | VP Marketing  | x9273     | jfirrelli@yiibai.com           | 1          |      1002 |
|           1704 | Smith     | John      | SVP Marketing | x9112     | johnsmith@classicmodelcars.com | 1          |      1076 |
+----------------+-----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
3 rows in set (0.00 sec)

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

---