MySQL - 檢視表 (View)

檢視表是一個虛擬的資料表，從MySQL 5.0.1 版本即開始支援

在使用時，設計者可自行將多個表格進行關聯、篩選，製作成一份獨立的檢視表

使用上，具有以下優點

簡單明瞭: 使用者不需要理解整個資料結構，直接呼叫檢視表就能取得整合完成的資料
安全: 使用者僅能看到檢視表允許查詢的部分
獨立: 檢視表結構會彙整關聯的資料表，當關聯資料表新增資料時，也能同時取得；僅有當關聯表修改列名時，才需要調整檢視表的結構對應名稱

建立檢視表

CREATE OR REPLACE VIEW user_view AS SELECT * FROM user;
Query OK, 0 rows affected (0.01 sec)

刪除檢視表

DROP VIEW user_view
Query OK, 0 rows affected (0.00 sec)

查看檢視表

SELECT * FROM user_view;
+-------+------------+
| names | join_date  |
+-------+------------+
| a    | 2018-01-01 |
| adam  | 2018-02-01 |
| adam  | 2018-02-01 |
+-------+------------+
3 rows in set (0.00 sec)

更新檢視表

檢視表對於更新資料有諸多限制，在一般單純 SELECT 的檢視表，可執行 UPDATE 功能但是有用到 sum, min, max,count, distinct, group by, having, union…. 等函式，都無法執行更新

在這裡還是建議，檢視表盡量僅用於檢視功能，如果需要進一步的操作，應該使用其他方式

UPDATE user_view set names='aa' WHERE names='a';
Query OK, 1 row affected (0.00 sec)

SELECT * FROM user_view;
+-------+------------+
| names | join_date  |
+-------+------------+
| aa    | 2018-01-01 |
| adam  | 2018-02-01 |
| adam  | 2018-02-01 |
+-------+------------+
3 rows in set (0.00 sec)

查看檢視表定義

SHOW TABLE STATUS LIKE 'user_view' \G
*************************** 1. row ***************************
           Name: user_view
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)

檢視表中建立檢視表

在MySQL 檢視表的定義存在一些限制，包括 FROM 之後不能有子查詢，使用 sum, min, max,count, distinct, group by, having, union…. 等函式，都無法執行更新..等

雖然FROM 之後不能有子查詢，還是可以先將子查詢建立成檢視表，就能實現

對於檢視表中使用檢視表的方式，

在更新資料時，可以搭配 CASCADED 與 LOCAL 這兩個檢查選項

CASCADED : 檢視表必須連同滿足內部依賴檢視表的條件，才可更新本檢視表 (預設選項，若無指定名稱則預設為CASCADED)

WITH CASCADED CHECK OPTION

LOCAL : 檢視表不須考慮內部依賴檢視表的條件，即可更新本檢視表

WITH LOCAL CHECK OPTION

CHECK OPTION 範例

建立表單，並且插入數值9

MariaDB [example]> CREATE TABLE main (i INT);
Query OK, 0 rows affected (0.10 sec)

MariaDB [example]> INSERT INTO main(i) VALUES (9);
Query OK, 1 row affected (0.00 sec)

MariaDB [example]> select * from main;
+------+
| i    |
+------+
|    9 |
+------+
1 row in set (0.00 sec)

建立三個view

main_view 指定 i < 10，未指定CHECK OPTION (預設為CASCADED)
main1_view 指定 i > 5，指定為CASCADED CHECK OPTION
main2_view 指定 i > 5，指定為LOCAL CHECK OPTION

MariaDB [example]> create or replace view main_view as
    -> select * from main
    -> where i < 10 with check option;
Query OK, 0 rows affected (0.02 sec)

MariaDB [example]> create or replace view main1_view as
    -> select * from main_view
    -> where i > 5 with cascaded check option;
Query OK, 0 rows affected (0.02 sec)

MariaDB [example]> create or replace view main2_view as
    -> select * from main_view
    -> where i > 5 with local check option;
Query OK, 0 rows affected (0.01 sec)

接著，試著更新資料，將 9 更新為 10 的情況

在 main1_view 會發生錯誤， main2_view 則更新成功

因為 main1_view 指定為CASCADED，需要同時檢查依賴檢視表的規則

main2_view 指定為 LOCAL ，只會檢查自己檢視表的規則，不會檢查依賴檢視表

MariaDB [example]> update main1_view set i=10 where i=9;
ERROR 1369 (HY000): CHECK OPTION failed 'example.main1_view'


MariaDB [example]> update main2_view set i=10 where i=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ex

CREATE OR REPLACE VIEW consumption_view AS (
SELECT 
	consumption.id, 
    consumption.member_id, 
    mfrom.name as mfrom_name,
    consumption.to_member_id, 
    mto.name as mto_name,
    consumption.actions, 
    consumption.dollars, 
    consumption.fees, 
    consumption.buysafeno, 
    consumption.created_at
FROM 
	consumption
LEFT OUTER JOIN
	members AS mfrom ON mfrom.id = consumption.member_id
LEFT OUTER JOIN
	members AS mto ON mto.id = consumption.to_member_id);

CREATE OR REPLACE VIEW tmp_information_view AS (
SELECT 
	tmp_information.id,
    tmp_information.member_id,
    members.name,
    tmp_information.buysafeno,
    tmp_information.tags,
    tmp_information.data,
    tmp_information.type,
    tmp_information.created_time
FROM 
	`tmp_information`
LEFT JOIN
	members 
ON
	tmp_information.member_id = members.id
WHERE 1);