mysql 视图限制_mysql视图的限制，以及实例

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

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

mysql> show tables;//查看表

+------------------+

| Tables_in_uchome |

+------------------+

| comment          |

| user             |

+------------------+

2 rows in set (0.00 sec)

mysql> create view user asselect * from user;//视图名和存在表重名，报错

ERROR 1050 (42S01): Table 'user'already exists

mysql> create view v_user asselect * 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> 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 asselect * from tmp_user;//用临时表会报错，看下的错

ERROR 1352 (HY000): View's SELECT refers to a temporary table 'tmp_user'

//创建临时表 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 asselect * from aa where id=@test;//创建视图

ERROR 1351 (HY000): View's SELECT contains a variable orparameter//报sql中有变量，错误

mysql> select * from aa where id=@test;  //真正的表是可以使用的

+----+------+------------+------+

| id | name | nname      | sex  |

+----+------+------------+------+

|  2 | d    | bbbb,4bbbb | NULL |

+----+------+------------+------+

1 row in set (0.00 sec)

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 asexecute p_test;//使用预处理变量报错

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version forthe right syntax tousenear'execute p_test'at line 1

mysql> create view v_test asp_test;//这样也不行

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version forthe right syntax tousenear'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)

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_testasselect * 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 asselect * 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)

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> 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-logSource distribution

7，不能给视图添加索引

mysql> create index aa_index on aa_test (c_id);

ERROR 1347 (HY000): 'test.aa_test'is not BASE TABLE//添加索引会报错的

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 asselect * from user;

Query OK, 0 rows affected (0.00 sec)

//视图对应二个基本表，视图中的内容，不和任何一张表一一对应，不能进行更新操作

mysql> create view tall asselect 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)

//下面的视图根基本表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的这几种情况，其实就是一种情况，规则就是，视图的数据根基本表的数据不一样了。