22. 視圖 · MySQL 5.1參考手冊

### 第22章：視圖 ** 目錄** [22.1. ALTER VIEW語法](#)[22.2. CREATE VIEW語法](#)[22.3. DROP VIEW語法](#)[22.4. SHOW CREATE VIEW語法](#) 在5.1版MySQL服務器中提供了視圖功能（包括可更新視圖）。本章討論了下述主題： ·???????? 使用CREATE VIEW或ALTER VIEW創建或更改視圖。 ·???????? 使用DROP VIEW銷毀視圖。 ·???????? 使用SHOW CREATE VIEW顯示視圖元數據。關于使用視圖方面的限制，請參見[附錄I：特性限制](# "Appendix?I.?Feature Restrictions")。如果你已從不支持視圖的較舊版本升級到MySQL 5.1，要想使用視圖，應升級授權表，使之包含與視圖有關的權限。請參見[2.10.2節，“升級授權表”](# "2.10.2.?Upgrading the Grant Tables")。 ### 22.1.?ALTER VIEW語法 ~~~ ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] ~~~ ~~~ ?? ?VIEW view_name [(column_list)] ~~~ ~~~ ??? AS select_statement ~~~ ~~~ ??? [WITH [CASCADED | LOCAL] CHECK OPTION] ~~~ 該語句用于更改已有視圖的定義。其語法與CREATE VIEW類似。請參見[22.2節，“CREATE VIEW語法”](# "22.2.?CREATE VIEW Syntax")。該語句需要具有針對視圖的CREATE VIEW和DROP權限，也需要針對SELECT語句中引用的每一列的某些權限。 ### 22.2.?CREATE VIEW語法 ~~~ CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] ~~~ ~~~ ?? ?VIEW view_name [(column_list)] ~~~ ~~~ ??? AS select_statement ~~~ ~~~ ??? [WITH [CASCADED | LOCAL] CHECK OPTION] ~~~ 該語句能創建新的視圖，如果給定了OR REPLACE子句，該語句還能替換已有的視圖。*select_statement*是一種SELECT語句，它給出了視圖的定義。該語句可從基表或其他視圖進行選擇。該語句要求具有針對視圖的CREATE VIEW權限，以及針對由SELECT語句選擇的每一列上的某些權限。對于在SELECT語句中其他地方使用的列，必須具有SELECT權限。如果還有OR REPLACE子句，必須在視圖上具有DROP權限。視圖屬于數據庫。在默認情況下，將在當前數據庫創建新視圖。要想在給定數據庫中明確創建視圖，創建時，應將名稱指定為*db_name.view_name*。 ~~~ mysql> CREATE VIEW test.v AS SELECT * FROM t; ~~~ 表和視圖共享數據庫中相同的名稱空間，因此，數據庫不能包含具有相同名稱的表和視圖。視圖必須具有唯一的列名，不得有重復，就像基表那樣。默認情況下，由SELECT語句檢索的列名將用作視圖列名。要想為視圖列定義明確的名稱，可使用可選的*column_list*子句，列出由逗號隔開的ID。*column_list*中的名稱數目必須等于SELECT語句檢索的列數。 SELECT語句檢索的列可以是對表列的簡單引用。也可以是使用函數、常量值、操作符等的表達式。對于SELECT語句中不合格的表或視圖，將根據默認的數據庫進行解釋。通過用恰當的數據庫名稱限定表或視圖名，視圖能夠引用表或其他數據庫中的視圖。能夠使用多種SELECT語句創建視圖。視圖能夠引用基表或其他視圖。它能使用聯合、UNION和子查詢。SELECT甚至不需引用任何表。在下面的示例中，定義了從另一表選擇兩列的視圖，并給出了根據這些列計算的表達式： ~~~ mysql> CREATE TABLE t (qty INT, price INT); ~~~ ~~~ mysql> INSERT INTO t VALUES(3, 50); ~~~ ~~~ mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t; ~~~ ~~~ mysql> SELECT * FROM v; ~~~ ~~~ +------+-------+-------+ ~~~ ~~~ | qty? | price | value | ~~~ ~~~ +------+-------+-------+ ~~~ ~~~ |??? 3 |??? 50 |?? 150 | ~~~ ~~~ +------+-------+-------+ ~~~ 視圖定義服從下述限制： ·???????? SELECT語句不能包含FROM子句中的子查詢。 ·???????? SELECT語句不能引用系統或用戶變量。 ·???????? SELECT語句不能引用預處理語句參數。 ·???????? 在存儲子程序內，定義不能引用子程序參數或局部變量。 ·???????? 在定義中引用的表或視圖必須存在。但是，創建了視圖后，能夠舍棄定義引用的表或視圖。要想檢查視圖定義是否存在這類問題，可使用CHECK TABLE語句。 ·???????? 在定義中不能引用TEMPORARY表，不能創建TEMPORARY視圖。 ·???????? 在視圖定義中命名的表必須已存在。 ·???????? 不能將觸發程序與視圖關聯在一起。在視圖定義中允許使用ORDER BY，但是，如果從特定視圖進行了選擇，而該視圖使用了具有自己ORDER BY的語句，它將被忽略。對于定義中的其他選項或子句，它們將被增加到引用視圖的語句的選項或子句中，但效果未定義。例如，如果在視圖定義中包含LIMIT子句，而且從特定視圖進行了選擇，而該視圖使用了具有自己LIMIT子句的語句，那么對使用哪個LIMIT未作定義。相同的原理也適用于其他選項，如跟在SELECT關鍵字后的ALL、DISTINCT或SQL_SMALL_RESULT，并適用于其他子句，如INTO、FOR UPDATE、LOCK IN SHARE MODE、以及PROCEDURE。如果創建了視圖，并通過更改系統變量更改了查詢處理環境，會影響從視圖獲得的結果： ~~~ mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65)); ~~~ ~~~ Query OK, 0 rows affected (0.00 sec) ~~~ ~~~ ? ~~~ ~~~ mysql> SET NAMES 'latin1'; ~~~ ~~~ Query OK, 0 rows affected (0.00 sec) ~~~ ~~~ ? ~~~ ~~~ mysql> SELECT * FROM v; ~~~ ~~~ +-------------------+---------------------+ ~~~ ~~~ | CHARSET(CHAR(65)) | COLLATION(CHAR(65)) | ~~~ ~~~ +-------------------+---------------------+ ~~~ ~~~ | latin1??????????? | latin1_swedish_ci?? | ~~~ ~~~ +-------------------+---------------------+ ~~~ ~~~ 1 row in set (0.00 sec) ~~~ ~~~ ? ~~~ ~~~ mysql> SET NAMES 'utf8'; ~~~ ~~~ Query OK, 0 rows affected (0.00 sec) ~~~ ~~~ ? ~~~ ~~~ mysql> SELECT * FROM v; ~~~ ~~~ +-------------------+---------------------+ ~~~ ~~~ | CHARSET(CHAR(65)) | COLLATION(CHAR(65)) | ~~~ ~~~ +-------------------+---------------------+ ~~~ ~~~ | utf8????????????? | utf8_general_ci???? | ~~~ ~~~ +-------------------+---------------------+ ~~~ ~~~ 1 row in set (0.00 sec) ~~~ 可選的ALGORITHM子句是對標準SQL的MySQL擴展。ALGORITHM可取三個值：MERGE、TEMPTABLE或UNDEFINED。如果沒有ALGORITHM子句，默認算法是UNDEFINED（未定義的）。算法會影響MySQL處理視圖的方式。對于MERGE，會將引用視圖的語句的文本與視圖定義合并起來，使得視圖定義的某一部分取代語句的對應部分。對于TEMPTABLE，視圖的結果將被置于臨時表中，然后使用它執行語句。對于UNDEFINED，MySQL將選擇所要使用的算法。如果可能，它傾向于MERGE而不是TEMPTABLE，這是因為MERGE通常更有效，而且如果使用了臨時表，視圖是不可更新的。明確選擇TEMPTABLE的1個原因在于，創建臨時表之后、并在完成語句處理之前，能夠釋放基表上的鎖定。與MERGE算法相比，鎖定釋放的速度更快，這樣，使用視圖的其他客戶端不會被屏蔽過長時間。視圖算法可以是UNDEFINED，有三種方式： ·???????? 在CREATE VIEW語句中沒有ALGORITHM子句。 ·???????? CREATE VIEW語句有1個顯式ALGORITHM = UNDEFINED子句。 ·???????? 為僅能用臨時表處理的視圖指定ALGORITHM = MERGE。在這種情況下，MySQL將生成告警，并將算法設置為UNDEFINED。正如前面所介紹的那樣，通過將視圖定義中的對應部分合并到引用視圖的語句中，對MERGE進行處理。在下面的示例中，簡要介紹了MERGE的工作方式。在該示例中，假定有1個具有下述定義的視圖v_merge： ~~~ CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS ~~~ ~~~ SELECT c1, c2 FROM t WHERE c3 > 100; ~~~ 示例1：假定發出了下述語句： ~~~ SELECT * FROM v_merge; ~~~ MySQL以下述方式處理語句： ·???????? v_merge成為t ·???????? *成為vc1、vc2，與c1、c2對應 ·???????? 增加視圖WHERE子句所產生的將執行的語句為： ~~~ SELECT c1, c2 FROM t WHERE c3 > 100; ~~~ 示例2：假定發出了下述語句： ~~~ SELECT * FROM v_merge WHERE vc1 < 100; ~~~ 該語句的處理方式與前面介紹的類似，但vc1 < 100變為c1 < 100，并使用AND連接詞將視圖的WHERE子句添加到語句的WHERE子句中（增加了圓括號以確保以正確的優先順序執行子句部分）。所得的將要執行的語句變為： ~~~ SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100); ~~~ 事實上，將要執行的語句是具有下述形式的WHERE子句： ~~~ WHERE (select WHERE) AND (view WHERE) ~~~ MERGE算法要求視圖中的行和基表中的行具有一對一的關系。如果不具有該關系。必須使用臨時表取而代之。如果視圖包含下述結構中的任何一種，將失去一對一的關系： ·???????? 聚合函數（SUM(), MIN(), MAX(), COUNT()等）。 ·???????? DISTINCT ·???????? GROUP BY ·???????? HAVING ·???????? UNION或UNION ALL ·???????? 僅引用文字值（在該情況下，沒有基本表）。某些視圖是可更新的。也就是說，可以在諸如UPDATE、DELETE或INSERT等語句中使用它們，以更新基表的內容。對于可更新的視圖，在視圖中的行和基表中的行之間必須具有一對一的關系。還有一些特定的其他結構，這類結構會使得視圖不可更新。更具體地講，如果視圖包含下述結構中的任何一種，那么它就是不可更新的： ·???????? 聚合函數（SUM(), MIN(), MAX(), COUNT()等）。 ·???????? DISTINCT ·???????? GROUP BY ·???????? HAVING ·???????? UNION或UNION ALL ·???????? 位于選擇列表中的子查詢 ·???????? Join ·???????? FROM子句中的不可更新視圖 ·???????? WHERE子句中的子查詢，引用FROM子句中的表。 ·???????? 僅引用文字值（在該情況下，沒有要更新的基本表）。 ·???????? ALGORITHM = TEMPTABLE（使用臨時表總會使視圖成為不可更新的）。關于可插入性（可用INSERT語句更新），如果它也滿足關于視圖列的下述額外要求，可更新的視圖也是可插入的： ·???????? 不得有重復的視圖列名稱。 ·???????? 視圖必須包含沒有默認值的基表中的所有列。 ·???????? 視圖列必須是簡單的列引用而不是導出列。導出列不是簡單的列引用，而是從表達式導出的。下面給出了一些導出列示例： ~~~ ·??????????????? 3.14159 ~~~ ~~~ ·??????????????? col1 + 3 ~~~ ~~~ ·??????????????? UPPER(col2) ~~~ ~~~ ·??????????????? col3 / col4 ~~~ ~~~ ·??????????????? (subquery) ~~~ 混合了簡單列引用和導出列的視圖是不可插入的，但是，如果僅更新非導出列，視圖是可更新的。考慮下述視圖： ~~~ CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t; ~~~ 該視圖是不可插入的，這是因為col2是從表達式導出的。但是，如果更新時不更新col2，它是可更新的。這類更新是允許的： ~~~ UPDATE v SET col1 = 0; ~~~ 下述更新是不允許的，原因在于，它試圖更新導出列： ~~~ UPDATE v SET col2 = 0; ~~~ 在某些情況下，能夠更新多表視圖，假定它能使用MERGE算法進行處理。為此，視圖必須使用內部聯合（而不是外部聯合或UNION）。此外，僅能更新視圖定義中的單個表，因此，SET子句必須僅命名視圖中某一表的列。即使從理論上講也是可更新的，不允許使用UNION ALL的視圖，這是因為，在實施中將使用臨時表來處理它們。對于多表可更新視圖，如果是將其插入單個表中，INSERT能夠工作。不支持DELETE。對于可更新視圖，可給定WITH CHECK OPTION子句來防止插入或更新行，除非作用在行上的*select_statement*中的WHERE子句為“真”。在關于可更新視圖的WITH CHECK OPTION子句中，當視圖是根據另一個視圖定義的時，LOCAL和CASCADED關鍵字決定了檢查測試的范圍。LOCAL關鍵字對CHECK OPTION進行了限制，使其僅作用在定義的視圖上，CASCADED會對將進行評估的基表進行檢查。如果未給定任一關鍵字，默認值為CASCADED。請考慮下述表和視圖集合的定義： ~~~ mysql> CREATE TABLE t1 (a INT); ~~~ ~~~ mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2 ~~~ ~~~ ??? -> WITH CHECK OPTION; ~~~ ~~~ mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0 ~~~ ~~~ ??? -> WITH LOCAL CHECK OPTION; ~~~ ~~~ mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0 ~~~ ~~~ ??? -> WITH CASCADED CHECK OPTION; ~~~ 這里，視圖v2和v3是根據另一視圖v1定義的。v2具有LOCAL檢查選項，因此，僅會針對v2檢查對插入項進行測試。v3具有CASCADED檢查選項，因此，不僅會針對它自己的檢查對插入項進行測試，也會針對基本視圖的檢查對插入項進行測試。在下面的語句中，介紹了這些差異： ~~~ ql> 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' ~~~ 視圖的可更新性可能會受到系統變量updatable_views_with_limit的值的影響。請參見[5.3.3節，“服務器系統變量”](# "5.3.3.?Server System Variables")。 INFORMATION_SCHEMA包含1個VIEWS表，從該表可獲取關于視圖對象的信息。請參見[23.1.15節，“INFORMATION_SCHEMA VIEWS表”](# "23.1.15.?The INFORMATION_SCHEMA VIEWS Table")。 ### 22.3.?DROP VIEW語法 ~~~ DROP VIEW [IF EXISTS] ~~~ ~~~ ??? view_name [, view_name] ... ~~~ ~~~ ??? [RESTRICT | CASCADE] ~~~ DROP VIEW能夠刪除1個或多個視圖。必須在每個視圖上擁有DROP權限。可以使用關鍵字IF EXISTS來防止因不存在的視圖而出錯。給定了該子句時，將為每個不存在的視圖生成NOTE。請參見[13.5.4.22節，“SHOW WARNINGS語法”](# "13.5.4.22.?SHOW WARNINGS Syntax")。如果給定了RESTRICT和CASCADE，將解析并忽略它們。 ### 22.4.?SHOW CREATE VIEW語法 ~~~ SHOW CREATE VIEW view_name ~~~ 該語句給出了1個創建給定視圖的CREATE VIEW語句。 ~~~ mysql> SHOW CREATE VIEW v; ~~~ ~~~ +------+----------------------------------------------------+ ~~~ ~~~ | View | Create View?????????????????????????????????????? ?| ~~~ ~~~ +------+----------------------------------------------------+ ~~~ ~~~ | v??? | CREATE VIEW `test`.`v` AS select 1 AS `a`,2 AS `b` | ~~~ ~~~ +------+----------------------------------------------------+ ~~~ 這是MySQL參考手冊的翻譯版本，關于MySQL參考手冊，請訪問[dev.mysql.com](http://dev.mysql.com/doc/mysql/en)。原始參考手冊為英文版，與英文版參考手冊相比，本翻譯版可能不是最新的。