MySQL视图的限制，以及实例

kider

作者:海底苍鹰
地址:http://blog.51yip.com/mysql/1062.html

视图从表象上看根表差不多，但是毕尽它不是表，对他的使用有什么限制呢？

1，mysql的视图名不能和现有表名重复

1. mysql> show tables;     //查看表  
   
2. +------------------+  
   
3. | Tables_in_uchome |  
   
4. +------------------+  
   
5. | comment          |  
   
6. | user             |  
   
7. +------------------+  
   
8. 2 rows in set (0.00 sec)  
   
9.   
   
10. mysql> create view user as select * from user;        //视图名和存在表重名，报错  
    
11. ERROR 1050 (42S01): Table 'user' already exists  
    
12. mysql> create view v_user as select * from user;     //创建视图  
    
13. Query OK, 0 rows affected (0.00 sec)  
    
14.   
    
15. mysql> show tables;    //查看表，包涵了视图  
    
16. +------------------+  
    
17. | Tables_in_uchome |  
    
18. +------------------+  
    
19. | comment          |  
    
20. | user             |  
    
21. | v_user           |  
    
22. +------------------+  
    
23. 3 rows in set (0.00 sec)  
    
24.   
    
25. mysql> check table v_user;     //查看一下视图，  
    
26. +---------------+-------+----------+----------+  
    
27. | Table         | Op    | Msg_type | Msg_text |  
    
28. +---------------+-------+----------+----------+  
    
29. | test_1.v_user | check | status   | OK       |  
    
30. +---------------+-------+----------+----------+  
    
31. 1 row in set (0.00 sec)  

复制代码

从上面的一些操作，我们可以看出，其实mysql有的时候，已经把视图当成一种虚拟表来使用了，既然是一种虚拟表，表名当然是不能重复的了。

2，视图所对应的表，不能是临时表

1. //创建临时表 tmp_user  
   
2. mysql> CREATE temporary TABLE IF NOT EXISTS `tmp_user` (  
   
3. ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  
   
4. ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',  
   
5. ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',  
   
6. ->   PRIMARY KEY (`id`)  
   
7. -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  
   
8. Query OK, 0 rows affected (0.04 sec)  
   
9.   
   
10. mysql> desc tmp_user;   //查看临时表，用show tables;看不到的。用check table也可以看到  
    
11. +-------+-------------+------+-----+---------+----------------+  
    
12. | Field | Type        | Null | Key | Default | Extra          |  
    
13. +-------+-------------+------+-----+---------+----------------+  
    
14. | id    | int(11)     | NO   | PRI | NULL    | auto_increment |  
    
15. | name  | varchar(50) | NO   |     |         |                |  
    
16. | sex   | int(1)      | NO   |     | 0       |                |  
    
17. +-------+-------------+------+-----+---------+----------------+  
    
18. 3 rows in set (0.00 sec)  
    
19.   
    
20. mysql> create view v_test as select * from tmp_user;      //用临时表会报错，看下的错  
    
21. ERROR 1352 (HY000): View's SELECT refers to a temporary table 'tmp_user'

复制代码

3，创建视图时不能使用系统或用户变量

1. mysql> set @test="2";   //定义一个用户变量  
   
2. Query OK, 0 rows affected (0.00 sec)  
   
3.   
   
4. mysql> create view vv_test as select * from aa where id=@test;   //创建视图  
   
5. ERROR 1351 (HY000): View's SELECT contains a variable or parameter   //报sql中有变量，错误  
   
6. mysql> select * from aa where id=@test;  //真正的表是可以使用的  
   
7. +----+------+------------+------+  
   
8. | id | name | nname      | sex  |  
   
9. +----+------+------------+------+  
   
10. |  2 | d    | bbbb,4bbbb | NULL |  
    
11. +----+------+------------+------+  
    
12. 1 row in set (0.00 sec)  

复制代码

4，不能使用预处理语句参数，存储过程中的参数或局部变量

1. mysql> prepare p_test from "select * from user";   //产生一个预处理变量  
   
2. Query OK, 0 rows affected (0.00 sec)  
   
3. Statement prepared  
   
4.   
   
5. mysql> create view v_test as execute p_test;   //使用预处理变量报错  
   
6. 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  
   
7. mysql> create view v_test as p_test;    //这样也不行  
   
8. 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  
   
9. mysql>  execute p_test;    //单独是没问题的  
   
10. +----+--------+-----+  
    
11. | id | name   | sex |  
    
12. +----+--------+-----+  
    
13. |  1 | zhangy |   0 |  
    
14. |  3 | tank   |   0 |  
    
15. |  4 | tank   |   0 |  
    
16. +----+--------+-----+  
    
17. 3 rows in set (0.00 sec)  

复制代码

存储过程中产生的参数，或者是局量也不行，大家可以试一下。

5，如果预处理语句调用了视图，视图就不能变了。

1. mysql> create view aa_test as select * from comment;    //创建一个视图  
   
2. Query OK, 0 rows affected (0.26 sec)  
   
3.   
   
4. mysql> prepare test22 from "select * from aa_test";   //预处理语句使用了这个视图  
   
5. Query OK, 0 rows affected (0.00 sec)  
   
6. Statement prepared  
   
7.   
   
8. mysql> execute test22;     //调用一下预处理语句  
   
9. +------+------+--------+---------+  
   
10. | c_id | u_id | name   | content |  
    
11. +------+------+--------+---------+  
    
12. |    1 |    1 | zhangy | test    |  
    
13. |    2 |    1 | zhangy | test2   |  
    
14. +------+------+--------+---------+  
    
15. 2 rows in set (0.00 sec)  
    
16.   
    
17. mysql> alter view aa_test as select * from user;    //修改视图,把基础表改成user  
    
18. Query OK, 0 rows affected (0.00 sec)  
    
19.   
    
20. mysql> execute test22;      //在调用一下预处理语句，内容没有变  
    
21. +------+------+--------+---------+  
    
22. | c_id | u_id | name   | content |  
    
23. +------+------+--------+---------+  
    
24. |    1 |    1 | zhangy | test    |  
    
25. |    2 |    1 | zhangy | test2   |  
    
26. +------+------+--------+---------+  
    
27. 2 rows in set (0.00 sec)  

复制代码

6，在存储过程中不能修改视图

1. mysql> create procedure test3()  
   
2. -> begin  
   
3. -> select * from aa_test;  
   
4. -> alter view aa_test as "select * from comment";  
   
5. -> select * from aa_test;  
   
6. -> end;|  
   
7. ERROR 1314 (0A000): ALTER VIEW is not allowed in stored procedures    //会报错的  

复制代码

为什么是mysql手册里面，我看到可以在存储过程中修改视图的，为什么我用的mysql就不行呢？是不是mysql版本的问题。我用的是Server version: 5.1.26-rc-log Source distribution

7，不能给视图添加索引

1. mysql> create index aa_index on aa_test (c_id);  
   
2. ERROR 1347 (HY000): 'test.aa_test' is not BASE TABLE   //添加索引会报错的  

复制代码

视图根本不是基本的表，在存放数据的文件夹中，他只有一个结构文件，没有.MYD,.MYI文件，如果能增加索引存放到什么地方。

8，视图插入，添加，删除的限制
对于有些视图是可以UPDATE、DELETE或INSERT等操作的，以达到修改基本表的内容。对于可更新的视图，它必须和基本表是一一对应关系。如果视图中包括以下的东西就不是一一对应关系了。就不能进行更新操作。

1. //下面的视图根基本表user是一一对应关系，可以进行更新操作  
   
2. mysql> create view v_user as select * from user;  
   
3. Query OK, 0 rows affected (0.00 sec)  
   
4.   
   
5. //视图对应二个基本表，视图中的内容，不和任何一张表一一对应，不能进行更新操作  
   
6. 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';  
   
7. 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的这几种情况，其实就是一种情况，规则就是，视图的数据根基本表的数据不一样了。