在MySQL中,视图是一张虚拟表,但是它本身并不存储任何数据。当使用 SQL语句访问视图时,它每次返回的数据是从其他表中临时生成的。在大多数情况下,MySQL中视图和普通表是被同等对待的,不过,视图和表还是有差别,比如视图不能创建触发器,也不能建立索引等。
一、创建视图
CREATE [OR REPLACE] [ALGORITHM = UNDEFINED | MERGE | TEMPTABLE ] VIEW v_name AS 查询语句 [WITH CHECK OPTION] ;
这里的查询语句可以是任意的,因此,视图又分为单表视图(即数据来自于一个表)和多表视图(即数据来自多个表)。
视图一旦被创建,就会生成一个结构文件 .frm。
创建视图的两种算法
视图的实现可以使用两种算法,第一种是 merge算法,第二种是 temptable算法。
1.merge(合并算法)
当用户发起对视图的查询时,服务器将视图 SQL与外部查询 SQL 合并,然后执行并返回结果给客户端。这种算法实现的视图查询效率较高,但是视图SQL中如果包含 GROUP BY、DISTINCT、任何聚合函数、UNION、子查询等时,也就是说如果不能在原表记录视图记录中建立一一映射的场景中,MySQL都会强制使用临时表算法来实现视图。
2.temptable(临时表算法)
当用户发起对视图的查询时,都将执行视图的 SQL语句来形成一个临时表,再用用户的SQL去临时表中查询相关数据。
执行:
CREATE VIEW V1 AS (SELECT * FROM TABLE1 ORDER BY 字段名1);
SELECT * FROM V1 WHERE 条件 ORDER BY 字段名2;
相当于执行:
SELECT * FROM ( SELECT * FROM TABLE1 ORDER BY 字段名1 ) AS TEMP WHERE 条件 ORDER BY 字段名2;
默认情况下,MySQL 会在这两种算法中选择一个。
二、查
从视图中获取数据时就把它当作一个普通的表就可以了,因此无论是普通查询还是复杂查询,视图都可以胜任。
如果想查看视图结构,SHOW TABLES [LIKE] ‘xxx’、DESC tbl_name、SHOW CREATE TABLE tbl_name、SHOW CREATE VIEW v_name 都是可以的。
三、增删改
MySQL中可以通过视图来影响视图的基表,包括更新、删除、增加数据。
下列情况下,对视图数据的增删改将失败:
1、新增数据时,由于视图中没有的字段在基表中将为NULL,因此,若该字段为 NOT NULL并且没有设置默认值,对视图的新增就会失败。
2、如果视图SQL中包含了 GROUP BY、UNION、聚合函数等,对视图的增删改将失败。
3、如果视图SQL为关联语句,那么对视图的增改所涉及到的列必须来自于同一个表中,否则失败。若来自于一张表,对视图的操作也只会影响到这一张表。注意,不能对关联视图数据进行删除。
4、创建视图时,如果加了 WITH CHECK OPTION ,那么对于视图的增改,必须使新得到的视图仍然满足创建视图时的 WHERE条件。否则操作将失败。
对于视图的管理
删除视图:
DROP VIEW [IF EXISTS] v_name;
修改视图结构:
ALTER VIEW v_name [(字段名1,字段名2,…)] AS (查询语句)
四、使用视图的好处
使用视图可以简化开发,将一些复杂的SQL语句保存为视图,之后的操作都可以对视图进行。并且,视图可以实现基于列的权限控制,对外选择性地提供数据,而不用去真正地在系统中创建列权限。