關於網路那些事...

網路行銷,SEO,網路趨勢,教學文章,網頁設計,生活時事

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

  1. main_view 指定 i < 10, 未指定CHECK OPTION (預設為CASCADED)
  2. main1_view 指定 i > 5,指定為CASCADED CHECK OPTION
  3. 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);

如果你喜歡我們的文章內容,請在這裡按個讚



最新文章推薦