MySQL 視圖深度解析：創建、查詢簡化、權限控制與可更新性規則

什麼是視圖

視圖是一個**命名的 SELECT 查詢**，不存儲物理數據（虛表）。查詢視圖時，MySQL 將視圖定義合併到外層查詢中重寫執行。基表數據變更，視圖即時反映。

核心價值

1. 簡化複雜查詢：將多表 JOIN、子查詢、聚合封裝為單表訪問
2. 行列級權限控制：GRANT 權限於視圖而非基表，隱藏敏感列（如 salary）、過濾敏感行
3. 邏輯解耦：基表結構變更時，僅調整視圖定義，應用層 SQL 無感

創建視圖語法

CREATE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
     [DEFINER = user]
     [SQL SECURITY {DEFINER | INVOKER}]
     VIEW view_name [(col_list)]
     AS select_statement
     [WITH [CASCADED | LOCAL] CHECK OPTION];

關鍵選項

• ALGORITHM=MERGE（默認）：視圖定義合併到外層查詢，**可使用索引**、性能最優
• ALGORITHM=TEMPTABLE：先將視圖結果物化到臨時表再查詢，**不可用索引**、用於不可合併的聚合/子查詢
• SQL SECURITY DEFINER：以創建者權限執行（可繞過調用者權限限制）
• WITH CHECK OPTION：插入/更新時強制滿足視圖 WHERE 條件，防止「更新穿透」導致數據不再可見

實戰示例：多表關聯視圖

基表

-- user, course, user_course (中間表)
-- 數據見原文

創建視圖

CREATE VIEW view_user_course AS
SELECT uc.id, u.name AS username, c.name AS coursename
FROM user u
LEFT JOIN user_course uc ON u.id = uc.userid
LEFT JOIN course c ON uc.courseid = c.id;

查詢簡化

-- 原來需寫三表 JOIN
SELECT username, coursename FROM view_user_course WHERE username = '小張';

視圖可更新性規則（關鍵！）

**並非所有視圖都可增刪改**，規則如下：

驗證示例

-- 可更新：單表視圖
CREATE VIEW view_user_keyinfo AS SELECT id, account, name FROM user;
INSERT INTO view_user_keyinfo (account, name) VALUES ('t','t');  -- ✅
UPDATE view_user_keyinfo SET name='new' WHERE id=1;              -- ✅

-- 受限：多表 JOIN 視圖
UPDATE view_user_course SET coursename='JAVA' WHERE id=1;        -- ✅ (只改 course 表)
UPDATE view_user_course SET username='x' WHERE id=3;             -- ✅ (只改 user 表)
UPDATE view_user_course SET username='x', coursename='y' WHERE id=3; -- ❌ 同時改兩表
DELETE FROM view_user_course WHERE id=3;                         -- ❌
INSERT INTO view_user_course (username, coursename) VALUES('a','b'); -- ❌

權限控制實戰

-- 僅授予視圖 SELECT 權限，隱藏 salary、address 等敏感列
GRANT SELECT ON view_user_keyinfo TO 'report_user'@'%';
-- report_user 無法訪問 user 表，也看不到被排除的列

性能優化提示

• 優先使用 ALGORITHM=MERGE，確保視圖定義可合併（無聚合、DISTINCT、LIMIT 等）
• 視圖上的 WHERE 條件下推依賴優化器，**為基表關鍵列建立索引**
• 避免在視圖上再建視圖（嵌套視圖），合併深度過大優化器可能放棄 MERGE
• EXPLAIN SELECT * FROM view_name 檢查是否使用索引、是否產生臨時表

總結

視圖是「SQL 的函數」：**封裝複雜性、隔離權限、解耦結構**。掌握可更新性邊界，用 MERGE 算法，配合索引，視圖既安全又高效。