当前位置：首页 > news >正文

MySQL 视图（2）

news 2025/7/12 8:04:24

上一篇：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

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。还有一些其他构造使视图不可更新。更具体地说，如果视图包含以下任何内容，则该视图不可更新：

聚合函数（SUM(), MIN(), MAX(), COUNT()等）。
DISTINCT
GROUP BY
HAVING
UNION 或 UNION ALL
select 列表中的子查询（原话：Subquery in the select list）

- 选择列表中的非依赖子查询无法进行插入，但可以进行更新、删除。对于选择列表中的依赖子查询，不允许使用数据更改语句。

Join 链接
FROM子句中引用不可更新视图
WHERE子句中引用FROM子句中表的子查询
仅引用文字值（在该情况下，没有要更新的基本表）。
ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。
对基表任意列的多次引用（插入会失败。更新/删除均正常）

视图中生成的列被认为是可更新的，因为它可以指定给它。但是，如果显式更新了此类列，则唯一允许的值是默认值。

多表视图有时可能是可更新的，前提是可以使用合并算法(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；