# 确保视图一致性 在本教程中，您将学习如何使用`WITH CHECK OPTION`子句确保视图的一致性。 ## WITH CHECK OPTION子句简介 有时候，[创建](http://www.yiibai.com/mysql/create-sql-views-mysql.html)一个视图来显示表的部分数据。然而，简单视图是[可更新](http://www.yiibai.com/mysql/create-sql-updatable-views.html)的，因此可以更新通过视图不可见的数据。此更新使视图不一致。为了确保视图的一致性，在创建或修改视图时使用`WITH CHECK OPTION`子句。 下面说明了`WITH CHECK OPTION`子句的语法 - ```sql CREATE OR REPLACE VIEW view_name AS select_statement WITH CHECK OPTION; ``` 请注意，将分号(`;`)放在`WITH CHECK OPTION`子句的末尾，而不是在[SELECT](http://www.yiibai.com/mysql/select-statement-query-data.html)语句的末尾来定义视图。 我们来看一下使用`WITH CHECK OPTION`子句的例子。 ## MySQL WITH CHECK OPTION子句示例 首先，我们根据`employees`表[创建](http://www.yiibai.com/mysql/create-sql-views-mysql.html)一个名为`vps`的视图，以显示其职位为`VP`的员工，例如`VP Marketing`和 `VP Sales`。 ```sql CREATE OR REPLACE VIEW vps AS SELECT employeeNumber, lastname, firstname, jobtitle, extension, email, officeCode, reportsTo FROM employees WHERE jobTitle LIKE '%VP%'; ``` 接下来，使用以下语句从`vps`视图中查询数据： ```sql 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 ``` 因为`vps`是一个简单的视图，因此它是可更新的。 然后，我们通过`vps`视图将一行员工数据信息插入。 ```sql 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`语句来验证它。 ```sql 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 | ``` 但这可能不是我们想要的，因为通过`vps`视图暴露`VP`员工，而不是其他员工。 为了确保视图的一致性，用户只能显示或更新通过视图可见的数据，则在创建或修改视图时使用`WITH CHECK OPTION`。 让我们修改视图以包括`WITH CHECK OPTION`选项。 ```sql 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`子句。 之后，再次通过`vps`视图将一行插入`employees`表中，如下所示： ```sql INSERT INTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo) VALUES(1704,'John','Minsu','IT Staff','x9112','johnminsu@yiibai.com',1,1703); ``` 这次MySQL拒绝插入并发出以下错误消息： ```sql Error Code: 1369 - CHECK OPTION failed 'yiibaidb.vps' ``` 最后，我们通过`vps`视图将一个职位为`SVP Marketing`的员工插入`employees`表，看看MySQL是否允许这样做。 ```sql 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`)。 可以通过根据`vps`视图查询数据来再次验证插入操作。 ```sql 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 ``` 在本教程中，您学习了如何使用`WITH CHECK OPTION`子句来确保视图的一致性。 --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://hezhiqiang-book.gitbook.io/mysql/di-wu-zhang/que-bao-shi-tu-yi-zhi-xing.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.