MySQL的视图

56432

  MySQL同其它数据库一样都有视图-VIEW，VIEW并不是真实存在的表，它其实是通过SELECT查询出来的投影（查询结果）来当成表来查询，在VIEW中存储的其实都是事先写好的SELECT语句，这样在平时就可以直接查询VIEW中的内容而查询到相应数据，在这里就用简单的2张表来演示下,建表的SQL如下附件，而在MySQL中创建删除VIEW也很简单：

1 2 3 4 5 6

创建：      CREATE VIEW 视图名 AS SELECT语句 修改：       ALTER VIEW 视图名 AS SELECT语句 删除：       DROP VIEW 视图名

而在MySQL中创建好的视图是当成MySQL中没有任何属性的表存在于数据库中Comment信息是VIEW的表，可以通过查看表的状态信息得知，创建好的VIEW物理文件则是在datadir下的数据文件夹中仅有一个MyISAM引擎的表结构文件，类似于MySQL的性能信息库performance_schema一样，其实performance_schema就是视图

1 2	mysql> SHOW TABLES; mysql> SHOW TABLE STATUS\G

总的来说VIEW一般用于以下几个场景中：
    1、简化子查询
在经常查询SQL中有子查询，如：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

mysql> use my_test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SELECT     ->     *     -> FROM     ->     (     ->         SELECT     ->             *     ->         FROM     ->             book_tbl t     ->         WHERE     ->             t.storage_date REGEXP '^2017.*$'     ->     ) tmp     -> WHERE     ->     tmp.book_name REGEXP '^C'; +---------+-----------+--------------+ | book_id | book_name | storage_date | +---------+-----------+--------------+ |       5 | C         | 2017-04-05   | +---------+-----------+--------------+ 1 row in set (0.00 sec)

在这种情况下就可以创建视图，平时只要直接查询视图即可获取到数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

CREATE VIEW v_book_c_2017 AS SELECT     * FROM     (         SELECT             *         FROM             book_tbl t         WHERE             t.storage_date REGEXP '^2017.*$'     ) tmp WHERE     tmp.book_name REGEXP '^C'; #建议用客户端工具中写SQL创建 mysql> SELECT * FROM v_book_c_2017; +---------+-----------+--------------+ | book_id | book_name | storage_date | +---------+-----------+--------------+ |       5 | C         | 2017-04-05   | +---------+-----------+--------------+ 1 row in set (0.01 sec)

    2、表的权限控制
如果一张重要的信息表，如用户表在某一些MySQL用户下不能让其看到所有字段信息时也可以使用视图，当然直接使用GRANTS授权相应字段也可以，但是这样用户在DESCRIBE tablename时还是可以看见表结构的，所以使用GRANTS授权相应字段也不是真正不泄露相应表结构信息的，此时就可以创建VIEW后直接把VIEW授权给用户即可，还是刚才book_tbl表的例子，某个用户仅能查询book_tbl前2个字段内的信息表时，创建相应的VIEW时:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

mysql> CREATE VIEW v_book_name AS SELECT     -> t.book_id,     -> t.book_name     -> FROM     -> book_tbl t; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM v_book_name; +---------+-----------+ | book_id | book_name | +---------+-----------+ |       1 | PHP       | |       2 | MySQL     | |       3 | Java      | |       4 | Python    | |       5 | C         | |       6 | Shell     | +---------+-----------+ 6 rows in set (0.00 sec)

    3、大数据表分表
在一张数据量极大的表中平时有大量的查询部分信息，此时在创建VIEW的时候写好SELECT的查询条件后，创建的VIEW就可以当成分表来查询使用，这样就可以不加条件SELECT * FROM 视图名 查询相应的数据信息，如在book_tbl表中需要查询2017年入库的图书信息，即可以以下方式创建VIEW：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

mysql> CREATE VIEW v_book_2017 AS SELECT     -> *     -> FROM     -> book_tbl t     -> WHERE     -> t.storage_date REGEXP '^2017.*$'; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM v_book_2017; +---------+-----------+--------------+ | book_id | book_name | storage_date | +---------+-----------+--------------+ |       1 | PHP       | 2017-04-12   | |       2 | MySQL     | 2017-04-12   | |       5 | C         | 2017-04-05   | |       6 | Shell     | 2017-09-03   | +---------+-----------+--------------+ 4 rows in set (0.00 sec)

    4、查询多张表关联信息
在平时的时候需要经常查询多张表的聚合信息时就可以创建好VIEW已简化平时写的大量SQL，这样多张表的聚合或者是关联信息就可以当成表来查询，从而可以简化相应的建立工作，如在book_tbl表和book_price表中找出已经有价格上市的书籍，如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

mysql> CREATE VIEW v_listed_book AS SELECT     -> a.book_id,     -> a.book_name,     -> b.book_price     -> FROM     -> book_tbl a     -> JOIN book_price b ON a.book_name = b.book_name; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM v_listed_book; +---------+-----------+------------+ | book_id | book_name | book_price | +---------+-----------+------------+ |       1 | PHP       |     10.000 | |       2 | MySQL     |     22.000 | |       3 | Java      |      9.500 | |       6 | Shell     |     20.000 | +---------+-----------+------------+ 4 rows in set (0.00 sec)

当然在使用MySQL的VIEW的过程中需要注意的几点是：一、MySQL自身并不是很擅长子查询，在MySQL做子查询查询时，需要对子查询启别名当成临时表来查询，在Oracle或者SqlServer中因为有另行封装开发过是不需要的，这一点比较重要，不然在MySQL的子查询或报1093错误；二、MySQL无法做到真正的物化视图，当然通过建立真实表利用触发器也可以实现，但是都不是MySQL自身就有物化视图，所以MySQL的视图也仅能用于查询，且不能写注释，而且建议平时不要使用大量的物化视图，以免导致在创建时不当反而导致性能下降且移植性降低；三、在VIEW中是可以建立视图的视图的，即在VIEW的基础上再建立VIEW，但是建议平时不要这么使用，以免当前一者VIEW因为删除导致在关联VIEW的视图失效，在日常生产环境中需要避免这样使用。四、VIEW中存储的是写好的SELECT语句，所以在视图中查出来的数据都是真实表的投影数据，当VIEW关联的真实表发生改变则VIEW的数据也会改变，且当真实表改名或者删除时VIEW也会失效，所以在使用视图VIEW时需要在日常真实表改变时修改或删除。