MySQL 5 視圖建立與管理完整指南

基本語法

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

• OR REPLACE：視圖存在時替換，需具備 DELETE 權限
• select_statement：定義視圖的 SELECT 查詢，可引用基礎表或其他視圖
• 需具備 CREATE VIEW 權限及查詢涉及欄位的 SELECT 權限
• 視圖屬於特定資料庫，預設建立於當前資料庫，亦可指定 db_name.view_name
• 資料庫內表與視圖共享命名空間，不可同名

欄位命名規則

• 視圖欄位名稱必須唯一，預設取自 SELECT 清單
• 可透過 column_list 明確指定別名，數量必須與查詢欄位數一致
• 查詢欄位可為簡單欄位引用、函數運算式、常數等

跨資料庫引用與無表查詢

• 未限定名稱依當前資料庫解析，可用 db_name.table_name 跨庫引用
• 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   |
+------+-------+-------+

視圖定義限制

• FROM 子句中不可包含子查詢
• 不可引用系統/使用者變數、預備語句參數
• 預存程序內定義視圖不可引用程序參數/區域變數
• 定義引用的表/視圖必須存在（事後刪除不影響視圖存在，但會導致查詢失敗，可用 CHECK TABLE 檢測）
• 不可引用 TEMPORARY 表，亦不可建立 TEMPORARY 視圖
• 不可關聯觸發器

ORDER BY、LIMIT 等子句的行為

• 視圖定義中允許 ORDER BY，但若外層查詢也有 ORDER BY，視圖內部的會被忽略
• LIMIT、ALL/DISTINCT、INTO、FOR UPDATE 等選項與外層查詢合併時效果未定義，建議避免在視圖定義中使用

系統變數變更對視圖結果的影響

mysql> CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));
mysql> SET NAMES 'latin1'; SELECT * FROM v;  -- latin1 / latin1_swedish_ci
mysql> SET NAMES 'utf8';   SELECT * FROM v;  -- utf8 / utf8_general_ci

視圖儲存的是查詢定義而非結果，執行時依當前會話環境解析。

ALGORITHM 選項深度解析

導致必須使用 TEMPTABLE 的構造

• 聚合函數（SUM, MIN, MAX, COUNT...）
• DISTINCT、GROUP BY、HAVING
• UNION / UNION ALL
• 僅引用常數值（無底層表）

可更新視圖條件

需滿足一對一關係（視圖列對應底層表列），且不包含：

• 聚合函數、DISTINCT、GROUP BY、HAVING、UNION
• 選取清單含子查詢、JOIN、不可更新視圖
• WHERE 子查詢引用 FROM 表
• ALGORITHM = TEMPTABLE

可插入視圖額外要求

• 無重複欄位名
• 包含基礎表所有無預設值的欄位
• 所有欄位皆為簡單欄位引用（非衍生欄位，如 col1+3、UPPER(col2)、子查詢）

混合簡單與衍生欄位的視圖不可插入，但可更新非衍生欄位。

多表可更新視圖

• 需可用 MERGE 處理，且使用內部連接（非外部連接/UNION）
• 僅允許更新單一表的欄位（SET 子句僅引用該表欄位）
• INSERT 僅支援單表插入，DELETE 不支援

WITH CHECK OPTION

• 限制插入/更新僅允許符合視圖 WHERE 條件的列
• LOCAL：僅檢查當前視圖條件
• CASCADED（預設）：遞迴檢查底層視圖條件

範例：LOCAL vs CASCADED

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;

INSERT INTO v2 VALUES (2);  -- 成功（僅檢查 v2: a>0）
INSERT INTO v3 VALUES (2);  -- 失敗（同時檢查 v3:a>0 與 v1:a<2）

系統變數 updatable_views_with_limit

影響帶 LIMIT 的可更新視圖行為，詳見官方文檔。