MySQL 檢視表（View） – 可更新與不可更新檢視表

簡介

在日常開發與資料分析的過程中，檢視表（View） 是一個非常實用的工具。它可以把複雜的查詢結果抽象成一個「虛擬表」，讓使用者像操作實體表一樣讀取資料，同時隱藏底層的實作細節。

然而，並不是所有的檢視表都能直接寫入資料。了解 可更新檢視表 與 不可更新檢視表 的差異，能幫助我們在設計資料庫時避免錯誤、提升效能，並正確運用檢視表來完成 CRUD（Create、Read、Update、Delete）工作。

本文將從概念說明、實作範例、常見陷阱與最佳實踐，逐步帶你掌握 MySQL 中檢視表的可更新性，並提供實務應用情境，讓你在專案中更得心應手。

---

核心概念

1. 什麼是可更新檢視表？

可更新檢視表是指 可以直接對檢視表執行 INSERT、UPDATE、DELETE，而 MySQL 會自動將這些變更映射回底層的基礎表（Base Table）。
要成為可更新檢視表，必須符合以下幾個條件（MySQL 8.0 以後的行為）：

條件	說明
單一基礎表	檢視表只能引用 一張 真實的資料表（或另一個已被確定可更新的檢視表）。
不含聚合函數	SUM()、COUNT()、AVG()… 之類的聚合會使結果不具唯一性，因而不可更新。
不使用 DISTINCT	DISTINCT 會去除重複列，導致無法確定要更新哪一筆。
不含 UNION / UNION ALL	多表合併的結果不具唯一映射關係。
不使用子查詢或衍生表（在 SELECT 子句中）	若子查詢返回多列，MySQL 無法確定寫入目標。
主鍵或唯一索引	必須能唯一定位基礎表的列，否則更新會產生歧義。
不含 GROUP BY / HAVING	會產生彙總結果，失去一對一映射。
不使用 LIMIT	會限制返回列數，導致寫入時無法保證完整性。

只要檢視表滿足上述條件，MySQL 會把 INSERT/UPDATE/DELETE 直接轉譯成對基礎表的相同操作。

---

2. 什麼是不可更新檢視表？

當檢視表違反上述任一條件時，就會變成 不可更新。此時，對檢視表執行寫入語句會得到錯誤訊息，例如：

ERROR 1369 (HY000): View 'my_view' is not updatable

不可更新的檢視表仍然可以 讀取，它們的價值在於：

• 抽象化複雜查詢，提高重用性
• 隱藏敏感欄位（只投影需要的欄位）
• 作為報表或資料分析的基礎

如果真的需要寫入，必須透過 INSTEAD OF 觸發器（MySQL 5.7 之後支援）或 存儲程序 來手動把變更映射回基礎表。

---

3. 可更新檢視表的實作方式

3.1 基本可更新檢視表

-- 建立一張簡單的基礎表
CREATE TABLE employee (
    emp_id   INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(50),
    dept_id  INT,
    salary   DECIMAL(10,2)
);

INSERT INTO employee (name, dept_id, salary) VALUES
('Alice', 1, 50000),
('Bob',   2, 45000),
('Carol', 1, 52000);

-- 建立可更新檢視表，只投影需要的欄位
CREATE VIEW emp_simple AS
SELECT emp_id, name, salary
FROM employee;

此時 emp_simple 可直接 UPDATE：

UPDATE emp_simple
SET salary = salary * 1.05
WHERE name = 'Bob';

MySQL 會把上述語句轉為：

UPDATE employee
SET salary = salary * 1.05
WHERE name = 'Bob';

3.2 使用 WITH CHECK OPTION 限制寫入範圍

WITH CHECK OPTION 可以防止使用者在檢視表上插入不符合檢視條件的資料。

CREATE VIEW emp_dept1 AS
SELECT emp_id, name, salary
FROM employee
WHERE dept_id = 1
WITH CHECK OPTION;

INSERT 範例（符合條件）：

INSERT INTO emp_dept1 (name, salary, dept_id)
VALUES ('David', 48000, 1);

INSERT 範例（違反條件，會失敗）：

INSERT INTO emp_dept1 (name, salary, dept_id)
VALUES ('Eve', 47000, 2);   -- ERROR 1369: CHECK OPTION violation

3.3 多層可更新檢視表（檢視表嵌套）

只要每一層都符合可更新條件，就可以形成「檢視表的檢視表」。

-- 第一層檢視表
CREATE VIEW emp_high_salary AS
SELECT emp_id, name, salary
FROM employee
WHERE salary > 50000;

-- 第二層檢視表（仍可更新）
CREATE VIEW emp_high_salary_name AS
SELECT emp_id, name
FROM emp_high_salary;

執行：

UPDATE emp_high_salary_name
SET name = 'Catherine'
WHERE emp_id = 3;

MySQL 會沿著層級向下映射，最終更新 employee 表。

3.4 使用 INSTEAD OF 觸發器為不可更新檢視表提供寫入能力

當檢視表必須包含聚合或 JOIN，但仍希望以檢視表方式寫入時，可透過觸發器自行處理。

-- 建立兩張表作為範例
CREATE TABLE sales (
    sale_id   INT PRIMARY KEY AUTO_INCREMENT,
    prod_id   INT,
    qty       INT,
    price     DECIMAL(10,2)
);

CREATE TABLE product (
    prod_id   INT PRIMARY KEY,
    prod_name VARCHAR(50)
);

INSERT INTO product VALUES (1,'Pen'),(2,'Notebook');
INSERT INTO sales (prod_id, qty, price) VALUES (1,10,1.5),(2,5,3.0);

-- 建立不可更新的檢視表（含 JOIN）
CREATE VIEW v_sales_summary AS
SELECT p.prod_name,
       SUM(s.qty) AS total_qty,
       SUM(s.qty * s.price) AS total_amount
FROM sales s
JOIN product p ON s.prod_id = p.prod_id
GROUP BY p.prod_name;

此檢視表不可直接 INSERT，於是建立 INSTEAD OF INSERT 觸發器：

DELIMITER $$

CREATE TRIGGER trg_ins_v_sales_summary
INSTEAD OF INSERT ON v_sales_summary
FOR EACH ROW
BEGIN
    -- 假設只允許新增單筆銷售紀錄
    INSERT INTO sales (prod_id, qty, price)
    SELECT prod_id, NEW.total_qty, NEW.total_amount / NEW.total_qty
    FROM product
    WHERE prod_name = NEW.prod_name;
END$$

DELIMITER ;

現在可以這樣寫入：

INSERT INTO v_sales_summary (prod_name, total_qty, total_amount)
VALUES ('Pen', 20, 30.0);

觸發器會把資料拆解、寫入 sales 表，完成「看似」的寫入操作。

---

常見陷阱與最佳實踐

陷阱	說明	解決方式
使用 SELECT *	直接把所有欄位投影進檢視表，若未來基礎表結構變更（新增欄位），檢視表可能變成不可更新。	明確列出需要的欄位，並在變更時同步檢查檢視表。
缺少唯一鍵	若檢視表沒有包含基礎表的主鍵或唯一索引，MySQL 無法定位要更新的列。	在檢視表中 保留主鍵欄位，或使用 ROW_NUMBER() 產生唯一值（注意不可更新性）。
混合多表 JOIN	多表 JOIN 的檢視表通常不可直接更新，除非使用 INSTEAD OF 觸發器。	僅在讀取需求時使用 JOIN，寫入時直接操作單一基礎表或使用存儲過程。
使用 GROUP BY / 聚合函數	產生彙總結果，失去一對一映射。	若必須彙總，改用 存儲過程 或 觸發器 處理寫入；或將彙總結果放在 只讀檢視表。
忘記 WITH CHECK OPTION	使用者可能在檢視表上插入不符合條件的資料，導致資料不一致。	為所有有條件限制的檢視表加上 WITH CHECK OPTION。
過度使用可更新檢視表	每次寫入都會觸發底層的查詢與重寫，可能影響效能。	只在 抽象化 與 安全性 需求明顯時使用；大量寫入時直接操作基礎表。

最佳實踐

1. 明確列出欄位：永遠使用 SELECT col1, col2 …，避免 SELECT *。
2. 保留主鍵：讓檢視表包含基礎表的主鍵或唯一索引。
3. 使用 WITH CHECK OPTION：保護資料完整性。
4. 分層設計：先建立 只讀的彙總檢視表，再根據需求建立 可更新的細部檢視表。
5. 測試可更新性：使用 SHOW CREATE VIEW view_name; 檢查是否包含 ALGORITHM=MERGE（對可更新檢視表而言較常見）以及 SQL SECURITY DEFINER。
6. 文件化：在專案文件中標註每個檢視表的可更新性與使用限制，方便團隊協作。

---

實際應用場景

場景	為何使用檢視表	可更新或只讀
員工自助系統	員工只能看到自己部門的薪資資訊，且只能更新聯絡方式。	建立 部門過濾檢視表，加 WITH CHECK OPTION，允許 UPDATE。
報表系統	月度銷售彙總需要多表 JOIN 與 GROUP BY，不允許直接寫入。	建立 只讀彙總檢視表，配合 BI 工具查詢。
多租戶 SaaS	每個租戶只能操作自己資料，且資料庫結構共用。	用 tenant_id 條件建立 租戶篩選檢視表，加 WITH CHECK OPTION，允許 CRUD。
資料遷移或清理	需要一次性把舊資料搬到新表，且希望使用相同的 INSERT 語法。	建立 臨時可更新檢視表 指向舊表，完成搬移後直接刪除。
權限抽象	前端只需要讀取特定欄位，避免暴露敏感欄位。	建立 只讀檢視表，投影必要欄位，前端只對此檢視表查詢。

---

總結

• 可更新檢視表 讓開發者在保持資料抽象的同時，仍能直接執行 INSERT/UPDATE/DELETE，前提是必須滿足單表、無聚合、保留唯一鍵等條件。
• 不可更新檢視表 常用於彙總、跨表查詢或安全性遮蔽，若真的需要寫入，可透過 INSTEAD OF 觸發器 或 存儲程序 手動映射。
• 在實務開發中，務必 明確列出欄位、保留主鍵、加上 CHECK OPTION，並在設計文件中註明每個檢視表的可更新性與限制。
• 正確運用檢視表可以提升 代碼可讀性、降低重複查詢、加強資料安全，同時避免因不當使用而產生的效能與資料一致性問題。

掌握了可更新與不可更新檢視表的原理與最佳實踐後，你就能在 MySQL 專案中更靈活地設計資料模型，讓資料存取既安全又高效。祝你寫碼順利！