MySQL 視圖完整指南：創建、修改、更新限制、權限控制與刪除

MySQL 視圖完整指南：創建、修改、更新限制、權限控制與刪除

視圖是虛擬表，不儲存數據，僅儲存 SELECT 定義。調用時即時執行派生數據。優點：簡化複雜查詢、權限隔離（隱藏敏感列）、邏輯復用。本文系統覆蓋視圖全生命周期操作。

一、創建視圖

-- 基本語法
CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] 
VIEW view_name [(column_list)] 
AS SELECT_statement 
[WITH [CASCADED|LOCAL] CHECK OPTION];

參數解析

實戰範例

-- 1. 簡化聚合查詢
CREATE VIEW avgPrice AS 
SELECT cat_id, AVG(shop_price) AS avg_price 
FROM goods GROUP BY cat_id;

SELECT * FROM avgPrice;  -- 直接查詢，無需寫 GROUP BY

-- 2. 權限控制：隱藏價格
CREATE VIEW baseGoods AS 
SELECT goods_id, goods_name FROM goods;

GRANT SELECT ON baseGoods TO 'readonly_user'@'%';
-- 用戶只能看到 id 和名稱，無法訪問 shop_price

-- 3. 帶檢查選項
CREATE VIEW highPriceGoods AS 
SELECT * FROM goods WHERE shop_price > 100
WITH CHECK OPTION;

-- 插入價格 50 會失敗（不符合視圖條件）
INSERT INTO highPriceGoods VALUES (99, 'Cheap', 50);  -- ERROR 1369

二、查看視圖

DESCRIBE view_name;              -- 結構
SHOW CREATE VIEW view_name;      -- 定義 SQL
SHOW TABLE STATUS LIKE 'view_name';  -- 類型顯示 VIEW
SELECT * FROM information_schema.VIEWS WHERE TABLE_NAME='view_name';

三、修改視圖

-- 方式一：CREATE OR REPLACE（不存在則創建）
CREATE OR REPLACE VIEW avgPrice AS 
SELECT cat_id, goods_name, AVG(shop_price) FROM goods GROUP BY cat_id;

-- 方式二：ALTER VIEW（必須存在）
ALTER VIEW avgPrice AS 
SELECT cat_id, goods_name, AVG(shop_price) FROM goods GROUP BY cat_id;

四、更新視圖（可更新視圖的條件）

視圖更新會轉換為基表更新。以下情況視圖不可更新：

-- 可更新視圖範例
CREATE VIEW activeUsers AS 
SELECT id, name, email FROM users WHERE status=1
WITH CHECK OPTION;

UPDATE activeUsers SET email='new@test.com' WHERE id=1;  -- 成功，基表同步更新
INSERT INTO activeUsers (name, email) VALUES ('Tom', 'tom@test.com');  -- 成功，status 自動為 1

五、刪除視圖

DROP VIEW [IF EXISTS] view_name1 [, view_name2 ...] [RESTRICT|CASCADE];
-- IF EXISTS：避免不存在報錯
-- RESTRICT/CASCADE：MySQL 忽略，保留兼容性

六、權限控制

-- 查看用戶視圖權限
SELECT Select_priv, Create_view_priv, Drop_priv 
FROM mysql.user WHERE user='username';

-- 授予視圖權限
GRANT SELECT, INSERT, UPDATE ON db.view_name TO 'user'@'host';

-- 視圖定義者權限 (DEFINER)
CREATE DEFINER = 'root'@'localhost' VIEW v AS SELECT * FROM t;
-- SQL SECURITY {DEFINER|INVOKER} 決定執行時權限檢查對象

七、索引與視圖的關係

• 視圖本身**不支援索引**（MySQL 5.7+ 不支援物化視圖）
• 查詢視圖時，優化器嘗試將視圖定義合併到外層查詢（MERGE 算法），從而利用基表索引
• ALGORITHM=TEMPTABLE 會阻止索引合併，效能較差
• 建議：視圖定義中盡量不使用聚合/子查詢，保持 MERGE 算法可用