如何在MySQL中建立視圖？

create [or replace] [algorithm = {undefined | merge | temptable}]    viewview_name[(column_list)]    asselect_statement    [with [cascaded | local] check option]

該語句能建立新的視圖，如果給定了or replace子句，該語句還能替換已有的視圖。select_statement是一種select語句，它給出了視圖的定義。該語句可從基表或其他視圖進行選擇。

該語句要求具有針對視圖的create view權限，以及針對由select語句選擇的每一列上的某些權限。對于在select語句中其他地方使用的列，必須具有select權限。如果還有or replace子句，必須在視圖上具有drop權限。

視圖屬于資料庫。在預設情況下，将在目前資料庫建立新視圖。要想在給定資料庫中明确建立視圖，建立時，應将名稱指定為db_name.view_name。

表和視圖共享資料庫中相同的名稱空間，是以，資料庫不能包含具有相同名稱的表和視圖。

視圖必須具有唯一的列名，不得有重複，就像基表那樣。預設情況下，由select語句檢索的列名将用作視圖列名。要想為視圖列定義明确的名稱，可使用可選的column_list子句，列出由逗号隔開的id。column_list中的名稱數目必須等于select語句檢索的列數。

select語句檢索的列可以是對表列的簡單引用。也可以是使用函數、常量值、操作符等的表達式。

對于select語句中不合格的表或視圖，将根據預設的資料庫進行解釋。通過用恰當的資料庫名稱限定表或視圖名，視圖能夠引用表或其他資料庫中的視圖。

能夠使用多種select語句建立視圖。視圖能夠引用基表或其他視圖。它能使用聯合、union和子查詢。select甚至不需引用任何表。在下面的示例中，定義了從另一表選擇兩列的視圖，并給出了根據這些列計算的表達式：

視圖定義服從下述限制：

·         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子句。

正如前面所介紹的那樣，通過将視圖定義中的對應部分合并到引用視圖的語句中，對merge進行處理。在下面的示例中，簡要介紹了merge的工作方式。在該示例中，假定有1個具有下述定義的視圖v_merge：

create algorithm = merge view v_merge (vc1, vc2) asselect 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算法要求視圖中的行和基表中的行具有一對一的關系。如果不具有該關系。必須使用臨時表取而代之。如果視圖包含下述結構中的任何一種，将失去一對一的關系：

·         distinct

·         group by

·         having

·         union或union all

·         僅引用文字值（在該情況下，沒有基本表）。

某些視圖是可更新的。也就是說，可以在諸如update、delete或insert等語句中使用它們，以更新基表的内容。對于可更新的視圖，在視圖中的行和基表中的行之間必須具有一對一的關系。還有一些特定的其他結構，這類結構會使得視圖不可更新。更具體地講，如果視圖包含下述結構中的任何一種，那麼它就是不可更新的：

·         聚合函數（sum(), min(), max(), count()等）。

·         位于選擇清單中的子查詢

·         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檢查選項，是以，不僅會針對它自己的檢查對插入項進行測試，也會針對基本視圖的檢查對插入項進行測試。在下面的語句中，介紹了這些差異：