create view 执行完,数据库里没有多出一份结果数据,只多了一条定义。视图(view)是一个虚拟表:它有列名、能被 select,内容由定义里的查询在每次访问时算出来。

一条 SELECT 被登记进数据字典

use scott;
 
create view v_ename_dname as
select e.ename, d.dname
from EMP e join DEPT d on e.deptno = d.deptno;
 
select * from v_ename_dname order by dname;

结果和直接写这条 join 一样。show create view 能看到保存下来的定义,以及 ALGORITHM、DEFINER、SQL SECURITY 三个属性:

show create view v_ename_dname\G

ALGORITHM 决定用 MERGE 还是 TEMPTABLE 执行,DEFINER 决定别人查这个视图时按谁的权限检查。改定义用 create or replace view 或 alter view,不要先 drop 再建,后者中间会有一段时间视图不存在:

create or replace view v_ename_dname as
select e.ename, d.dname
from EMP e join DEPT d on e.deptno = d.deptno;

视图不存数据,所以它没有索引、没有主键、没有默认值,也挂不了触发器。show tables 看不出表还是视图,show full tables from scott 的 Table_type 才标 BASE TABLE 或 VIEW。视图和表共享同一个命名空间,同一个库里不能有一张表和一个视图同名,也不能有两个同名视图。

删除视图只删定义:

drop view v_ename_dname;
select count(*) from EMP;      -- 一行都没少

反过来,基表改列名不会自动改视图,查询时才报 Unknown column。改表之前先查依赖。

改视图和改基表,方向不对称

课件写“视图的数据变化会影响到基表,基表的数据变化也会影响到视图”。后半句无条件成立,前半句只在视图可更新时成立。

update v_ename_dname set ename = 'TEST' where ename = 'CLARK';
select * from EMP where ename in ('CLARK', 'TEST');   -- 只剩 TEST

EMP 里的 CLARK 变成了 TEST,视图下次查询也跟着变。反方向改基表:

update EMP set deptno = 20 where ename = 'JAMES';
select * from v_ename_dname where ename = 'JAMES';   -- JAMES | RESEARCH

结果是 RESEARCH。课件这条 SQL 写的是 deptno = 10,贴出来的结果却是 RESEARCH。scott 的 DEPT 表里 10 是 ACCOUNTING,20 才是 RESEARCH,两者对不上;照 SQL 真跑,视图显示 ACCOUNTING。

可更新视图的条件,MySQL 手册列得很具体:视图的行和基表的行必须一一对应;定义里不能出现聚合函数、distinct、group by、having、union、窗口函数、select 列表里的子查询;不能使用 ALGORITHM=TEMPTABLE;列必须是简单引用,不能是 sal * 1.1 这种表达式。多表视图还要求是内连接,并且一次只能改其中一张表。

select table_name, is_updatable
from information_schema.views
where table_schema = 'scott';

建完查一眼 is_updatable,比背清单可靠。通过视图 insert 时,视图必须包含基表里所有没有默认值的列,否则 MySQL 不会补一个值。

with check option 管的是通过视图写进去的行

create view v_acct_emp as
select empno, ename, deptno
from EMP
where deptno = 10
with check option;
 
update v_acct_emp set deptno = 20 where ename = 'MILLER';
-- ERROR 1369 (HY000): CHECK OPTION failed 'scott.v_acct_emp'

视图定义只收 deptno = 10 的行。通过视图把一行改成 20,改完这行就不在视图的 where 条件里了,MySQL 直接拒绝。它约束的是通过这个视图发起的 insert 和 update;直接改基表,它管不着。默认是 CASCADED,嵌套视图会一路检查到底层的 where;with local check option 只检查当前这一层。

这个选项常和权限一起用:给外部账号一个筛选过的视图,再带上 check option,既限制他能看什么,也限制他能写什么。

视图、临时表、CTE

视图临时表CTE
存什么查询定义实际数据查询定义
生命周期持久,直到 drop当前会话,断开即删单条语句
能否建索引否可以否
是否占存储只占数据字典占临时表空间或内存不占
典型用途固定取数口径、权限隔离物化中间结果拆复杂查询

视图适合把稳定的取数规则固定下来,也适合做列级权限隔离:MySQL 没有列级隐藏,建一个只含公开列的视图、只授视图权限,就能挡住敏感列。默认 SQL SECURITY DEFINER,调用者查视图时按定义者的权限检查,所以定义者要对基表有 select,调用者只需要视图上的权限。

视图救不了慢查询。它不缓存结果,MERGE 视图会被展开成等价 SQL,TEMPTABLE 视图反而要先物化。要把结果变快,应该落到真表上建索引、定时刷新。

课件列的规则还有几条:视图名必须唯一;数量没有硬限制,但嵌套越多,展开和优化的成本越高;视图里可以写 order by,外层 select 也写 order by 时,视图里的那个会被覆盖;视图可以和表一起出现在查询里。