视图是表表达式的一种, 所以它也是虚拟表. 对视图操作的时候会通过语句动态的从表中临时获取数据.
1. 创建, 修改视图
- CREATE [OR REPLACE]
- [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
- VIEW [IF NOT EXISTS] view_name [(column_list)]
- AS select_statement
- [WITH [CASCADED | LOCAL] CHECK OPTION]
- ALTER
- [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
- VIEW view_name [(column_list)]
- AS select_statement
- [WITH [CASCADED | LOCAL] CHECK OPTION]
当使用 or replace 时, 如果视图存在则此语句相当于 alter view, 如果视图不存在, 则等价于 create view.
关于 algorithm, 后文详细说明.
with [local|cascaded] check option: 它的对象是可更新视图(即 merge 算法的视图). 对于可更新视图, 可给定 WITH CHECK OPTION 子句来防止插入或更新非法记录, 除非作用在行上的 select_statement 中的 WHERE 子句为 "true". 其中 local 表示只要满足本视图的筛选条件即可插入或更新, cascaded 表示必须满足所有视图的筛选条件才可插入或更新. 默认是 with cascaded check option.
例如, 下面的语句定义了 3 个视图, 其中后两个视图是以第一个视图作为基表创建的. 在向 view2 和 view3 插入记录的时候, 如果记录中字段 a=10: 由于 view2 默认使用的是 cascaded 选项, a=10 不满足 view1 的条件, 所以插入失败; 而 view3 使用的是 local 选项, 只需满足 view3 的条件即可, 所以 a=10 满足条件, 即可以成功插入.
- create view view1 as select * from t where a<10;
- create view view2 as select * from view1 where a>5;
- create view view3 as select * from view1 where a>5 with local check option;
MySQL/MariaDB 中视图创建后, 列的定义是 "已固化" 状态. 也就是说, 如果视图定义语句中的 select 语句中使用了星号 "*" 表示所有列, 在创建视图的时候会转化为对应的列名存储在视图定义语句中, 所以如果基表中新增了列将不会被视图的 SQL 语句检索到.
例如:
- create or replace view v_city
- as
- select * from world.city where id>200;
查看视图的定义语句: 可以看到, select 语句中的星号是替换为了对应的列名来表示的.
- mysql> mysql> show create view v_city\G
- *************************** 1. row ***************************
- View: v_city
- Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`192.168.100.%` SQL SECURITY DEFINER VIEW `v_city` AS select `city`.`ID` AS `ID`,`city`.`Name` AS `Name`,`city`.`CountryCode` AS `CountryCode`,`city`.`District` AS `District`,`city`.`Population` AS `Population` from `city` where (`city`.`ID`> 200)
- character_set_client: utf8
- collation_connection: utf8_general_ci
- 1 row in set (0.00 sec)
在 MySQL/MariaDB 中视图定义语句中的 select 部分中, from 后面不能是子查询. 在这一点上 MySQL/MariaDB 和其他类型的数据库有些不一样. 如果在某种条件下, 视图的定义语句 from 字句正好需要的是子查询, 可以将这个子查询先定义成视图, 再将视图放在 from 字句中. 更新视图时, 实际上是转到对应的基表上进行更新.
2. 视图算法 merge,temptable
algorithm={undefined|merge|temptable}是视图选择算法. 视图的算法会影响 MySQL/MariaDB 处理视图的方式:
1.merge 会将引用视图的语句与视图定义语句合并起来, 使得视图定义的某一部分取代语句的对应部分. 例如在引用视图时会将视图名替换成基表名, 将查询涉及的列替换成基表中的列名等.
2.temptable 将视图的结果放入临时表中, 然后使用该表的数据执行对应语句操作.
3.undefined 是让 MySQL/MariaDB 自己选择 merge 还是 temptable, 它更倾向于 merge. 这是未指定 algorithm 时的默认值.
例如, 以下是 merge 的一个特殊例子, 很能说明 merge 算法:
- MariaDB [test]> create or replace table t
- (id int auto_increment,
- name char(20),
- age int,
- primary key(id));
- MariaDB [test]> insert into t(name,age) values
- ('chenyi',21),
- ('huanger',22),
- ('zhangsan',23),
- ('lisi',24),
- ('wangwu',25),
- ('zhaoliu',26);
- MariaDB [test]> select * from t;
- +----+----------+------+
- | id | name | age |
- +----+----------+------+
- | 1 | chenyi | 21 |
- | 2 | huanger | 22 |
- | 3 | zhangsan | 23 |
- | 4 | lisi | 24 |
- | 5 | wangwu | 25 |
- | 6 | zhaoliu | 26 |
- +----+----------+------+
- # 创建一个 id<5 的视图 my_view
- MariaDB [test]> create or replace algorithm=merge view my_view(vf1,vf2) as
- select id,name from t where age<24;
- MariaDB [test]> select * from my_view;
- +-----+----------+
- | vf1 | vf2 |
- +-----+----------+
- | 1 | chenyi |
- | 2 | huanger |
- | 3 | zhangsan |
- +-----+----------+
返回的结果是 3 行记录.
由于是 merge 算法的视图, 在引用视图 (此处是查询操作) 的时候, 会将视图中的各项替换为基表 t 中的各项. 包括:
1."*" 号替换为 vf1 和 vf2, 它们又替换为 t 表中的 id 和 name.
2.from 子句中的 my_view 替换为表 t.
3. 加上视图定义语句中的 where 子句.
因此, select * from my_view; 在执行的时候, 会转换为下面的查询语句:
select id,name from t where age<24;
如果查询 my_view 的时候, 使用下面的语句:
- MariaDB [test]> select * from my_view where vf1<2;
- +-----+--------+
- | vf1 | vf2 |
- +-----+--------+
- | 1 | chenyi |
- +-----+--------+
在执行的时候, 该语句将替换为下面的语句:
select id,name from t where id<2 and age<24;
只有使用 merge 算法的时候, 视图才是可更新视图, 因为 temptable 算法操作的是填充到临时表中的数据, 无法结合基表进行数据更新.
因为 merge 算法结合了基表, 因此它有一些限制, 出现了以下情况时不能使用 merge 算法:
- HAVING
- LIMIT
- GROUP BY
- DISTINCT
- UNION
- UNION ALL
7. 使用了聚合函数, 如 MAX(), MIN(), SUM() or COUNT()
8. 在 select 列表中有子查询
9. 没有基表, 因为可能引用的是纯值, 例如 create view va as select 2.
之所以有以上限制, 是因为使用了它们之后, 视图的结构和基表的机构不一致, 无法和基表一一对应, 也就无法作为可更新视图.
3. 删除, 查看视图
可以一次性删除多个视图.
DROP VIEW [IF EXISTS] view_name [, view_name] ...
MySQL/MariaDB 中不存在 show view status 语句. 可以使用 show table status 表和视图的状态信息, 使用 show tables 显示出数据库中的表和视图.
SHOW TABLE STATUS LIKE 'v_city';
查看视图定义语句:
show create view view_name;
还可以从 information_schema.views 表中查看相关信息, 但是要注意的是, 在 views 表中视图名所在的字段称为 table_name 而不是 view_name. 如下:
select * from information_schema.views where table_name='view_name';
4. 检查无效视图
在创建视图的时候, 要求它的基表已存在, 否则会报错. 但是在视图创建成功后, 视图的基表可能会删除掉, 或者更新基表中的引用字段. 这时视图就已经是无效视图.
如何检测这些无效视图?
可以先在 information.schema 中查找出有哪些视图, 然后再使用 check table 语句检测.
例如:
check table my_view,my_view2
以下是无效视图检查结果:
- MariaDB [test]> check table my_view\G
- *************************** 1. row ***************************
- Table: test.my_view
- Op: check
- Msg_type: Error
- Msg_text: Table 'test.t' doesn't exist
- *************************** 2. row ***************************
- Table: test.my_view
- Op: check
- Msg_type: Error
Msg_text: View 'test.my_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
- *************************** 3. row ***************************
- Table: test.my_view
- Op: check
- Msg_type: error
- Msg_text: Corrupt
- 3 rows in set (0.000 sec)
来源: http://www.linuxidc.com/Linux/2018-04/151941.htm