【MySQL】View 視圖

—— — — — — 2023/2/4更新 — — — — — —
寫完這篇文章後沒多久，又在網路上看到以下這篇文章，促使我想再多補充一些資料

提升服務效能、減輕DB負擔！(2): Materialized View

先說結論：

• 文章提的Materialized View(實體化檢視表)如其名，是需要占用儲存空間的(可設定過期時間避免資源浪費)，與本文所介紹的View 是不太一樣的
• Materialized View 是PostgreSQL或Oracle才有的功能，MySQL 目前都還沒有(至少到8.0版本都還沒有，但有其他做法可達到類似功能)
• 搭配cronjob 或其他排程套件可定期刷新Materialized View 達到提供緩存、減輕DB 負擔

— — — — — — 正文開始 — — — — — —

繼上篇的SP 後，本篇來介紹View 這個常用的東東(至少我們公司很常用拉XP)

VIEW

View 的概念其實就是一個虛擬的表，藉由SELECT 的查詢動態產生的虛擬資料表，為何我一直強調虛擬這個特性呢？因為相比其他真實存在於資料庫(儲存在硬碟)的資料表，View是一個虛擬、只存在於View自己定義的SELECT 結果集合體，因此你無法在電腦的硬碟上找到View 的資料表。然而在實際使用上，我們卻可以如操作實際存在的資料表那樣地操作View — 所有SQL的查詢語法和DML(INSERT/UPDATE/DELETE)操作(不建議)都可以在View上操作。

既然View 就如我們操作一般資料表，那為何還要多此一舉去寫View，而不直接操作員資料表就好呢？ 以下就來說明View 的優點：

優點

• 加強資料庫的安全性：這點我想是幾乎會選擇用View 的主要原因，因為View 可以將實體資料表結構隱藏起來，用View 可以選擇要給不同權限的人檢視及使用不同程度的內容，因此對於產出給不同層級要用的統計報表，View 就有其優勢。
• 提高效率：將複雜的查詢隱藏在View 內，可以簡化資料查詢。(對於資料庫效能未必有幫助)

當然View 也有其缺點：

缺點

• 多一道查詢工序，且沒有真正的資料(資料都是從原表來的)，所以有更多的操作限制
• 若針對不同權限的人開放不同層級的View，無疑會增添管理的複雜度

其實說到底，View 其實就是方便我們在簡化重複查詢或是針對不同權限使用者所使用的工具。以下終於要來實作View(歹戲拖棚XD)

宣告View

CREATE (OR REPLACE) (ALGORITHM = UNDEFINED | MERGE | TEMPTABLE ) 
VIEW view_name (column_list)
AS select_statement
(WITH (CASCADED | LOCAL) CHECK OPTION)

以上是View 大致的長相，接下來分別說明各部件

1. 首先可以選擇單純用 CREATE 語法建立View，也可以保險起見用CREATE OR REPLACE，如此一來在建立過程中，如果尚未存在同名的View，則會直接建立View，若已存在同名View，則會替換已有的View，相當於 ALTER VIEW的用法
2. 建立View 時可以為其選擇將來使用該View 的演算法，其值有 UNDEFINED、MERGE、TEMPTABLE三者可選。
   默認的演算法是UNDEFINED，若沒設定或是選擇UNDEFINED，則MySQL會自動選擇適合的算法(因為效能且為了能對View進行DML操作，MERGE優先權高於TEMPTABLE)；
   選MERGE的話，會將之後引用View 的語句和View 的定義合併(merge)起來，使的View 定義的部分內容取代引用語句的對應部分。非常抽象我明白......待會再詳細舉例；
   選TEMPTABLE的話，則會將View 的結果放置於暫時表中，然後再執行引用的語句內容，同樣待會再詳細舉例。
   若有使用下列關鍵字，則MySQL 沒辦法為View 選擇MERGE演算法：
   * Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
   * DISTINCT
   * GROUP BY
   * HAVING
   * LIMIT
   * UNION or UNION ALL
   * Subqueries in the select list
   * Assignments to user variables
   * Refererences only to literal values (in this case, there is no underlying table)
   Ref.
3. 接著就是為View 取名，並且可幫View 的欄位取名，取別名的欄位數必須和之後SELECT 出的欄位數相等
4. AS 後方接實際SELECT的原表，可單表、多表、跨資料庫的SELECT
5. 最後則是確認View 更新時保證資料的正確性： WITH CHECK OPTION，有兩個設定值可選 cascade、local，預設為cascade。
   cascade代表的是更新View 時須確認View 及引用原表或其他View 的相關限制( WHERE條件或是HAVING之類的)；
   local則代表View 時只需確認本View 的相關限制就行了(MySQL 5.7.6 前的版本)。
   MySQL 5.7.6 以後的版本，cascade、local兩者基本上沒差了，更新View 時都會檢查原表的限制；

總算介紹完怎麼宣告一個View，紙上談兵是不夠的，實作才能更融會貫通

實例1：MERGE演算法

先前提過MERGE演算法會將引用View 的語句和View 的定義合併起來，使的View 定義的部分內容取代引用語句的對應部分( the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.)。先看過下例再回來看這段定義，應該可以更好理解

CREATE VIEW ALGORITHM = MERGE v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;

接著用以下語句來引用View

1.1
SELECT * FROM v_merge;

MySQL 執行時看到演算法選擇MERGE，會將v_merge 替換成View 定義中的 t；* 則變成vc1, vc2，其實也就是c1, c2，vc1, vc2 只是定義時取的別名；最後再合併定義時的WHERE 語句。因此最終執行的語句會變成

SELECT c1, c2 FROM t WHERE c3 > 100;

那如果用下句來引用View

1.2
SELECT * FROM v_merge WHERE vc1 < 100;

概念上和上例非常類似，只是引用語句本身也有WHERE 條件，照上面的概念延伸的話，vc1 < 100 就會變成 c1 < 100，整體就會變成下句

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

因為在定義時就先有WHERE 限制，因此在優先順序上定義的限制在前

實例2：WITH CHECK OPTION

宣告時介紹過WITH CHECK OPTION帶不同設定時的意義，以下來進行比較

CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2 WITH CHECK OPTION;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0 WITH LOCAL CHECK OPTION;
CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0 WITH CASCADED CHECK OPTION;

可看到透過v1 View另外建立v2 及v3 View，v2 採用local v3則用cascade，local僅會檢查自己的限制條件，cascade則會檢查自己及引用的原表，因此為v2 及v3 同時插入一個值：2就會有不同的結果

-- Before MySQL 5.7.6
mysql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'

-- MySQL 5.7.6 and after 
mysql> INSERT INTO v2 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v2'
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'

最後提醒一點： 因為View 本身沒有資料，因此所有對View 的DML操作，最終都會體現在原表中！
然後也不是所有View 都可以進行DML操作，演算法必須是MERGE才可以，想一響邏輯上也可以理解，如果是TEMPTABLE演算法，則在執行引用語句前，資料已經先被SELECT出來放在暫時表中，引用語句是對暫時表進行操作，因此無用，所以可以理解成TEMPTABLE演算法不能進行DML操作。至於有哪些View 一定是TEMPTABLE演算法，可見下列：

Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
* DISTINCT
* GROUP BY
* HAVING
* LIMIT
* UNION or UNION ALL
* Subqueries in the select list
* Assignments to user variables
* Refererences only to literal values (in this case, there is no underlying

最後的最後補充一個小Tip：在MySQL 上執行SHOW CREATE VIEW view_name 就可以看到該View 的相關資訊拉~

資料來源：

MySQL :: MySQL 5.7 Reference Manual :: 8.2.2.4 Optimizing Derived Tables and View References with…

深入解析MySQL视图VIEW

SQL VIEW 檢視表 / 視圖 - SQL 語法教學 Tutorial