上一篇：MySQL視圖（1）

---

基于其他視圖

案例對 WITH [CASCADED | LOCAL] CHECK OPTION 進行釋義

創建視圖時，可以基于表 / 多個表，也可以使用 其他視圖+表 / 其他視圖 + 其他視圖 的方式進行組合。

總結

• 更新視圖，同時也會更新基表數據。更新基表，對應的視圖數據也會更新。
• WITH CASCADED CHECK OPTION 會檢測當前視圖（v3）WHERE 子句約束以及所有底部視圖 WHERE 子句約束條件（v1/v2）
• 當使用 CASCADED 時，不僅需要滿足當前視圖 WHERE 子句約束，而且無論底部視圖是否有 WITH CHECK OPTION 選項，都需要滿足

drop table t1;
drop view v1,v2,v3;CREATE TABLE t1 (a INT);CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 10
WITH CHECK OPTION;CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 4
WITH LOCAL CHECK OPTION;CREATE VIEW v3 AS SELECT * FROM v2 WHERE a < 8
WITH CASCADED CHECK OPTION;INSERT INTO v3 values(9); -- 報錯，不符合 v3 視圖中小于8的約束
INSERT INTO v3 values(4); -- 報錯，不符合 v2 視圖中大于4的約束
INSERT INTO v3 values(11);-- 報錯，不符合 v1 視圖中大于4的約束
INSERT INTO v3 values(6); -- 正常，符合 v1/v2/v3 視圖約束
INSERT INTO v3 values(7); -- 正常，符合 v1/v2/v3 視圖約束

• WITH [CASCADED | LOCAL] CHECK OPTION 均會檢測多層視圖，如下案例，插入 v3 視圖數據時，會檢測 v1、v2 的數據約束條件
• with local check option 時，首先需要滿足當前視圖的條件，然后對于底部視圖，也是先看是否有指定的 with check option 選項語句，有的話對應處理，無則不需要滿足底部視圖的 WHERE 子句條件。

drop table t1;
drop view v1,v2,v3;CREATE TABLE t1 (a INT);CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 10
WITH CHECK OPTION;-- 沒有 check option 選項
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 4; CREATE VIEW v3 AS SELECT * FROM v2 WHERE a < 14
WITH LOCAL CHECK OPTION;INSERT INTO v3 values(3); -- 正常，因為基表 v2 沒有 check option 所以不做約束
INSERT INTO v3 values(4); -- 正常，因為基表 v2 沒有 check option 所以不做約束
INSERT INTO v3 values(11);-- 報錯，必須符合基表 v1 小于10的約束，因為有check option 選項
INSERT INTO v3 values(10);-- 報錯，必須符合基表 v1 小于10的約束，因為有check option 選項-- 初始化環境
drop table t1;
drop view v1,v2,v3;CREATE TABLE t1 (a INT);CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 10
WITH CHECK OPTION;-- 有 local check option 選項
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 4 
WITH LOCAL CHECK OPTION;CREATE VIEW v3 AS SELECT * FROM v2 WHERE a < 14
WITH LOCAL CHECK OPTION;INSERT INTO v3 values(3); -- 報錯，必須符合基表 v2 大于 4 的約束，因為有check option選項
INSERT INTO v3 values(4); -- 報錯，必須符合基表 v2 大于 4 的約束，因為有check option選項

當前視圖沒有 check option 約束時，可以更新當前視圖數據，但是在當前視圖中不顯示該數據（參考情況1）。同時需要保證在該視圖的 select_statement 中，如果對象是視圖，并且也沒有 check option 約束限制（參考情況2），有也沒事，但是不能限制，才可以正常插入、更新數據（參考情況3）。

-- 情況 1：視圖無 check option 選項，關聯表
drop table t1;
drop view v1,v2,v3;
CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 10;
INSERT INTO v1 values(15);  -- 正常，因為沒有 check option可以更新，但是在視圖中不顯示而已。-- 情況 2：視圖無 check option 選項，關聯其他視圖也無 check option 選項
drop table t1;
drop view v1,v2,v3;
CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 10
WITH CHECK OPTION;-- 沒有 check option 選項
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 4; 
CREATE VIEW v3 AS SELECT * FROM v2 WHERE a < 14;INSERT INTO v3 values(3); -- 正常，因為基表 v2 沒有 check option 所以不做約束
INSERT INTO v3 values(4); -- 正常，因為基表 v2 沒有 check option 所以不做約束
INSERT INTO v3 values(11);-- 報錯，必須符合基表 v1 小于10的約束，因為有check option 選項
INSERT INTO v3 values(15);-- 報錯，必須符合基表 v1/v3 的約束，因為有check option 選項-- 情況 3：視圖無 check option 選項，關聯其他視圖有 check option 選項
drop table t1;
drop view v1,v2,v3;
CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 10
WITH CHECK OPTION;-- 有 check option 選項
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 4 
WITH CHECK OPTION; 
CREATE VIEW v3 AS SELECT * FROM v2 WHERE a < 14;INSERT INTO v3 values(3); -- 報錯，必須符合視圖 v2 大于4的約束，因為有check option 選項
INSERT INTO v3 values(5); -- 正常

基于 WITH 通用表達式

CREATE VIEW t1_view as WITHcte1 AS (SELECT 1 as a, 2 as b FROM dual),cte2 AS (SELECT 1 as c, 4 as d FROM dual)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;SELECT * FROM t1_view;
+---+---+
| b | d |
+---+---+
| 2 | 4 |
+---+---+
1 row in set (0.00 sec)CREATE VIEW t1_view as WITHcte1 AS (SELECT 1 as a, 2 as b FROM dual),cte2 AS (SELECT 1 as c, 4 as d FROM dual)
SELECT * FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;SELECT * FROM t1_view;
+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 1 | 4 |
+---+---+---+---+
1 row in set (0.00 sec)

基于 CASE WHEN

• 直接使用不支持，但可以在 SELECT 里面

CREATE TABLE `t1` (`id` int NOT NULL AUTO_INCREMENT,`consumer` char(20) DEFAULT NULL,`brand` varchar(20) DEFAULT NULL,`sal` int DEFAULT NULL,PRIMARY KEY (`id`)
);
INSERT INTO t1 VALUES(1,'人民','蘋果',5000);
INSERT INTO t1 VALUES(2,'蘇教','小米',-4000);
INSERT INTO t1 VALUES(3,'深教','oppo',0);CREATE VIEW t1_view AS SELECT id, sal, sign(CASE WHEN sal=0 THEN 200 ELSE sal END ) FROM t1 ORDER BY id;

基于自定義存儲函數

MySQL :: MySQL 8.0 Reference Manual :: 15.1.23 CREATE VIEW Statement # create view

單個存儲函數

DELIMITER //
CREATE FUNCTION f_test()
returns varchar(20)
BEGINdeclare a varchar(200);SELECT CONCAT('111','222','333') FROM DUAL INTO a;return a;
END;
//
DELIMITER ;
CREATE VIEW t1_view AS SELECT f_test();

存儲函數+存儲過程

• 創建自定義函數的視圖，以下案例中，不允許在過程中更新自個兒的視圖，個人理解是先執行存儲過程中的 UPDATE 語句，再創建視圖相關數據，或者理解成，作為視圖依據的這個結果集，是不允許被更新的。

DROP VIEW t9_view;
DROP FUNCTION f_test;
DROP PROCEDURE p_test;DELIMITER //
CREATE FUNCTION f_test(samplename varchar(200))
RETURNS varchar(13)  
beginIF samplename IS NOT NULL thenCALL p_test(1);END IF;RETURN 0;
END;
//
CREATE PROCEDURE p_test(in p_in int)
BEGINUPDATE t9_view SET aa = p_in;
END;
//
DELIMITER ;CREATE VIEW t9_view(aa) AS SELECT f_test('xxxx');SELECT * FROM t9_view;
ERROR 1424 (HY000): Recursive stored functions and triggers are not allowed.

• 創建自定義函數的視圖，以下案例中，允許在過程中更新其他的表 / 視圖

DROP VIEW t9_view;
DROP FUNCTION f_test;
DROP PROCEDURE p_test;
DROP TABLE t1;
CREATE TABLE t1 (id int);
INSERT INTO t1 VALUES(1);DELIMITER //
CREATE FUNCTION f_test(samplename varchar(200))
RETURNS varchar(13)  
beginIF samplename IS NOT NULL thenCALL p_test(3);END IF;RETURN 0;
END;
//
CREATE PROCEDURE p_test(in p_in int)
BEGINupdate t1 set id=p_in;
END;
//
DELIMITER ;
CREATE VIEW t9_view(aa) AS SELECT f_test('xxxx');
SELECT * FROM t9_view;
+------+
| aa   |
+------+
| 0    |
+------+
1 row in set (0.01 sec)mysql> SELECT * FROM t1;
+------+
| id   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

• 視圖可能依賴于存儲函數，該函數可能調用其他存儲例程。例如，以下視圖調用存儲函數f（）：

CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);Suppose that f() contains a statement such as this:IF name IS NULL thenCALL p1();
ELSECALL p2();
END IF;

上面是官方案例，下面案例是我自己寫的

CREATE TABLE t1 (id int NOT NULL,consumer char(20) DEFAULT NULL,price varchar(20) DEFAULT NULL,sal int DEFAULT NULL,datetime1 DATE,`datetime2` bigint
);
INSERT INTO t1 VALUES(1,'書局','7000',5000,'2020-09-22',20200923121200);
INSERT INTO t1 VALUES(2,'微山','3000',-4000,'2020-09-21',20200921101159);
INSERT INTO t1 VALUES(3,'深咖','5000',0,'2020-08-21',20190821101159);DELIMITER //
CREATE FUNCTION f_test(samplename varchar(200))
returns varchar(13)  
beginIF samplename IS NOT NULL thenCALL p_test(4);END IF;RETURN 4;
END;
//
CREATE PROCEDURE p_test(in p_in int)
beginupdate t1 set id=p_in;
END;
//
DELIMITER ;CREATE VIEW t9_view AS SELECT * FROM t1 WHERE t1.id = f_test(t1.consumer);
SELECT * FROM t9_view;
ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger 
because it is already used by statement which invoked this stored function/trigger.

總結：

1）不支持遞歸存儲函數，即創建自定義函數視圖，在自定義函數體中調用存儲過程，過程會更改該視圖相關信息，參考案例 一

2）創建自定義函數的視圖，允許在過程中更新其他的表 / 視圖，參考案例 二

3）綜合案例1~3來看，使用函數的方式創建視圖，無論是直接 SELECT 函數 還是 以函數作為 WHERE 子句的條件來創建的視圖，在函數 / 過程中都不能 INSERT / UPDATE 創建視圖時的基礎表（insert操作驗證過，未寫出來）。

ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}

Merge：當使用視圖時，會把查詢視圖的語句和創建視圖的語句合并起來，形成一條語句，最后再從基表中查詢

TEMPTABLE：先把定義視圖的語句執行起來，把數據放在一張系統定義的臨時表，然后執行用戶的sql語句

DROP TABLE t1;
CREATE TABLE t1 (c1 INT, c2 int);
insert into t1 values(3,3);
insert into t1 values(4,4);
insert into t1 values(1,1);
insert into t1 values(2,2);DROP VIEW v_merge;
DROP VIEW v_merge2;-- 定義為 MERGE
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t1 WHERE c1 < 100 ORDER BY c1 DESC, c2 ASC;table v_merge;
select * from v_merge group by vc1;
+------+------+
| vc1  | vc2  |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    1 |    1 |
|    2 |    2 |
+------+------+
4 rows in set (0.01 sec)-- 定義為 TEMPTABLE
CREATE ALGORITHM = TEMPTABLE VIEW v_merge2 (vc1, vc2) AS
SELECT c1, c2 FROM t1 WHERE c1 < 100 ORDER BY c1 DESC, c2 ASC;table v_merge2;
select * from v_merge2 group by vc1;
+------+------+
| vc1  | vc2  |
+------+------+
|    4 |    4 |
|    3 |    3 |
|    2 |    2 |
|    1 |    1 |
+------+------+
4 rows in set (0.01 sec)

修改視圖

MySQL :: MySQL 8.0 Reference Manual :: 15.1.11 ALTER VIEW Statement

ALTER[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}][DEFINER = user][SQL SECURITY { DEFINER | INVOKER }]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]

select_stmt

• 是一種 SELECT 語句。它給出了視圖的定義。該語句可以從基表或其他視圖進行選擇。

column_name_list

• 視圖必須具有唯一的列名，不得有重復，就像基表那樣。缺省情況下，由 SELECT 語句檢索的列名將用作視圖列名。
• 要想為視圖列定義明確的名稱，可使用可選的 column_name_list 子句，列出由逗號隔開的 column_name。
• column_name_list 中的名稱數目必須等于 SELECT 語句檢索的列數。
• SELECT 語句檢索的列可以是對表列的簡單引用。也可以是使用函數、常量值、操作符等的表達式。

刪除視圖

當前用戶必須在每個視圖上有 DROP 權限。

MySQL :: MySQL 8.0 Reference Manual :: 15.1.35 DROP VIEW Statement

drop_view_stmt:DROP VIEW [IF EXISTS] view_name_list [CASCADE | RESTRICT];view_name_list:view_name [, view_name_list]

IF EXISTS

• 使用 IF EXISTS 關鍵字可以防止由于視圖不存在而出錯。

view_name_list

• 如果 view_name_list 中包含一部分存在和一部分不存在的視圖，執行可能報錯但是存在的視圖依然會被刪除。

CASCADE | RESTRICT

• CASCADE 為級聯刪除，自動刪除依賴此視圖的對象。

• RESTRICT 為約束刪除，如果有依賴對象存在，則拒絕刪除此視圖。

DROP VIEW v1,v2;
DROP VIEW IF EXISTS v3,v4;

可更新和不可更新視圖

MySQL :: MySQL 8.0 Reference Manual :: 27.5.3 Updatable and Insertable Views

https://www.cnblogs.com/zhuchenglin/p/9602569.html

要使視圖可更新，視圖中的行與基礎表中的行之間必須存在一對一的關系。還有一些其他構造使視圖不可更新。更具體地說，如果視圖包含以下任何內容，則該視圖不可更新：

1. 聚合函數（SUM(), MIN(), MAX(), COUNT()等）。
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION 或 UNION ALL
6. select 列表中的子查詢 （原話：Subquery in the select list）

• • 選擇列表中的非依賴子查詢無法進行插入，但可以進行更新、刪除。對于選擇列表中的依賴子查詢，不允許使用數據更改語句。

1. Join 鏈接
2. FROM子句中引用不可更新視圖
3. WHERE子句中引用FROM子句中表的子查詢
4. 僅引用文字值（在該情況下，沒有要更新的基本表）。
5. ALGORITHM = TEMPTABLE（使用臨時表總會使視圖成為不可更新的）。
6. 對基表任意列的多次引用（插入會失敗。更新/刪除 均正常）

視圖中生成的列被認為是可更新的，因為它可以指定給它。但是，如果顯式更新了此類列，則唯一允許的值是默認值。

多表視圖有時可能是可更新的，前提是可以使用合并算法(ALGORITHM = MERGE)進行處理。要使其工作，視圖必須使用內部聯接( inner join)（而不是外部聯接或并集，not an outer join or a UNION）。此外，視圖定義中只能更新一個表，因此SET子句只能命名視圖中某個表中的列。不允許使用UNION ALL的視圖，即使它們在理論上是可更新的。

INSERT

使用 INSERT 語句時，INSERT 語句中含有被合并的視圖引用（視圖聯接視圖的引用），如果是視圖聯接視圖的引用（案例中的vjoin），則這個視圖的所有組件必須都是可更新的（不是物化）。

對于多表組合成的可更新視圖，INSERT 是可以正常插入該視圖的其中一張表的。

CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
CREATE VIEW vup AS SELECT * FROM t2;
CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;--此語句無效，因為聯接視圖的一個組件不可更新
INSERT INTO vjoin (c) VALUES (1);-- 本聲明有效；該視圖不包含物化組件
INSERT INTO vup (c) VALUES (1);

UPDATE

使用 UPDATE 語句更新的，有可能是被合并的一個或多個表的視圖引用。如果更新的是一個視圖聯接視圖，則必須保證聯接視圖中至少一個組件（其中一個視圖）是可以更新的，這里跟 INSERT 不同。

一個多表更新語句，語句更新表引用的必須是基表或可更新視圖的引用，不可更新表的引用可能是物化視圖或者派生表。

CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
CREATE VIEW vup AS SELECT * FROM t2;
CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;-- 本聲明有效，列c來自聯接視圖的可更新部分：
UPDATE vjoin SET c=c+1;-- 本聲明無效，列x來自不可更新的部分：
UPDATE vjoin SET x=x+1;-- 本聲明有效，多表更新的更新表引用是一個可更新視圖（vup）：
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET c=c+1;-- 本聲明無效，它嘗試更新一個物化派生表：
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET s=s+1;

DELETE

單表或多表被 DELETE 語句刪除，前提是必須是合并視圖，聯接視圖則不被允許（與 INSERT / UPDATE 不同）。

CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
CREATE VIEW vup AS SELECT * FROM t2;
CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;--此語句無效，因為該視圖是聯接視圖（the view is a join view）：
DELETE FROM vjoin WHERE c=1;-- 此語句有效，因為該視圖是一個被合并的（可更新）視圖：
DELETE FROM vup WHERE c=2;-- 此語句有效，因為它從一個被合并的（可更新）視圖中刪除：
DELETE vup FROM vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON dt.s=vup.c;

如果并非所有列都是簡單列引用（例如，如果視圖包含表達式或復合表達式的列），則視圖不可插入。雖然這樣的視圖是不可插入的，但如果只更新非表達式的列，則它是可更新的。考慮以下觀點：

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;-- 可以更新
UPDATE v SET col1 = 0;-- 此視圖不可插入，因為col2是一個表達式。
UPDATE v SET col2=0；