Mysql视图初步理解

一、基本概念

《高性能mysql》中定义：mysql5.0版本之后开始引入视图。视图本身是一个虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，它返回的数据是mysql从其他表中生成。视图和表是在同一个命名空间，mysql在很多地方对于视图和表是同样对待的。
不过视图和表也有不同，例如，不能对视图创建触发器，也不能使用drop table命令删除视图。

二、视图的优缺点

优点：

• 简单：使用视图的用户完全不需要关心所对应的表的结构、关系等，直接使用即可。
• 安全：可限制某些用户看到的一些关键信息和字段（登录密码、工资金额等）。
• 数据独立：基表更改了结构后，不会对视图造成影响。

缺点：

• 查询性能差：查询性较慢，无法使用索引。
• 修改复杂：基表改变与其它表关联的时候，也要修改视图结构。

三、视图的三种算法

• UNDEFINED：默认算法。UNDEFINED算法使MySQL可以选择使用MERGE或TEMPTABLE算法。MySQL优先使用MERGE算法进行TEMPTABLE算法，因为MERGE算法效率更高。

• MERGE：MySQL首先将输入查询与定义视图的SELECT语句组合成单个查询。 然后MySQL执行组合查询返回结果集。 如果SELECT语句包含集合函数(如MIN，MAX，SUM，COUNT，AVG等)或DISTINCT，GROUP BY，HAVING，LIMIT，UNION，UNION ALL，子查询，则不允许使用MERGE算法。 如果SELECT语句无引用表，则也不允许使用MERGE算法。 如果不允许MERGE算法，MySQL将算法更改为UNDEFINED。请注意，将视图定义中的输入查询和查询组合成一个查询称为视图分辨率。

• TEMPTABLE：MySQL首先根据定义视图的SELECT语句创建一个临时表，然后针对该临时表执行输入查询。因为MySQL必须创建临时表来存储结果集并将数据从基表移动到临时表，所以TEMPTABLE算法的效率比MERGE算法效率低。 另外，使用TEMPTABLE算法的视图是不可更新的。

四、视图的基本操作

先查看所有的表：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| news           |
| player         |
| student        |
+----------------+
3 rows in set (0.00 sec)

下面以player表进行测试，先看下player表中的数据：

mysql> select * from player;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 易建联      |  33 |
|  2 | 刘翔       |  34 |
|  3 | 姚明      |  32 |
+----+--------+-----+
3 rows in set (0.00 sec)

4.1 创建视图

创建一个名为player_view视图：

mysql> create view player_view as select name from player;
Query OK, 0 rows affected (0.00 sec)

再次查看所有的表：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| news           |
| player         |
| player_view    |
| student        |
+----------------+
4 rows in set (0.00 sec)

查询一下player_view视图中的数据：

mysql> select * from player_view;
+--------+
| name   |
+--------+
| 易建联      |
| 刘翔       |
| 姚明      |
+--------+
3 rows in set (0.00 sec)

更新一下基表（player）中的某一条数据：

mysql> update player set name="丁彦雨航" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

再次查询player_view视图中的数据是否有变化：

mysql> select * from player_view;
+----------+
| name     |
+----------+
| 丁彦雨航       |
| 刘翔         |
| 姚明        |
+----------+
3 rows in set (0.00 sec)

修改一下视图中的数据：

mysql> update player_view set name="勒布朗" where name="丁彦雨航";
Query OK, 1 row affected (0.00 sec)

再看下基表中的变化：

mysql> select * from player;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 勒布朗      |  33 |
|  2 | 刘翔       |  34 |
|  3 | 姚明      |  32 |
+----+--------+-----+
3 rows in set (0.00 sec)

4.2 查看视图

查看所有视图：

mysql> show table status where comment='view';
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| player_view | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set (0.00 sec)

查看视图结构：

mysql> DESCRIBE player_view;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

查看视图创建情况：

mysql> show create view player_view;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View        | Create View                                                                                                                                                           | character_set_client | collation_connection |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| player_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `player_view` AS select `player`.`id` AS `id`,`player`.`name` AS `name` from `player` | gb2312               | gb2312_chinese_ci    |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

4.3 修改视图

先看下视图中的数据：

mysql> select * from player_view;
+--------+
| name   |
+--------+
| 勒布朗      |
| 刘翔       |
| 姚明      |
+--------+
3 rows in set (0.00 sec)

修改一下视图中的结构：

mysql> alter view player_view as select id,name from player;
Query OK, 0 rows affected (0.00 sec)

在查看一下视图中的数据：

mysql> select * from player_view;
+----+--------+
| id | name   |
+----+--------+
|  1 | 勒布朗      |
|  2 | 刘翔       |
|  3 | 姚明      |
+----+--------+
3 rows in set (0.00 sec)

4.4 删除视图

mysql> drop view player_view;
Query OK, 0 rows affected (0.01 sec)

五、使用视图注意事项（列出一部分）

• select语句不能包含from语句中的子查询。
• select语句不能引用系统或用户变量。
• select语句不能引用预处理语句参数。
• 不能给视图添加索引。
• 不能将出发程序与视图关联在一起。
• 不得有重复的视图列名称。
• algorithm=temptable会成为不可更新的。

六、总结

最近做的是医院的项目，我需要给医院那边使用视图提供一些数据，例如，患者姓名、卡号、排队实况等信息。

给他们设置权限，只能查询，不能删除添加和修改。

视图在实际应用中很方便，但是在某种程度上也能进行优化。

本篇为基础讲解，仅此记录。

参考文章：《高性能mysql》、《PHP核心技术与最佳实践》、创建mysql视图。

本文地址：https://blog.csdn.net/qq_42249896/article/details/107604966