MySQL视图

1.视图介绍

1.1 什么是视图

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

1.2 视图有什么优势？

因为视图的诸多优点，如下

1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

2.创建视图

2.1 创建视图语句介绍

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]

解释：
CREATE 创建视图
OR REPLACE 如果该视图存在，则覆盖该视图，但是该用户必须要有drop权限
ALGORITHM 影响 MySQL 处理视图的方式，默认是UNDEFINED
WITH CHECK OPTION 可以给出该子句来限制对视图引用的表中的行的插入或更新
select_statement 是 SELECT，它提供了视图的定义语句。
DEFINER = user 定义者，如果不指定就是创建该视图的用户
SQL SECURITY DEFINER | INVOKER （定义者/调用者）

3.操作解释

3.1 create

mysql> create view gj_view  AS select SUM(countrylanguage.Percentage) from city  ,countrylanguage where City.CountryCode=.countrylanguage.CountryCode limit 10;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
#正常create一个view，不指定任何参数。
mysql> show create view gj_view\G
*************************** 1. row ***************************
                View: gj_view
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `gj_view` AS select sum(`countrylanguage`.`Percentage`) AS `SUM(countrylanguage.Percentage)` from (`city` join `countrylanguage`) where (`city`.`CountryCode` = `countrylanguage`.`CountryCode`) limit 10
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

mysql> 
#我们可以看下这些参数的默认值
ALGORITHM=UNDEFINED 
DEFINER=`root`@`localhost` (创建用户)
SQL SECURITY DEFINER

3.2 OR REPLACE

如果视图不存在， CREATE OR REPLACE VIEW则与CREATE VIEW效果一样。如果视图确实存在，则 CREATE OR REPLACE VIEW替换它。

mysql> CREATE OR REPLACE view gj_view AS select sum(`countrylanguage`.`Percentage`) AS `SUM(countrylanguage.Percentage)` from (`city` join `countrylanguage`) where (`city`.`CountryCode` = `countrylanguage`.`CountryCode`) limit 10;
Query OK, 0 rows affected (0.00 sec)

mysql> 

3.3 ALGORITHM

3.3.1 算法介绍

CREATE VIEW 或 ALTER VIEW 的可选 ALGORITHM 子句是标准 SQL 的 MySQL 扩展。它会影响 MySQL 处理视图的方式。
ALGORITHM 采用三个值：MERGE、TEMPTABLE 或 UNDEFINED。

1. 对于 MERGE，引用视图和视图定义的语句的文本被合并，以便视图定义的部分替换语句的相应部分。
2. 对于 TEMPTABLE，视图的结果被检索到临时表中，然后用于执行语句。
3. 对于 UNDEFINED，MySQL 选择要使用的算法。如果可能，它更喜欢 MERGE 而不是 TEMPTABLE，因为 MERGE 通常更有效，并且如果使用临时表，视图将无法更新。

3.3.2 MERGE算法

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

通俗的说，意思就是MERGE算法是一个合并算法，每当执行的时候,先将视图的sql语句与外部查询视图的sql语句,合并在一起,最终执行；这样操作对效率基本上没有什么影响，但是使用这种算法有一定限制

#创建视图
mysql> create  OR REPLACE ALGORITHM=MERGE view sbtest.gj_view as select * from sbtest.sbtest1 ;
Query OK, 0 rows affected (0.00 sec)
#使用基表查询
mysql> select pad from sbtest1 where  k = 161864 or id in ('23','34','56'); 
+-------------------------------------------------------------+
| pad                                                         |
+-------------------------------------------------------------+
| 65545788631-71943997167-28841433297-57514893583-54512179345 |
| 40142567337-05281035179-76543613200-97526675950-21617940594 |
| 71130978898-14049454643-07135898519-22200484901-58406552366 |
| 67588215073-82235908987-27353900394-39582386455-65915490114 |
+-------------------------------------------------------------+
4 rows in set (0.96 sec)
#使用视图查询
mysql> select pad from gj_view where  k = 161864 or id in ('23','34','56');       
+-------------------------------------------------------------+
| pad                                                         |
+-------------------------------------------------------------+
| 65545788631-71943997167-28841433297-57514893583-54512179345 |
| 40142567337-05281035179-76543613200-97526675950-21617940594 |
| 71130978898-14049454643-07135898519-22200484901-58406552366 |
| 67588215073-82235908987-27353900394-39582386455-65915490114 |
+-------------------------------------------------------------+
4 rows in set (0.98 sec)

mysql> 

结论：MERGE算法对效率的影响很小。

3.3.3 TEMPTABLE算法

对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。

TEMPTABLE算法是将结果放置到临时表中，意味这要mysql要先创建好一个临时表，然后将结果放到临时表中去，然后再使用这个临时表进行相应的查询。为什么文档中说“果使用了临时表，视图是不可更新的。

#为了更好模拟现象，我们使用sysbench压测的表，并且删掉索引
mysql> show create table sbtest1;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                           |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest1 | CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table sbtest1 drop index k_1; 
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
#创建视图
mysql> create  OR REPLACE ALGORITHM=TEMPTABLE view sbtest.gj2_view as select * from sbtest.sbtest1 ;
Query OK, 0 rows affected (0.00 sec)

mysql> 
#查询视图
mysql> select pad from gj_view where  k = 161864 or id in ('23','34','56');         
+-------------------------------------------------------------+
| pad                                                         |
+-------------------------------------------------------------+
| 65545788631-71943997167-28841433297-57514893583-54512179345 |
| 40142567337-05281035179-76543613200-97526675950-21617940594 |
| 71130978898-14049454643-07135898519-22200484901-58406552366 |
| 67588215073-82235908987-27353900394-39582386455-65915490114 |
+-------------------------------------------------------------+
4 rows in set (2.02 sec)
#查询基表
mysql> select pad from sbtest1 where  k = 161864 or id in ('23','34','56');         
+-------------------------------------------------------------+
| pad                                                         |
+-------------------------------------------------------------+
| 65545788631-71943997167-28841433297-57514893583-54512179345 |
| 40142567337-05281035179-76543613200-97526675950-21617940594 |
| 71130978898-14049454643-07135898519-22200484901-58406552366 |
| 67588215073-82235908987-27353900394-39582386455-65915490114 |
+-------------------------------------------------------------+
4 rows in set (1.08 sec)

mysql>

结论：该模式比较影响性能，结果集越大就会越慢。

3.3.4 UNDEFINED算法

这个就一句话了，如果不选择ALGORITHM，则默认是UNDEFINED，而UNDEFINED优先选择MERGE。这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

3.4 DEFINER(定义者)

definer 翻译成中文是“定义者”的意思。MySQL中，创建视图(view)、函数(function)、存储过程(procedure)、触发器(trigger)、事件(event)时，都可以指定 DEFINER = user 选项，即指定此对象的定义者是谁，若不显式指定，则创建此对象的用户就是定义者。

DEFINER=`testuser`@`%`

3.5 SQL SECURITY

DEFINER | INVOKER （定义者/调用者）

定义者(Definer)-指的是编译存储对象的拥有者，包括包、存储过程、函数、触发器和视图。

调用者(Invoker)-指当前会话中生效的schema，不一定就是当前登录的用户。

1.创建视图定义者用户并授权
create user testuser@'%';
GRANT USAGE ON *.* TO 'testuser'@'%' identified  by '123';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE VIEW, SHOW VIEW ON `world`.* TO 'testuser'@'%';

2.创建视图
# SQL SECURITY= DEFINER
CREATE ALGORITHM=UNDEFINED DEFINER=`testuser`@`%` SQL SECURITY DEFINER VIEW `view_definer` AS select k.Name as Na,k.Population as Po,c.CountryCode as Cou,c.Language as Lan,c.Percentage as Per from city as k inner join countrylanguage as c  on k.CountryCode=c.CountryCode  group by k.Name order by k.Population limit 100;


#SQL SECURITY INVOKER
CREATE ALGORITHM=UNDEFINED DEFINER=`testuser`@`%` SQL SECURITY INVOKER VIEW `view_invoker` AS select k.Name as Na,k.Population as Po,c.CountryCode as Cou,c.Language as Lan,c.Percentage as Per from city as k inner join countrylanguage as c  on k.CountryCode=c.CountryCode  group by k.Name order by k.Population limit 100;

3.创建并授权查看这两个视图的用户
create user gengjin@'%' ; 
GRANT USAGE ON *.* TO 'gengjin'@'%';
GRANT SELECT ON `world`.`view_invoker` TO 'gengjin'@'%'; 
GRANT SELECT ON `world`.`view_definer` TO 'gengjin'@'%' identified  by '123';

4.登陆该用户并且查看
 /data/mysql/base/5.7.30/bin/mysql -p -S /data/mysql/data/3306/mysqld.sock -ugengjin
 
 mysql> select user();
+-------------------+
| user()            |
+-------------------+
| gengjin@localhost |
+-------------------+
1 row in set (0.00 sec)

mysql> 

 mysql> select * from view_definer limit 10;
+-------------------+-----+-----+-------------+-----+
| Na                | Po  | Cou | Lan         | Per |
+-------------------+-----+-----+-------------+-----+
| Adamstown         |  42 | PCN | Pitcairnese | 0.0 |
| West Island       | 167 | CCK | English     | 0.0 |
| Fakaofo           | 300 | TKL | English     | 0.0 |
| Citt del Vaticano | 455 | VAT | Italian     | 0.0 |
| Bantam            | 503 | CCK | English     | 0.0 |
| Yaren             | 559 | NRU | Chinese     | 8.5 |
| The Valley        | 595 | AIA | English     | 0.0 |
| Alofi             | 682 | NIU | English     | 0.0 |
| Flying Fish Cove  | 700 | CXR | Chinese     | 0.0 |
| South Hill        | 961 | AIA | English     | 0.0 |
+-------------------+-----+-----+-------------+-----+
10 rows in set (0.02 sec)

mysql> select * from view_invoker limit 10;
ERROR 1143 (42000): SELECT command denied to user 'gengjin'@'localhost' for column 'CountryCode' in table 'city'
mysql> 

结论：对于视图、函数及存储过程，还可以指定 SQL SECURITY 属性，其值可以为 DEFINER（定义者） 或 INVOKER（调用者），表示在执行过程中，使用谁的权限来执行。DEFINER 表示按定义者拥有的权限来执行，INVOKER 表示用调用者的权限来执行。

默认情况下，SQL SECURITY 属性为 DEFINER 。其值为 DEFINER 时，数据库中必须存在 DEFINER 指定的定义者用户，并且该定义者用户拥有对应的操作权限及引用的相关对象的权限，执行者只需拥有调用权限就能成功执行。当 SQL SECURITY 属性为 INVOKER 时，则需要执行者有调用权限并且有引用的相关对象的权限，才能成功执行。

3.6 WITH CHECK OPTION

要避免将某个视图的某一行更新为使其不再满足视图的条件，请在创建视图时添加 WITH CHECK OPTION 关键字。该子句要求数据库服务器对插入或更新的每一行进行测试，以确保其符合视图的 WHERE 子句设置的条件。如果不符合条件，那么数据库服务器将拒绝该操作，并显示错误。

当视图定义中包含 UNION 运算符时，不能包括 WITH CHECK OPTION 关键字。

mysql> create  OR REPLACE ALGORITHM=MERGE view sbtest.gj_view as select * from sbtest.sbtest1 where id < 500 with check option ;
Query OK, 0 rows affected (0.01 sec)

mysql> select id ,pad from gj_view limit 6;
+----+-------------------------------------------------------------+
| id | pad                                                         |
+----+-------------------------------------------------------------+
|  1 | 47328242323-08400347702-59924112291-23393276689-85999736088 |
|  2 | 36116300692-38522419200-59052290502-56511681674-29972993336 |
|  3 | 94468594903-21083565691-45440159231-24309307339-48235999207 |
|  4 | 23419257792-77795894307-10252813405-33667485656-51057104396 |
|  5 | 56434851533-23000686113-06149845034-42970841097-26636077131 |
|  6 | 73974447854-72753416473-53618577063-72253384978-88085037150 |
+----+-------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql> update gj_view set id=501 where pad = '56434851533-23000686113-06149845034-42970841097-26636077131';  
ERROR 1369 (HY000): CHECK OPTION failed 'sbtest.gj_view'
mysql> 

嵌套视图：

定义在另一个视图的上面的视图

mysql> create view gj_view
    -> as
    -> select * from sbtest1
　　 -> where id < 1000;

使用WITH CHECK OPTION约束时，(不指定选项则默认是CASCADED)

可以使用CASCADED或者 LOCAL选项指定检查的程度：

①WITH CASCADED CHECK OPTION：检查所有的视图

例如：嵌套视图及其底层的视图

②WITH LOCAL CHECK OPTION：只检查将要更新的视图本身

对嵌套视图不检查其底层的视图