MySQL 視圖完整指南：建立、管理與實戰應用

MySQL 視圖完整指南

視圖 是虛擬資料表，其內容由 SELECT 查詢動態產生。合理使用視圖可提升安全性、簡化複雜查詢、隔離業務邏輯變更。本文系統講解 MySQL 視圖的建立、查看、修改、更新、刪除及進階特性。

一、為何使用視圖？

1. 安全性控管：將敏感欄位隱藏，僅授權視圖給特定用戶（GRANT SELECT ON view_name TO user）。
2. 查詢效能優化：預先定義複雜 JOIN、聚合邏輯，避免重複編寫 SQL。
3. 結構變更緩衝：底層資料表結構調整時，只需修改視圖定義，應用層 SQL 無需變動。
4. 複雜查詢拆解：將大查詢拆分為多個視圖，再透過視圖聯合查詢，提升可維護性。

二、建立視圖

語法

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

關鍵選項解析

範例：多表聯結視圖

CREATE TABLE student (stuno INT, stuname NVARCHAR(60));
CREATE TABLE stuinfo (stuno INT, class NVARCHAR(60), city NVARCHAR(60));

INSERT INTO student VALUES (1,'王林'),(2,'高莉'),(3,'張海');
INSERT INTO stuinfo VALUES (1,'五班','河南'),(2,'六班','河北'),(3,'七班','山東');

CREATE VIEW stu_class (id, name, class) AS
SELECT s.stuno, s.stuname, i.class
FROM student s JOIN stuinfo i ON s.stuno = i.stuno;

三、查看視圖

1. DESCRIBE / DESC

DESC stu_class;

顯示欄位定義、型別、是否 NULL、鍵、預設值等。

2. SHOW TABLE STATUS

SHOW TABLE STATUS LIKE 'stu_class';

Comment 欄位為 VIEW 即為視圖，其他欄位多為 NULL（虛表特徵）。

3. SHOW CREATE VIEW

SHOW CREATE VIEW stu_class\G

輸出完整建立語句，含 DEFINER、SQL SECURITY、字符集等元資訊。

4. 查詢 INFORMATION_SCHEMA.VIEWS

SELECT * FROM information_schema.VIEWS WHERE TABLE_NAME = 'stu_class';

可獲取 VIEW_DEFINITION、IS_UPDATABLE、SECURITY_TYPE 等詳細屬性。

四、修改視圖

方式一：CREATE OR REPLACE VIEW

CREATE OR REPLACE VIEW stu_class AS
SELECT stuno AS id FROM student JOIN stuinfo ON student.stuno = stuinfo.stuno;

方式二：ALTER VIEW

ALTER VIEW stu_class AS SELECT stuno FROM student;

兩者效果相同，ALTER VIEW 語意更明確。

五、更新視圖（透過視圖修改基表資料）

視圖若符合條件，可直接 INSERT、UPDATE、DELETE，操作會轉發至基表。

-- 先調整視圖包含可更新欄位
ALTER VIEW stu_class AS SELECT stuno, stuname FROM student;

UPDATE stu_class SET stuname='小芳' WHERE stuno=2;
INSERT INTO stu_class VALUES (6,'浩傑');
DELETE FROM stu_class WHERE stuno=1;

不可更新視圖的常見特徵

• 包含非基表欄位（運算式、聚合函數、常數）
• SELECT 含 DISTINCT、UNION、GROUP BY、HAVING
• 基表含無預設值的 NOT NULL 欄位且視圖未包含該欄位
• 使用 TEMPTABLE 演算法或 ALGORITHM=TEMPTABLE

六、刪除視圖

DROP VIEW [IF EXISTS] view_name [, view_name] ...;

支援一次刪除多個視圖，IF EXISTS 避免不存在報錯。

七、進階主題

DEFINER 與 SQL SECURITY

• DEFINER：定義視圖的用戶，預設為建立者（CURRENT_USER）
• SQL SECURITY {DEFINER | INVOKER}：執行權限檢查模式
  DEFINER（預設）：以建立者權限執行
  INVOKER：以呼叫者權限執行

視圖效能考量

• 優先使用 MERGE 演算法，避免臨時表開銷
• 視圖嵌套層級不宜過深（建議 ≤ 3 層）
• 大量資料聚合視圖考慮改用物化表 + 定時刷新

八、總結

MySQL 視圖是資料庫抽象層的重要工具，善用可大幅提升安全性、維護性與開發效率。關鍵要點：

1. 明確選擇 ALGORITHM，優先 MERGE
2. 合理設定 WITH CHECK OPTION 保護資料完整性
3. 理解 DEFINER/INVOKER 權限模型，避免權限提升風險
4. 定期檢視 information_schema.VIEWS.IS_UPDATABLE 確認可更新性