MySQL Views & the WITH CHECK OPTION Clause

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

MySQL 视图和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;Code language: SQL (Structured Query Language) (sql)

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

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

MySQL 视图和WITH CHECK OPTION示例

首先，根据employees表创建一个名为vps的视图，以显示职位为 VP 的员工，例如销售副总裁、营销副总裁。

CREATE OR REPLACE VIEW vps AS
    SELECT 
        employeeNumber,
        lastname,
        firstname,
        jobtitle,
        extension,
        email,
        officeCode,
        reportsTo
    FROM
        employees
    WHERE
        jobTitle LIKE '%VP%';Code language: SQL (Structured Query Language) (sql)

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

SELECT * FROM vps;Code language: SQL (Structured Query Language) (sql)

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

然后，通过vps视图向employees表中插入一行。

INSERT INTO vps(
    employeeNumber,
    firstName,
    lastName,
    jobTitle,
    extension,
    email,
    officeCode,
    reportsTo
) 
VALUES(
    1703,
    'Lily',
    'Bush',
    'IT Manager',
    'x9111',
    'lilybush@classicmodelcars.com',
    1,
    1002
);Code language: SQL (Structured Query Language) (sql)

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

SELECT 
   * 
FROM 
   employees
ORDER BY 
   employeeNumber DESC;Code language: SQL (Structured Query Language) (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;Code language: SQL (Structured Query Language) (sql)

请注意CREATE OR REPLACE语句末尾的WITH CHECK OPTION 。

之后，通过vps视图向employees表中插入一行：

INSERT INTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo)
VALUES(1704,'John','Smith','IT Staff','x9112','johnsmith@classicmodelcars.com',1,1703);Code language: SQL (Structured Query Language) (sql)

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

Error Code: 1369. CHECK OPTION failed 'classicmodels.vps'Code language: SQL (Structured Query Language) (sql)

最后通过vps视图将职称为SVP Marketing的员工插入到employees表中，看看是否允许。

INSERT INTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo)
VALUES(1704,'John','Smith','SVP Marketing','x9112','johnsmith@classicmodelcars.com',1,1076);Code language: SQL (Structured Query Language) (sql)

MySQL 发出消息：

1 rows(s) affected.

您可以通过从vps视图查询数据来验证插入。

SELECT * FROM vps;Code language: SQL (Structured Query Language) (sql)

它按预期工作。

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