同乐学堂什么是视图
通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
视图的特性
视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；
可以跟基本表一样，进行增删改查操作(ps:增删改操作有条件限制)；
视图的作用
方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；
更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别；
使用场合
权限控制的时候，不希望用户访问表中某些含敏感信息的列，比如salary...
关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作；
视图实例1-创建视图及查询数据操作
现有三张表：用户(user)、课程(course)、用户课程中间表(user_course)，表结构及数据如下：
表定义：
-- ---------------------------- -- Table structure for `course` -- ----------------------------DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(200) NOT NULL, `description` varchar(500) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of course -- ----------------------------INSERT INTO `course` VALUES ('1', 'JAVA', 'JAVA课程'); INSERT INTO `course` VALUES ('2', 'C++', 'C++课程'); INSERT INTO `course` VALUES ('3', 'C语言', 'C语言课程'); -- ---------------------------- -- Table structure for `user` -- ----------------------------DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `account` varchar(255) NOT NULL, `name` varchar(255) NOT NULL, `address` varchar(255) DEFAULT NULL, `others` varchar(200) DEFAULT NULL, `others2` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of user -- ----------------------------INSERT INTO `user` VALUES ('1', 'user1', '小陈', '美国', '1', '1'); INSERT INTO `user` VALUES ('2', 'user2', '小张', '日本', '2', '2'); INSERT INTO `user` VALUES ('3', 'user3', '小王', '中国', '3', '3'); -- ---------------------------- -- Table structure for `user_course` -- ----------------------------DROP TABLE IF EXISTS `user_course`; CREATE TABLE `user_course` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `userid` bigint(20) NOT NULL, `courseid` bigint(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of user_course -- ----------------------------INSERT INTO `user_course` VALUES ('1', '1', '2'); INSERT INTO `user_course` VALUES ('2', '1', '3'); INSERT INTO `user_course` VALUES ('3', '2', '1'); INSERT INTO `user_course` VALUES ('4', '2', '2'); INSERT INTO `user_course` VALUES ('5', '2', '3'); INSERT INTO `user_course` VALUES ('6', '3', '2');
View Code
表数据：
这时，当我们想要查询小张上的所以课程相关信息的时候，需要这样写一条长长的SQL语句，如下：
SELECT `uc`.`id` AS `id`, `u`.`name` AS `username`, `c`.`name` AS `coursename` FROM `user` `u` LEFT JOIN `user_course` `uc` ON ((`u`.`id` = `uc`.`userid`)) LEFT JOIN `course` `c` ON ((`uc`.`courseid` = `c`.`id`)) WHERE u.`name` = '小张'
但是我们可以通过视图简化操作，例如我们创建视图view_user_course如下：
-- ---------------------------- -- View structure for `view_user_course` -- ----------------------------DROP VIEWIF EXISTS `view_user_course`; CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `view_user_course` AS ( SELECT `uc`.`id` AS `id`, `u`.`name` AS `username`, `c`.`name` AS `coursename` FROM ( ( `user` `u` LEFT JOIN `user_course` `uc` ON ((`u`.`id` = `uc`.`userid`)) ) LEFT JOIN `course` `c` ON ((`uc`.`courseid` = `c`.`id`)) ) );
几点说明（MySQL中的视图在标准SQL的基础之上做了扩展）：
ALGORITHM=UNDEFINED：指定视图的处理算法；
DEFINER=`root`@`localhost`：指定视图创建者；
SQL SECURITY DEFINER：指定视图查询数据时的安全验证方式；
创建好视图之后，我们可以直接用以下SQL语句在视图上查询小张上的所以课程相关信息，同样可以得到所需结果：
SELECT vuc.username, vuc.coursename FROM view_user_course vuc WHERE vuc.username = '小张'
视图实例2-增删改数据操作
继续，我们可以尝试在视图view_user_course上做增删改数据操作，如下：
update view_user_course set username='test',coursename='JAVASCRIPT' where id=3
遗憾的是操作失败，提示错误信息如下：
[SQL] update view_user_course set username='test',coursename='JAVASCRIPT' where id=3
[Err] 1393 - Can not modify more than one base table through a join view 'demo.view_user_course'
因为不能在一张由多张关联表连接而成的视图上做同时修改两张表的操作；
那么哪些操作可以在视图上进行呢？
视图与表是一对一关系情况：如果没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行增删改数据操作；
如我们创建用户关键信息视图view_user_keyinfo，如下：
-- ---------------------------- -- View structure for `view_user_keyinfo` -- ----------------------------DROP VIEWIF EXISTS `view_user_keyinfo`; CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `view_user_keyinfo` AS SELECT `u`.`id` AS `id`, `u`.`account` AS `account`, `u`.`name` AS `username` FROM `user` `u`;
进行增删改操作如下，操作成功（注意user表中的其它字段要允许为空，否则操作失败）：
INSERT INTO view_user_keyinfo (account, username) VALUES ('test1', 'test1');
DELETEFROM view_user_keyinfo WHERE username = 'test1';
UPDATE view_user_keyinfo SET username = 'updateuser'WHERE id = 1
视图与表是一对多关系情况：如果只修改一张表的数据，且没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行改数据操作，如以下语句，操作成功；
update view_user_course set coursename='JAVA' where id=1;
update view_user_course set username='test2' where id=3;
以下操作失败：
delete from view_user_course where id=3;
insert into view_user_course(username, coursename) VALUES('2','3');
视图总结
视图定义要服从下述限制：
· SELECT语句不能包含FROM子句中的子查询。 · SELECT语句不能引用系统或用户变量。 · SELECT语句不能引用预处理语句参数。 ·在存储子程序内，定义不能引用子程序参数或局部变量。 ·在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。 ·在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。 ·在视图定义中命名的表必须已存在。 ·不能将触发程序与视图关联在一起。 在视图定义中允许使用ORDER BY，但是，如果从特定视图进行了选择，而该视图使用了具有自己ORDER BY的语句，它将被忽
视图从表象上看根表差不多，但是毕尽它不是表，对他的使用有什么限制呢？
1，mysql的视图名不能和现有表名重复
mysql> show tables; //查看表 +------------------+ | Tables_in_uchome | +------------------+ | comment | | user | +------------------+ 2 rows in set (0.00 sec) mysql> create view user as select * from user; //视图名和存在表重名，报错 ERROR 1050 (42S01): Table 'user' already exists mysql> create view v_user as select * from user; //创建视图 Query OK, 0 rows affected (0.00 sec) mysql> show tables; //查看表，包涵了视图 +------------------+ | Tables_in_uchome | +------------------+ | comment | | user | | v_user | +------------------+ 3 rows in set (0.00 sec) mysql> check table v_user; //查看一下视图， +---------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+-------+----------+----------+ | test_1.v_user | check | status | OK | +---------------+-------+----------+----------+ 1 row in set (0.00 sec)
从上面的一些操作，我们可以看出，其实mysql有的时候，已经把视图当成一种虚拟表来使用了，既然是一种虚拟表，表名当然是不能重复的了。
2，视图所对应的表，不能是临时表
//创建临时表 tmp_user mysql> CREATE temporary TABLE IF NOT EXISTS `tmp_user` ( -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID', -> `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', -> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女', -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; Query OK, 0 rows affected (0.04 sec) mysql> desc tmp_user; //查看临时表，用show tables;看不到的。用check table也可以看到 +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | | | | sex | int(1) | NO | | 0 | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> create view v_test as select * from tmp_user; //用临时表会报错，看下的错 ERROR 1352 (HY000): View's SELECT refers to a temporary table 'tmp_user'
3，创建视图时不能使用系统或用户变量
mysql> set @test="2"; //定义一个用户变量 Query OK, 0 rows affected (0.00 sec) mysql> create view vv_test as select * from aa where id=@test; //创建视图 ERROR 1351 (HY000): View's SELECT contains a variable or parameter //报sql中有变量，错误 mysql> select * from aa where id=@test; //真正的表是可以使用的 +----+------+------------+------+ | id | name | nname | sex | +----+------+------------+------+ | 2 | d | bbbb,4bbbb | NULL | +----+------+------------+------+ 1 row in set (0.00 sec)
4，不能使用预处理语句参数，存储过程中的参数或局部变量
mysql> prepare p_test from "select * from user"; //产生一个预处理变量 Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> create view v_test as execute p_test; //使用预处理变量报错 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'execute p_test' at line 1 mysql> create view v_test as p_test; //这样也不行 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'p_test' at line 1 mysql> execute p_test; //单独是没问题的 +----+--------+-----+ | id | name | sex | +----+--------+-----+ | 1 | zhangy | 0 | | 3 | tank | 0 | | 4 | tank | 0 | +----+--------+-----+ 3 rows in set (0.00 sec)
存储过程中产生的参数，或者是局量也不行，大家可以试一下。
5，如果预处理语句调用了视图，视图就不能变了。
mysql> create view aa_test as select * from comment; //创建一个视图 Query OK, 0 rows affected (0.26 sec) mysql> prepare test22 from "select * from aa_test"; //预处理语句使用了这个视图 Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> execute test22; //调用一下预处理语句 +------+------+--------+---------+ | c_id | u_id | name | content | +------+------+--------+---------+ | 1 | 1 | zhangy | test | | 2 | 1 | zhangy | test2 | +------+------+--------+---------+ 2 rows in set (0.00 sec) mysql> alter view aa_test as select * from user; //修改视图,把基础表改成user Query OK, 0 rows affected (0.00 sec) mysql> execute test22; //在调用一下预处理语句，内容没有变 +------+------+--------+---------+ | c_id | u_id | name | content | +------+------+--------+---------+ | 1 | 1 | zhangy | test | | 2 | 1 | zhangy | test2 | +------+------+--------+---------+ 2 rows in set (0.00 sec)
6，在存储过程中不能修改视图
mysql> create procedure test3() -> begin -> select * from aa_test; -> alter view aa_test as "select * from comment"; -> select * from aa_test; -> end;| ERROR 1314 (0A000): ALTER VIEW is not allowed in stored procedures //会报错的
为什么是mysql手册里面，我看到可以在存储过程中修改视图的，为什么我用的mysql就不行呢？是不是mysql版本的问题。我用的是Server version: 5.1.26-rc-log Source distribution
7，不能给视图添加索引
mysql> create index aa_index on aa_test (c_id); ERROR 1347 (HY000): 'test.aa_test' is not BASE TABLE //添加索引会报错的
视图根本不是基本的表，在存放数据的文件夹中，他只有一个结构文件，没有.MYD,.MYI文件，如果能增加索引存放到什么地方。
8，视图插入，添加，删除的限制
对于有些视图是可以UPDATE、DELETE或INSERT等操作的，以达到修改基本表的内容。对于可更新的视图，它必须和基本表是一一对应关系。如果视图中包括以下的东西就不是一一对应关系了。就不能进行更新操作。
//下面的视图根基本表user是一一对应关系，可以进行更新操作 mysql> create view v_user as select * from user; Query OK, 0 rows affected (0.00 sec) //视图对应二个基本表，视图中的内容，不和任何一张表一一对应，不能进行更新操作 mysql> create view tall as select a.id,a.name,b.content from user a left join comment b on a.id=b.u_id where b.content != 'null'; Query OK, 0 rows affected (0.00 sec)
a），聚合函数（SUM(), MIN(), MAX(), COUNT()等）。
b），DISTINCT
c），GROUP BY
d），HAVING
e），UNION或UNION ALL
f），位于选择列表中的子查询
g），Join
h），FROM子句中的不可更新视图
i），WHEHE子句中的子查询，引用FROM子句中的表。
m），仅引用文字值（在该情况下，没有要更新的基本表）。
n），ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。
o），关于可插入性（可用INSERT语句更新），如果它也满足关于视图列的下述额外要求，可更新的视图也是可插入的：
p），不得有重复的视图列名称。
q），视图必须包含没有默认值的基表中的所有列。
r），视图列必须是简单的列引用而不是导出列。
上面a－r的这几种情况，其实就是一种情况，规则就是，视图的数据根基本表的数据不一样了。
视图算法概述
可选的ALGORITHM子句是对标准SQL的MySQL扩展。ALGORITHM可取三个值：MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。算法会影响MySQL处理视图的方式。 对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。 对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。 对于UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。 明确选择TEMPTABLE的1个原因在于，创建临时表之后、并在完成语句处理之前，能够释放基表上的锁定。与MERGE算法相比，锁定释放的速度更快，这样，使用视图的其他客户端不会被屏蔽过长时间。 视图算法可以是UNDEFINED，有三种方式： ·在CREATE VIEW语句中没有ALGORITHM子句。 · CREATE VIEW语句有1个显式ALGORITHM = UNDEFINED子句。 · 为仅能用临时表处理的视图指定ALGORITHM = MERGE。在这种情况下，MySQL将生成告警，并将算法设置为UNDEFINED。
如果视图中包含GROUY BY , DISTINCT 、任何聚合函数、UNION、子查询等，只要无法在原表记录和视图记录中建立一 一 映射的场景中，MySQL都将使用临时表算法来实现视图！
MySQL实现视图有两种算法，合并算法、临时表算法。根据MySQL高性能的作者建议尽量使用合并算法。避免使用临时表算法！
如果想确定哪种算法可以使用EXPLAIN 针对视图简单的查询。
为什么大多数人不推荐使用视图，解释如下：
1、大多数人人为定义视图，操作视图很简单，但是实际上MySQL的视图背后的实现逻辑是非常复杂，且不可控！
一条看起来很简单的视图查询，EXPLAIN出来的却有几百行到几千行，那是何等恶心~。实际上是因为引用了很多的表和其它表的视图！
2、如果要非得要打算用视图来提升性能，需要做详细的比较测试！即使合并算法也会产生大量开销，并且视图的性能很难预测！
3、MySQL的视图现阶段并不是很成熟，因为复杂的视图在高并发的查询下，导致查询优化器花大量的时间在执行计划生成统计数据阶段，甚至会导致MySQL服务器僵死！
来源：MySQL高性能3等一些互联网博客
微信扫一扫，打赏作者吧～
