MySQL 视图（1）

Crett

已于 2024-05-31 11:08:33 修改

阅读量1k

点赞数 27

分类专栏： MySQL 文章标签：数据库 mysql

于 2024-05-24 01:07:36 首次发布

本文链接：https://blog.csdn.net/wejfoasdbsdg/article/details/139160765

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

常用视图语句

-- 创建视图
CREATE VIEW t1_view AS SELECT *  FROM t1;
CREATE VIEW v AS VALUES ROW(1,2);

-- 查询视图
SELECT * FROM t1_view;

-- 查询视图的相关系统视图
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';

-- 删除视图
DROP VIEW v1,v2;
DROP VIEW IF EXISTS v3,v4;

-- 修改视图
ALTER VIEW t1_view AS select * from xxxx;

关于视图

视图是从一个或多个表导出的虚拟的表。本质上，视图是一个存储好的查询，用户通过访问这个视图来获取该视图定义的数据。

视图的优势

把经常使用的数据定义为视图以简化SQL操作。数据库的查询大多要使用聚合函数，同时还要显示其它字段的信息，可能还会需要关联到其它表，这时涉及的 SQL 语句可能比较复杂。如果需要频繁执行此查询，就可以通过创建视图简化查询操作，之后只需要执行 SELECT * FROM view_name 就可以获得预期结果。
视图限制用户查询和修改可视的数据范围，提高了数据的安全性。视图是动态的数据集合，数据随着基表的更新而更新。但是视图是虚拟的，物理上是不存在的，只是存储了数据的集合，所以可以将基表中重要的字段信息屏蔽，不通过视图展示给用户。
视图拥有逻辑上的独立性，屏蔽了真实表的结构带来的影响。视图可以使应用程序和数据库的表在一定程度上互相独立。如果没有视图，应用一定是建立在表上的。创建了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。

视图的特点

与表不同，视图没有分配存储空间。从视图引用的基表中提取或派生数据（除了用于定义数据字典中视图的查询的存储之外，它不需要其他存储）。
在数据库中，基表和视图共享相同的命名空间，因此基表和视图不能具有相同的名称。
视图中无法存在相同名称的列，也无法创建。
可更新视图数据更新后，基表数据同时也会更新（通常情况），但需要注意视图数据与基表非一一对应，或者基表有约束或其他关联，视图数据可能更新失败。

视图限制

MySQL :: MySQL 8.0 Reference Manual :: 27.9 Restrictions on Views # 视图限制

视图定义在创建时被“冻结”，并且不受基础表定义的后续更改的影响。例如，如果在表上将视图定义为SELECT*，则稍后添加到基表中的新列不会成为视图的一部分，视图不会报错，但是从基表中删除列会导致查询视图中时出错。
视图需要 CREATE_VIEW / SELECT / DROP 权限，以下一一对应。

- 创建视图。
- 对于SELECT语句中其他位置使用的列，需要 SELECT 权限。
- 对于 OR REPLACE 子句，则还必须具有该视图的DROP权限。
- 如果存在 DEFINER 子句，则所需的权限取决于用户。

视图列如果手动定义数量，则需要与后面的 select_statement 数量一致。
无法在视图上建立索引。
- 索引可用于使用在合并算法处理的视图。但是，使用 temptable 算法处理的视图无法利用其基础表上的索引（尽管可以在生成临时表期间使用索引）。

SELECT语句不能引用系统变量或用户定义的变量。
在存储程序中，SELECT语句不能引用程序参数或局部变量。
SELECT语句不能引用已准备好的语句参数。
定义中引用的任何表或视图都必须存在。如果在创建视图后，删除了定义引用的表或视图，则使用该视图会导致错误。要检查视图定义是否存在此类问题，可以使用 CHECK TABLE 语句。
定义不能引用临时表，也不能创建临时视图。
不能将触发器与视图关联。
SELECT语句中列名的别名将根据64个字符的最大列长度（而不是256个字符的最大别名长度）进行检查。
视图定义中允许使用ORDER BY，但如果 select from view，view 具有自己 ORDER BY 语句，则将忽略 ORDER BY。
- 例如，如果视图定义包含LIMIT子句，并且您查询该视图的语句另外包含有LIMIT子句，则它未定义应该应用哪个LIMIT子句。
- 同样的原理也适用于SELECT关键字后面的ALL、DISTINCT或SQL_SMALL_RESULT等选项，以及INTO、FOR UPDATE、FOR SHARE、LOCK IN SHARE MODE和PROCEDURE等子句。

原文：For example, if a view definition includes a LIMIT clause, and you select from the view using a statement that has its own LIMIT clause, it is undefined which limit applies.

创建视图

语法分析

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

[ OR REPLACE ]

若存在相同视图名称，则可以使用该语句，进行替换视图，反之则创建该视图。

[ ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE } ]

MySQL :: MySQL 8.0 Reference Manual :: 27.5.2 View Processing Algorithms

名为：算法，ALGORITHM：可选的ALGORITHM子句是对标准SQL的MySQL扩展，可取三个值：MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句，默认算法是UNDEFINED(未定义的)。算法会影响MySQL处理视图的方式。

对于MERGE，会将引用视图的语句的文本与视图定义时的文本合并起来，使得视图定义的某一部分取代语句的对应部分。

解释：当使用视图时，会把查询视图的语句和创建视图的语句合并起来，形成一条语句，最后再从基表中查询

对于TEMPTABLE，当使用视图时，会把创建视图的语句的查询结果当成一张临时表，再从临时表中进行筛选。

解释：先把定义视图的语句执行起来，把数据放在一张系统定义的临时表，然后执行用户的sql语句

对于UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。
明确选择TEMPTABLE的1个原因在于，创建临时表之后、并在完成语句处理之前，能够释放基表上的锁定。与MERGE算法相比，锁定释放的速度更快，这样，使用视图的其他客户端不会被屏蔽过长时间。

[DEFINER = user]

可以参考 PL/SQL 中 definer 功能与之类似，用来定义其他用户，来进行权限限制。

[SQL SECURITY { DEFINER | INVOKER }]

使用 DEFINER 定义用户后，不写该属性，默认为 SQL SECURITY DEFINER
使用 SQL SECURITY INVOKER 后，DEFINER 定义直接无效。

VIEW view_name [(column_list)]

定义视图名称，以及自定义视图列名，注意列数量 (column_list) 要与后面的 select_statement 列数量一致。

AS select_statement

MySQL :: MySQL 8.0 Reference Manual :: 15.2.13 SELECT Statement

这 select_statement 是一个 SELECT 提供视图定义的语句。（从视图中选择实际上是使用 SELECT 语句 select_statement 进行选择。）可以从基表、其他视图中进行选择。从 MySQL 8.0.19 开始，SELECT 语句可以使用 VALUES 语句作为其源，也可以用 TABLE 语句替换，如 CREATE TABLE ... SELECT.

[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

MySQL :: MySQL 8.0 Reference Manual :: 27.5.4 The View WITH CHECK OPTION Clause

LOCAL关键字：若底部视图不带 CHECK OPTION，限制为只针对当前正在定义的视图的 WHERE 子句的约束，若带有 CHECK OPTION 选项，则会对其他视图内的 WHERE 子句进行检测。
CASCADED 还会导致对底层视图的检查进行评估，比如 v2 视图是根据 SELECT v1 视图生成的，那么 CASCADED 选项会在满足 v2 视图条件的同时，也需要满足 v1 视图创建时 WHERE 子句的条件。
无 with check option 时，是不需要满足当前视图的条件的。并且对于底部视图（即该视图的底部视图），先判断底部视图是否有with check option语句，有的话，按照其规则（local或者cascaded）处理，没有的话则不需要满足底部视图的条件。
当两个关键字都没有给出时（ WITH CHECK OPTION ），默认值为 cascade（ WITH CASCADED CHECK OPTION ）。

DEFINER 创建指定用户

CREATE TABLE t1(a INT , b VARCHAR(50)) PARTITION BY LIST columns(b)(
	PARTITION p0 VALUES IN ('abc\def','中国\人民'),
	PARTITION p1 VALUES IN ('hig\多多','klm \ \ ')
);
CREATE DEFINER = 'TEST_A1'@'%' VIEW t1_view AS SELECT * FROM t1;

基于临时数据

mysql> CREATE VIEW v AS SELECT 1 + 1 FROM DUAL;
Query OK, 0 rows affected (0.00 sec)

mysql> desc v;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| 1 + 1 | int  | NO   |     | 0       |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.01 sec)

基于 VALUES 语句

CREATE VIEW v AS VALUES ROW(1,2);
mysql> DESC v;
+----------+------+------+-----+---------+-------+
| Field    | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| column_0 | int  | NO   |     | 0       |       |
| column_1 | int  | NO   |     | 0       |       |
+----------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)

基于 TABLE 语句

CREATE TABLE test2(b int, c int);
INSERT INTO test2 VALUES(11,22);

mysql> CREATE VIEW v AS TABLE test2;
Query OK, 0 rows affected (0.01 sec)

mysql> desc v;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| b     | int  | YES  |     | NULL    |       |
| c     | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)


-- table + values 组合创建视图
mysql> CREATE VIEW v AS SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v2;
Query OK, 0 rows affected (0.01 sec)

mysql> desc v;
+----------+--------+------+-----+---------+-------+
| Field    | Type   | Null | Key | Default | Extra |
+----------+--------+------+-----+---------+-------+
| column_0 | bigint | NO   |     | 0       |       |
| column_1 | bigint | NO   |     | 0       |       |
| column_2 | bigint | NO   |     | 0       |       |
+----------+--------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> table v;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        3 |        5 |
|        2 |        4 |        6 |
+----------+----------+----------+
2 rows in set (0.01 sec)

基于系统内置函数

案例中包含自定义视图列名（column_list）
案例中使用了：时间函数，字符函数，转换函数，数学函数

mysql> CREATE VIEW v_today (today) AS SELECT CURRENT_DATE;
Query OK, 0 rows affected (0.00 sec)

mysql> table v_today;
+------------+
| today      |
+------------+
| 2022-06-07 |
+------------+
1 row in set (0.00 sec)

CREATE VIEW v_today AS SELECT CAST(0 AS DATE);
CREATE VIEW v_today AS SELECT CHARSET(char('' USING utf8mb4));
CREATE VIEW v_today AS SELECT ABS(-5.777);

基于运算符


mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

基于表 / 多个表

建立两张表，其中一张表做为约束关系

CREATE TABLE t1 (id1 int,name varchar(256),sex varchar(256));
CREATE TABLE t2 (id2 int,age int,date date);
CREATE VIEW v AS SELECT id1 as id, name, date, age, sex FROM t1 a, t2 b WHERE a.id1 = b.id2;

建立两张表，两张表均作为视图数据

CREATE TABLE t1 (id1 int,name varchar(256),sex varchar(256));
CREATE TABLE t2 (id2 int,age int,date date);
CREATE VIEW v AS SELECT * FROM t1, t2 ;

mysql> desc v;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id1   | int          | YES  |     | NULL    |       |
| name  | varchar(256) | YES  |     | NULL    |       |
| sex   | varchar(256) | YES  |     | NULL    |       |
| id2   | int          | YES  |     | NULL    |       |
| age   | int          | YES  |     | NULL    |       |
| date  | date         | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

基于其他视图

案例对 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); -- 正常

下一篇：MySQL视图（2）

Crett

关注

27
点赞
踩
26

收藏

觉得还不错? 一键收藏
1
评论
MySQL 视图（1）

当前视图没有 check option 约束时，可以更新当前视图数据，但是在当前视图中不显示该数据（参考情况1）。同时需要保证在该视图的 select_statement 中，如果对象是视图，并且也没有 check option 约束限制（参考情况2），有也没事，但是不能限制，才可以正常插入、更新数据（参考情况3）。本质上，视图是一个存储好的查询，用户通过访问这个视图来获取该视图定义的数据。解释：先把定义视图的语句执行起来，把数据放在一张系统定义的临时表，然后执行用户的sql语句。
复制链接

扫一扫