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;

請注意，將分號(;)放在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%';

接下來，使用以下語句從vps視圖中查詢數據：

SELECT * FROM vps;

執行上面查詢語句，得到以下結果 -

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

因為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);

請注意，新創建的員工通過vps視圖不可見，因為她的職位是IT經理，而不是VP。使用以下SELECT語句來驗證它。

SELECT * FROM employees WHERE employeeNumber=1703;

執行上面語句，得到以下結果 -

+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
| 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選項。

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表中，如下所示：

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

這次MySQL拒絕插入併發出以下錯誤消息：

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

最後，我們通過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);

MySQL發出1行受影響(Query OK, 1 row affected)。

可以通過根據vps視圖查詢數據來再次驗證插入操作。

SELECT * FROM vps;

如上查詢結果所示，它的確按預期工作了。

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

在本教學中，您學習了如何使用WITH CHECK OPTION子句來確保視圖的一致性。