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     | [email protected] | 1          |      1002 |
|           1076 | Firrelli | Jeff      | VP Marketing | x9273     | [email protected] | 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','[email protected]',1,1002);

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

SELECT * FROM employees WHERE employeeNumber=1703;

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

+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
| employeeNumber | lastName  | firstName | extension | email                 | officeCode | reportsTo | jobTitle             |
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
|           1703 | Bush      | Lily      | x9111     | [email protected]  | 1          |      1002 | IT Manager           |
|           1702 | Gerard    | Martin    | x2312     | [email protected]     | 4          |      1102 | Sales Rep            |
|           1625 | Kato      | Yoshimi   | x102      | [email protected]       | 5          |      1621 | Sales Rep            |
|           1621 | Nishi     | Mami      | x101      | [email protected]      | 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','[email protected]',1,1703);

這次MySQL拒絕插入並行出以下錯誤訊息：

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

最後，我們通過vps檢視將一個職位為SVP Marketing的員工插入employees表，看看MySQL是否允許這樣做。

INSERT INTO vps(employeeNumber,firstname,lastname,jobtitle,extension,email,officeCode,reportsTo)
VALUES(1704,'John','Minsu','SVP Marketing','x9112','[email protected]',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     | [email protected]           | 1          |      1002 |
|           1076 | Firrelli | Jeff      | VP Marketing  | x9273     | [email protected]           | 1          |      1002 |
|           1704 | Minsu    | John      | SVP Marketing | x9112     | [email protected] | 1          |      1076 |
+----------------+----------+-----------+---------------+-----------+--------------------------------+------------+-----------+
3 rows in set

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