MySQL的視圖

標籤：mysql   view   

   MySQL同其它資料庫一樣都有視圖-VIEW，VIEW並不是真實存在的表，它其實是通過SELECT查詢出來的投影（結果）來當成表來查詢，在VIEW中儲存的其實都是事先寫好的SELECT語句，這樣在平時就可以直接查詢VIEW中的內容而查詢到相應資料，在這裡就用簡單的2張表來示範下,建表的SQL如下附件，而在MySQL中建立刪除VIEW也很簡單：

建立：     CREATE VIEW 視圖名 AS SELECT語句修改：      ALTER VIEW 視圖名 AS SELECT語句刪除：      DROP VIEW 視圖名

而在MySQL中建立好的視圖是當成MySQL中沒有任何屬性的表存在於資料庫中Comment資訊是VIEW的表，可以通過查看錶的狀態資訊得知，建立好的VIEW物理檔案則是在datadir下的資料檔案夾中僅有一個MyISAM引擎的表結構檔案，類似於MySQL的效能資訊庫performance_schema一樣，其實performance_schema就是視圖

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

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M01/A4/6F/wKioL1mrfi7im05aAAbwXIE4yzs954.png-wh_500x0-wm_3-wmp_4-s_4209366237.png" title="VIEW物理檔案" alt="wKioL1mrfi7im05aAAbwXIE4yzs954.png-wh_50" />

總的來說VIEW一般用於以下幾個情境中：

    1、簡化子查詢

在經常查詢SQL中有子查詢，如：

mysql> use my_test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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)

在這種情況下就可以建立視圖，平時只要直接查詢檢視即可擷取到資料

CREATE VIEW v_book_c_2017 AS SELECT    *FROM    (        SELECT            *        FROM            book_tbl t        WHERE            t.storage_date REGEXP ‘^2017.*$‘    ) tmpWHERE    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時:

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：

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表中找出已經有價格上市的書籍，如下：

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時需要在日常真實表改變時修改或刪除。

本文出自 “Jim的技術隨筆” 部落格，謝絕轉載！

MySQL的視圖