MySQL 5.1+ 引入視圖功能，本文提供視圖全生命周期操作指南：完整創建語法、三大算法選擇、OR REPLACE 修改技巧、CHECK OPTION 約束、刪除與查看命令、10 大創建限制、實戰案例（產品-採購明細視圖），幫你從零掌握視圖開發。

創建視圖完整語法

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

關鍵子句詳解

OR REPLACE

視圖存在時覆蓋，等同 DROP + CREATE，需 DROP VIEW 權限

ALGORITHM

MERGE：合併重寫，可更新，性能優；TEMPTABLE：物化臨時表，不可更新，釋放鎖快；UNDEFINED：自動選擇（默認）

column_list

自定義輸出列名，數量順序需與 SELECT 列一致

WITH CHECK OPTION

防止插入/更新不符合視圖 WHERE 條件的行；CASCADED（默認）向下傳遞，LOCAL 僅當前視圖

實戰案例：產品採購明細視圖

建表與數據

CREATE TABLE product (
    product_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    price DOUBLE NOT NULL
);
INSERT INTO product VALUES (1, 'apple', 5.5);

CREATE TABLE purchase (
    id INT NOT NULL,
    product_id INT NOT NULL,
    qty INT NOT NULL DEFAULT 0,
    gen_time DATETIME NOT NULL
);
INSERT INTO purchase VALUES (1, 1, 10, NOW());

創建視圖（自定義列名）

CREATE OR REPLACE VIEW purchase_detail AS
SELECT
    p.name AS name,
    p.price AS price,
    pu.qty AS qty,
    p.price * pu.qty AS total_value
FROM product p
JOIN purchase pu ON p.product_id = pu.product_id;

查詢視圖

SELECT * FROM purchase_detail;
+-------+-------+-----+-------------+
| name  | price | qty | total_value |
+-------+-------+-----+-------------+
| apple |   5.5 |  10 |          55 |
+-------+-------+-----+-------------+

修改視圖（去掉 qty 列）

ALTER VIEW purchase_detail AS
SELECT
    p.name AS name,
    p.price AS price,
    p.price * pu.qty AS total_value
FROM product p
JOIN purchase pu ON p.product_id = pu.product_id;

視圖管理命令

-- 刪除單個/多個視圖
DROP VIEW [IF EXISTS] purchase_detail;
DROP VIEW IF EXISTS v1, v2, v3;

-- 查看視圖定義（格式化輸出）
SHOW CREATE VIEW purchase_detail\G

-- 區分視圖與基表
SHOW FULL TABLES;
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| product        | BASE TABLE |
| purchase       | BASE TABLE |
| purchase_detail| VIEW       |
+----------------+------------+

10 大創建限制（必讀）

1. 需 CREATE VIEW 權限，OR REPLACE 額外需 DROP VIEW
2. SELECT 不能包含 FROM 子查詢（5.7+ 已放寬）
3. 不能引用系統/用戶變量（@var）
4. 不能引用預處理參數
5. 存儲過程中不能引用過程參數/局部變量
6. 引用的基表/視圖必須存在（創建後可刪除基表，視圖失效）
7. 不能引用 TEMPORARY 表，不能創建臨時視圖
8. 基表必須在定義時存在
9. 不能關聯觸發器
10. 視圖內 ORDER BY 若外層也有 ORDER BY 會被忽略

可更新視圖判斷清單

僅當滿足 全部 條件時可 INSERT/UPDATE/DELETE 視圖：

• 算法為 MERGE
• 無聚合/去重/分組/UNION
• 無子查詢、無 JOIN
• 所有列直接來自單一基表（非表達式）
• 基表無未在視圖中且無默認值的 NOT NULL 列

最佳實踐建議

• 命名規範：v_ 前綴
• 權限最小化：GRANT SELECT ON db.v_view TO 'app'@'%';
• 複雜報表強制 ALGORITHM=TEMPTABLE 減少鎖持有時間
• 定期 CHECK TABLE v_name 檢測基表缺失導致的視圖失效

視圖是邏輯解耦層，善用它讓業務 SQL 更簡潔、安全、可維護。