在現代資訊系統中，資料安全與隱私保護已經成為不可忽視的核心議題。隨著台灣個人資料保護法的施行、歐盟 GDPR（General Data Protection Regulation）的嚴格要求，以及各產業監管機構對資料治理的重視，企業必須在提供資料存取便利性的同時，確保敏感資訊得到適當的保護。資料庫檢視（Database View）作為一種強大的資料抽象化機制，提供了在資料庫層面實現細緻化存取控制的理想解決方案。
資料庫檢視本質上是一個預先定義的 SQL 查詢結果，它以虛擬表格的形式呈現，使用者可以像查詢一般表格一樣查詢檢視，但檢視本身並不實際儲存資料。每當使用者查詢檢視時，資料庫管理系統會動態執行檢視定義中的 SELECT 語句，從底層的基礎表格中擷取資料，並將結果呈現給使用者。這種虛擬性使得檢視成為實現資料安全與存取控制的絕佳工具，因為我們可以精確控制哪些資料欄位對哪些使用者可見，而不需要修改底層的表格結構或複製資料。
在實務應用中，檢視的價值遠不止於資料隱藏。它還可以用於簡化複雜的多表格聯結查詢、封裝業務邏輯、提供穩定的資料存取介面，以及支援應用程式與資料庫之間的鬆耦合架構。當底層表格結構需要調整時，只要維護檢視的定義，應用程式通常不需要做任何修改，這大幅降低了系統維護的成本與風險。此外，透過建立多層次的檢視體系，我們可以為不同角色的使用者提供適當的資料視角，既滿足各自的業務需求，又確保資料安全性。
本文將以企業投訴管理系統為實務案例，深入探討資料庫檢視的各種應用技術。我們將從基礎的敏感欄位隱藏開始，逐步介紹資料部分遮罩、複雜查詢封裝、可更新檢視的設計、巢狀檢視的應用，以及效能最佳化策略。每個概念都會搭配完整的 SQL 程式碼範例與詳細的註解說明，讓讀者能夠直接應用於實際專案中。無論您是資料庫管理員、後端開發者，還是資料安全工程師，本文都將為您提供實用的技術知識與最佳實踐建議。
資料庫檢視的核心概念與架構設計
資料庫檢視是一種特殊的資料庫物件，它透過 SQL 查詢定義了一個虛擬的表格結構。與實體表格不同，檢視不儲存任何資料，而是在每次被查詢時動態產生結果。這種設計帶來了顯著的優勢，包含資料的即時性（檢視總是反映底層表格的最新狀態）、儲存空間的節省（不需要額外的儲存空間），以及維護的便利性（只需要在一個地方定義資料邏輯）。
從架構設計的角度來看，檢視在應用程式與資料庫之間扮演了資料存取層的角色。應用程式不直接查詢基礎表格，而是透過檢視來存取資料。這種間接存取機制創造了一個抽象層，讓我們能夠在不影響應用程式的前提下，調整底層的資料庫結構。例如，當我們需要將一個表格分割成多個子表格以提升效能時，只要重新定義檢視的 SQL 語句，應用程式就能繼續正常運作，完全不會察覺底層結構的變化。
在資料安全方面，檢視提供了多層次的保護機制。最基本的應用是欄位級別的存取控制，透過在檢視定義中選擇性地包含或排除特定欄位，我們可以確保敏感資料不會暴露給未授權的使用者。更進階的應用包含資料列層級的過濾，根據使用者的身分或角色，只顯示他們有權限查看的資料列。此外，我們還可以實作資料遮罩，將敏感資料以部分隱藏或轉換的形式呈現，既保護隱私又提供必要的資訊。
以下圖表展示了檢視在多層次資料存取架構中的角色與運作機制：
@startuml
!define PLANTUML_FORMAT svg
!theme _none_
skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 14
skinparam minClassWidth 100
package "應用程式層" {
actor "一般使用者" as User1
actor "客服人員" as User2
actor "系統管理員" as Admin
}
package "檢視層（資料存取控制）" {
component "公開資料檢視\nv_public_data" as View1 {
note right
隱藏所有敏感欄位
只顯示公開資訊
end note
}
component "業務資料檢視\nv_business_data" as View2 {
note right
部分遮罩敏感欄位
顯示必要業務資訊
end note
}
component "完整資料檢視\nv_full_data" as View3 {
note right
完整資料存取
包含所有欄位
end note
}
}
package "資料層（實體表格）" {
database "company\n公司資訊表" as T1 {
note bottom
包含敏感欄位:
- 電話號碼
- 電子郵件
- 身分證字號
end note
}
database "complaint\n投訴記錄表" as T2
database "customer\n客戶資料表" as T3
}
User1 --> View1 : 查詢公開資訊
User2 --> View2 : 查詢業務資料
Admin --> View3 : 完整權限查詢
View1 --> T1 : SELECT 非敏感欄位
View1 --> T2
View2 --> T1 : SELECT 部分遮罩
View2 --> T2
View2 --> T3
View3 --> T1 : SELECT *
View3 --> T2
View3 --> T3
@enduml檢視的執行機制涉及查詢重寫（Query Rewriting）與最佳化過程。當使用者對檢視執行查詢時，資料庫管理系統會將使用者的查詢與檢視定義中的 SQL 語句合併，產生一個針對基礎表格的完整查詢。這個合併後的查詢會經過查詢最佳化器的處理，產生最有效率的執行計畫。這意味著透過檢視查詢資料，在大多數情況下不會比直接查詢基礎表格慢，特別是當基礎表格有適當的索引支援時。
然而，檢視的使用也需要謹慎設計。過於複雜的檢視定義，特別是包含多層巢狀子查詢或大量表格聯結的檢視，可能會產生效能問題。此外，某些類型的檢視無法用於資料更新操作，這在設計系統架構時需要考慮。理解檢視的限制與最佳實踐，對於建構高效且可維護的資料存取層至關重要。
投訴管理系統的資料模型設計
為了充分展示檢視的各種應用技術，我們需要一個具有實務代表性的資料模型。投訴管理系統是一個典型的企業應用場景，涉及多個實體之間的關聯關係，且包含敏感的個人資料，非常適合作為檢視應用的示範案例。
在這個系統中，我們有三個核心實體：公司資訊、投訴記錄與客戶資料。公司資訊表格儲存了被投訴企業的基本資料，包含公司名稱、業主姓名、聯絡方式等。其中，業主的電話號碼、電子郵件地址等屬於敏感的個人資料，需要妥善保護。投訴記錄表格儲存了客戶提出的各項投訴內容、投訴日期、處理狀態與解決方案。客戶資料表格則包含投訴人的基本資訊。
這個資料模型的設計考慮了典型的業務需求與安全要求。一般使用者（如網站訪客）應該能夠查看投訴統計資訊，但不能看到業主的聯絡方式。客服人員需要處理投訴，可能需要看到部分遮罩的聯絡資訊以確認身分，但不應該看到完整的資料。只有系統管理員才能存取完整的資料，包含所有敏感欄位。透過檢視機制，我們可以為這三種角色建立不同的資料視角，實現細緻化的存取控制。
-- ============================================================
-- 建立投訴管理系統的資料表格
-- ============================================================
-- 此系統包含公司資訊、投訴記錄與客戶資料三個核心實體
-- ============================================================
-- 表格 1: 公司資訊（包含敏感的業主聯絡資訊）
-- ============================================================
CREATE TABLE company (
-- 主鍵：公司的唯一識別碼
-- 使用 AUTO_INCREMENT 自動產生遞增的 ID
company_id INT PRIMARY KEY AUTO_INCREMENT,
-- 公司名稱
-- 設定為 NOT NULL 確保每個記錄都有明確的公司名稱
company_name VARCHAR(100) NOT NULL,
-- 業主姓名
-- 記錄公司負責人或業主的姓名
owner VARCHAR(100) NOT NULL,
-- 業主電話號碼（敏感資料）
-- 這是需要保護的個人資料
-- 格式範例：0912-345-678
owner_phone_number VARCHAR(20),
-- 業主電子郵件（敏感資料）
-- 用於聯絡業主的電子郵件地址
owner_email VARCHAR(100),
-- 業主身分證字號（高敏感資料）
-- 台灣身分證字號格式：A123456789
-- 這是最敏感的個人資料，需要最高等級的保護
owner_id_number CHAR(10),
-- 公司地址
-- 相對不敏感的資訊，可以公開
address VARCHAR(200),
-- 公司類型
-- 例如：零售業、服務業、製造業等
business_type VARCHAR(50),
-- 公司規模（員工數）
employee_count INT,
-- 記錄建立時間
-- 自動記錄資料建立的時間戳記
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- 記錄最後更新時間
-- 每次更新時自動記錄新的時間戳記
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 建立索引以提升查詢效能
INDEX idx_company_name (company_name),
INDEX idx_business_type (business_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='公司資訊表，包含業主的敏感個人資料';
-- ============================================================
-- 表格 2: 投訴記錄
-- ============================================================
CREATE TABLE complaint (
-- 主鍵：投訴記錄的唯一識別碼
complaint_id INT PRIMARY KEY AUTO_INCREMENT,
-- 外鍵：被投訴的公司
-- 關聯到 company 表格的 company_id
company_id INT NOT NULL,
-- 投訴描述
-- 使用 TEXT 型態以儲存較長的投訴內容
complaint_desc TEXT NOT NULL,
-- 投訴日期
-- 記錄投訴發生的日期
complaint_date DATE NOT NULL,
-- 處理狀態
-- 可能的值：pending（待處理）、processing（處理中）、
-- resolved（已解決）、closed（已關閉）
status VARCHAR(20) DEFAULT 'pending',
-- 嚴重程度
-- low（低）、medium（中）、high（高）、critical（緊急）
severity VARCHAR(20) DEFAULT 'medium',
-- 投訴類型
-- 例如：服務品質、產品問題、價格爭議、態度不佳等
complaint_type VARCHAR(50),
-- 處理結果說明
-- 記錄問題如何被解決
resolution TEXT,
-- 處理完成日期
-- 投訴被解決的日期
resolved_date DATE,
-- 負責處理的客服人員
assigned_to VARCHAR(100),
-- 記錄建立時間
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- 記錄最後更新時間
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 外鍵約束
-- RESTRICT 防止刪除仍有投訴記錄的公司
-- CASCADE 當公司資料更新時，同步更新投訴記錄中的關聯
FOREIGN KEY (company_id) REFERENCES company(company_id)
ON DELETE RESTRICT
ON UPDATE CASCADE,
-- 建立索引
INDEX idx_complaint_company (company_id),
INDEX idx_complaint_status (status),
INDEX idx_complaint_date (complaint_date),
INDEX idx_complaint_type (complaint_type),
INDEX idx_severity (severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='投訴記錄表，儲存客戶對公司的各項投訴';
-- ============================================================
-- 表格 3: 客戶資料（投訴人資訊）
-- ============================================================
CREATE TABLE customer (
-- 主鍵：客戶唯一識別碼
customer_id INT PRIMARY KEY AUTO_INCREMENT,
-- 客戶姓名
customer_name VARCHAR(100) NOT NULL,
-- 客戶電話（敏感資料）
customer_phone VARCHAR(20),
-- 客戶電子郵件（敏感資料）
customer_email VARCHAR(100),
-- 客戶地址
customer_address VARCHAR(200),
-- 記錄建立時間
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_customer_name (customer_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='客戶資料表，儲存投訴人的基本資訊';
-- ============================================================
-- 插入範例資料
-- ============================================================
-- 這些範例資料用於測試檢視的各種功能
-- 插入公司資料
INSERT INTO company (
company_name,
owner,
owner_phone_number,
owner_email,
owner_id_number,
address,
business_type,
employee_count
)
VALUES
(
'萬能通訊股份有限公司',
'張三豐',
'0912-345-678',
'chang@wn-telecom.com.tw',
'A123456789',
'台北市信義區信義路五段100號12樓',
'電信服務業',
150
),
(
'金融信託銀行',
'李四海',
'0923-456-789',
'lee@jf-bank.com.tw',
'B234567890',
'台北市中山區南京東路三段200號',
'金融業',
500
),
(
'友善當舖',
'王五福',
'0934-567-890',
'wang@friendly-pawn.com',
'C345678901',
'新北市板橋區中山路一段300號',
'典當業',
8
),
(
'永續環保科技有限公司',
'陳六明',
'0945-678-901',
'chen@eco-tech.com.tw',
'D456789012',
'台中市西屯區工業區一路50號',
'環保科技業',
80
);
-- 插入投訴記錄
INSERT INTO complaint (
company_id,
complaint_desc,
complaint_date,
status,
severity,
complaint_type,
resolution,
resolved_date,
assigned_to
)
VALUES
(
1,
'手機收訊不良，在地下室完全無法接收訊號，嚴重影響工作',
'2024-01-15',
'resolved',
'high',
'服務品質',
'已安排技術人員檢測並增設基地台',
'2024-01-20',
'客服專員A'
),
(
1,
'網路連線經常不穩定，下載速度遠低於合約承諾',
'2024-01-20',
'processing',
'medium',
'服務品質',
NULL,
NULL,
'客服專員B'
),
(
1,
'客服人員態度不佳，電話等待時間過長',
'2024-02-01',
'pending',
'low',
'態度不佳',
NULL,
NULL,
NULL
),
(
2,
'分行營業時間太短，下班後無法辦理業務',
'2024-01-25',
'resolved',
'medium',
'服務時間',
'已延長營業時間至晚上8點',
'2024-02-01',
'客服專員C'
),
(
2,
'網路銀行系統經常當機，無法進行轉帳作業',
'2024-02-15',
'processing',
'critical',
'系統問題',
NULL,
NULL,
'技術主管A'
),
(
3,
'典當商品估價不公，明顯低於市場行情',
'2024-02-05',
'pending',
'medium',
'價格爭議',
NULL,
NULL,
NULL
),
(
3,
'贖回商品時發現商品有損壞，店家不願負責',
'2024-02-10',
'pending',
'high',
'商品問題',
NULL,
NULL,
'客服專員D'
),
(
4,
'回收服務預約後長期未處理，影響作業時程',
'2024-02-20',
'processing',
'medium',
'服務延遲',
NULL,
NULL,
'客服專員E'
);
-- 插入客戶資料
INSERT INTO customer (
customer_name,
customer_phone,
customer_email,
customer_address
)
VALUES
('林小明', '0987-654-321', 'lin@email.com', '台北市大安區'),
('陳大華', '0976-543-210', 'chen@email.com', '新北市新店區'),
('黃美麗', '0965-432-109', 'huang@email.com', '桃園市中壢區');
在建立這些表格後，我們就有了一個包含敏感資料的完整資料模型。接下來我們將透過各種類型的檢視來示範如何保護這些敏感資料，同時提供必要的業務資訊存取能力。
建立隱藏敏感欄位的基礎檢視
檢視最直接的應用就是選擇性地顯示表格中的欄位，將敏感資料完全排除在檢視之外。這種方式適用於完全不需要看到敏感資料的使用者，例如網站的一般訪客或是只需要查看統計資訊的分析人員。
在我們的投訴管理系統中，公開檢視應該讓一般使用者能夠查看各公司的投訴統計資訊，以便消費者在選擇服務提供者時有參考依據。然而，業主的電話號碼、電子郵件、身分證字號等個人資料不應該被公開。同時，為了提供更有價值的資訊，我們可以在檢視中整合投訴統計資料，讓使用者一次查詢就能獲得完整的資訊。
-- ============================================================
-- 建立公開投訴統計檢視
-- ============================================================
-- 此檢視完全隱藏業主的敏感聯絡資訊
-- 提供公司基本資訊與投訴統計數據
-- 適用角色：一般網站訪客、消費者查詢
CREATE VIEW v_complaint_public_statistics AS
SELECT
-- ============================================================
-- 公司基本資訊（非敏感欄位）
-- ============================================================
c.company_id,
c.company_name AS 公司名稱,
c.owner AS 業主姓名,
c.address AS 公司地址,
c.business_type AS 業務類型,
c.employee_count AS 員工人數,
-- 注意：完全不包含以下敏感欄位
-- - owner_phone_number（業主電話）
-- - owner_email（業主電子郵件）
-- - owner_id_number（業主身分證字號）
-- ============================================================
-- 投訴統計指標
-- ============================================================
-- 計算該公司的投訴總數
COUNT(cp.complaint_id) AS 投訴總數,
-- 計算各狀態的投訴數量
-- 使用 CASE 表達式進行條件計數
SUM(CASE WHEN cp.status = 'pending' THEN 1 ELSE 0 END) AS 待處理數,
SUM(CASE WHEN cp.status = 'processing' THEN 1 ELSE 0 END) AS 處理中數,
SUM(CASE WHEN cp.status = 'resolved' THEN 1 ELSE 0 END) AS 已解決數,
SUM(CASE WHEN cp.status = 'closed' THEN 1 ELSE 0 END) AS 已關閉數,
-- 計算各嚴重程度的投訴數量
SUM(CASE WHEN cp.severity = 'critical' THEN 1 ELSE 0 END) AS 緊急投訴數,
SUM(CASE WHEN cp.severity = 'high' THEN 1 ELSE 0 END) AS 高嚴重度數,
SUM(CASE WHEN cp.severity = 'medium' THEN 1 ELSE 0 END) AS 中嚴重度數,
SUM(CASE WHEN cp.severity = 'low' THEN 1 ELSE 0 END) AS 低嚴重度數,
-- 計算投訴解決率（百分比）
-- 使用 CASE 避免除以零的錯誤
CASE
WHEN COUNT(cp.complaint_id) > 0 THEN
ROUND(
SUM(CASE WHEN cp.status = 'resolved' THEN 1 ELSE 0 END) * 100.0 /
COUNT(cp.complaint_id),
1
)
ELSE NULL
END AS 解決率,
-- 計算最近一次投訴的日期
-- 使用 MAX 函式取得最新的日期
MAX(cp.complaint_date) AS 最近投訴日期,
-- 計算最舊一次投訴的日期
MIN(cp.complaint_date) AS 首次投訴日期,
-- 計算平均處理天數（僅針對已解決的投訴）
ROUND(
AVG(
CASE
WHEN cp.status = 'resolved' AND cp.resolved_date IS NOT NULL
THEN DATEDIFF(cp.resolved_date, cp.complaint_date)
ELSE NULL
END
),
1
) AS 平均處理天數
FROM
-- 主表格：company（公司資訊）
company c
-- LEFT JOIN 確保即使沒有投訴的公司也會顯示
-- 這些公司的投訴統計會顯示為 0 或 NULL
LEFT JOIN complaint cp ON c.company_id = cp.company_id
-- 按公司分組以計算每個公司的統計數據
GROUP BY
c.company_id,
c.company_name,
c.owner,
c.address,
c.business_type,
c.employee_count;
-- ============================================================
-- 使用檢視進行查詢
-- ============================================================
-- 範例 1: 查詢所有公司的投訴統計，按投訴總數排序
SELECT
公司名稱,
業務類型,
投訴總數,
待處理數,
已解決數,
解決率,
最近投訴日期
FROM
v_complaint_public_statistics
ORDER BY
投訴總數 DESC;
-- 範例 2: 查詢投訴總數超過 2 件的公司
SELECT
公司名稱,
業主姓名,
投訴總數,
緊急投訴數,
解決率
FROM
v_complaint_public_statistics
WHERE
投訴總數 > 2
ORDER BY
緊急投訴數 DESC, 投訴總數 DESC;
-- 範例 3: 查詢特定業務類型的投訴統計
SELECT
公司名稱,
投訴總數,
解決率,
平均處理天數
FROM
v_complaint_public_statistics
WHERE
業務類型 = '電信服務業'
ORDER BY
解決率 ASC;
這個公開檢視完全隱藏了所有敏感的個人資料，使用者無法透過這個檢視看到業主的聯絡方式或身分證字號。同時，檢視整合了豐富的統計資訊，讓使用者能夠全面了解各公司的投訴處理情況。透過這種方式，我們在保護隱私的前提下，提供了有價值的公開資訊。
在實務應用中，我們可以授予網站應用程式的資料庫使用者帳號對這個檢視的 SELECT 權限，而不授予對底層 company 表格的直接存取權限。這樣即使應用程式存在 SQL 注入漏洞，攻擊者也無法透過應用程式存取敏感的個人資料。
實作資料部分遮罩的進階檢視
在某些業務場景中，完全隱藏敏感資料可能不符合需求。例如，客服人員在處理投訴時，可能需要確認業主的身分，但基於最小權限原則，他們不應該看到完整的聯絡資訊。資料遮罩（Data Masking）技術提供了折衷方案，透過部分隱藏或轉換敏感資料，既保護隱私又提供必要的資訊。
常見的資料遮罩方式包含電話號碼只顯示後四碼、電子郵件只顯示第一個字元與網域、身分證字號只顯示開頭與結尾等。這些遮罩方式能讓使用者確認資料的存在與部分內容，同時防止完整資訊的洩露。在我們的投訴管理系統中，業務處理檢視適合使用這種部分遮罩的設計。
-- ============================================================
-- 建立部分遮罩敏感資料的業務檢視
-- ============================================================
-- 此檢視對敏感資料進行部分遮罩處理
-- 適用角色：客服人員、業務處理人員
CREATE VIEW v_company_business_masked AS
SELECT
-- ============================================================
-- 基本資訊（非敏感欄位）
-- ============================================================
company_id,
company_name AS 公司名稱,
owner AS 業主姓名,
address AS 公司地址,
business_type AS 業務類型,
employee_count AS 員工人數,
-- ============================================================
-- 遮罩處理的敏感欄位
-- ============================================================
-- 電話號碼遮罩：只顯示最後四碼
-- 原始格式：0912-345-678
-- 遮罩後：****-***-5678
CASE
WHEN owner_phone_number IS NOT NULL THEN
CONCAT(
'****-***-',
-- 移除所有分隔符號後取最後四個字元
RIGHT(REPLACE(owner_phone_number, '-', ''), 4)
)
ELSE NULL
END AS 聯絡電話,
-- 電子郵件遮罩：只顯示第一個字元和完整網域
-- 原始格式：chang@wn-telecom.com.tw
-- 遮罩後：c***@wn-telecom.com.tw
CASE
WHEN owner_email IS NOT NULL THEN
CONCAT(
-- 取第一個字元
LEFT(owner_email, 1),
'***@',
-- 取 @ 符號後的所有內容（網域）
SUBSTRING_INDEX(owner_email, '@', -1)
)
ELSE NULL
END AS 電子郵件,
-- 身分證字號遮罩：只顯示開頭與結尾各兩碼
-- 原始格式：A123456789
-- 遮罩後：A1******89
CASE
WHEN owner_id_number IS NOT NULL THEN
CONCAT(
-- 取開頭兩個字元
LEFT(owner_id_number, 2),
'******',
-- 取結尾兩個字元
RIGHT(owner_id_number, 2)
)
ELSE NULL
END AS 身分證字號,
-- ============================================================
-- 時間戳記
-- ============================================================
created_at AS 建立時間,
updated_at AS 更新時間
FROM
company;
-- ============================================================
-- 使用遮罩檢視進行查詢
-- ============================================================
-- 範例 1: 查詢特定公司的遮罩資訊
SELECT
公司名稱,
業主姓名,
聯絡電話,
電子郵件,
身分證字號
FROM
v_company_business_masked
WHERE
company_id = 1;
-- 範例 2: 查詢所有公司的部分資訊
SELECT
公司名稱,
業主姓名,
聯絡電話,
業務類型
FROM
v_company_business_masked
ORDER BY
公司名稱;
-- 範例 3: 結合投訴資料的查詢
-- 展示如何在實務中使用遮罩檢視
SELECT
cm.公司名稱,
cm.業主姓名,
cm.聯絡電話,
COUNT(cp.complaint_id) AS 投訴數量,
SUM(CASE WHEN cp.status = 'pending' THEN 1 ELSE 0 END) AS 待處理數
FROM
v_company_business_masked cm
LEFT JOIN complaint cp ON cm.company_id = cp.company_id
GROUP BY
cm.company_id,
cm.公司名稱,
cm.業主姓名,
cm.聯絡電話
HAVING
待處理數 > 0
ORDER BY
待處理數 DESC;
部分遮罩的設計需要仔細考慮業務需求與安全要求之間的平衡。遮罩過多可能導致資訊不足以支援業務操作，而遮罩過少則可能無法提供足夠的隱私保護。在設計遮罩策略時，建議諮詢法務與資安部門的意見，確保符合相關法規要求。
此外，遮罩檢視通常應該搭配適當的存取控制機制。即使資料已經過遮罩處理，也應該只授予需要這些資訊的使用者存取權限。透過資料庫的權限管理系統，我們可以確保只有特定角色（如客服人員）能夠查詢這個遮罩檢視，進一步強化資料安全性。
使用檢視封裝複雜的業務邏輯
除了資料安全方面的應用，檢視也是封裝複雜查詢邏輯的有效工具。在實務系統中，某些報表或分析需要複雜的多表格聯結、子查詢或窗口函式。將這些複雜的邏輯封裝在檢視中，可以大幅簡化應用程式的程式碼，並確保所有使用者使用一致的計算邏輯。
在我們的投訴管理系統中，投訴分析是一個重要的業務需求。管理者需要了解每個公司的投訴詳情、處理效率、問題類型分布等資訊。這些分析涉及多個表格的聯結與複雜的計算，非常適合使用檢視來封裝。
-- ============================================================
-- 建立詳細投訴分析檢視
-- ============================================================
-- 此檢視整合公司資訊、投訴詳情與各種統計指標
-- 適用角色：管理人員、分析人員
CREATE VIEW v_complaint_detailed_analysis AS
SELECT
-- ============================================================
-- 公司基本資訊
-- ============================================================
c.company_id,
c.company_name AS 公司名稱,
c.owner AS 業主姓名,
c.business_type AS 業務類型,
-- ============================================================
-- 投訴基本資訊
-- ============================================================
cp.complaint_id AS 投訴編號,
cp.complaint_desc AS 投訴內容,
cp.complaint_date AS 投訴日期,
cp.complaint_type AS 投訴類型,
cp.severity AS 嚴重程度,
-- 將狀態代碼轉換為易讀的中文描述
CASE cp.status
WHEN 'pending' THEN '待處理'
WHEN 'processing' THEN '處理中'
WHEN 'resolved' THEN '已解決'
WHEN 'closed' THEN '已關閉'
ELSE '未知狀態'
END AS 處理狀態,
cp.resolution AS 處理結果,
cp.resolved_date AS 解決日期,
cp.assigned_to AS 負責人員,
-- ============================================================
-- 時間相關計算
-- ============================================================
-- 計算投訴處理天數
-- 對於已解決的投訴，計算從投訴到解決的天數
CASE
WHEN cp.status IN ('resolved', 'closed') AND cp.resolved_date IS NOT NULL
THEN DATEDIFF(cp.resolved_date, cp.complaint_date)
ELSE NULL
END AS 處理天數,
-- 計算投訴經過天數（從投訴日期到今天）
DATEDIFF(CURRENT_DATE, cp.complaint_date) AS 經過天數,
-- 判斷是否為逾期未處理（超過 7 天仍未解決）
CASE
WHEN cp.status NOT IN ('resolved', 'closed')
AND DATEDIFF(CURRENT_DATE, cp.complaint_date) > 7
THEN '是'
ELSE '否'
END AS 是否逾期,
-- ============================================================
-- 公司層級的統計指標（使用相關子查詢）
-- ============================================================
-- 計算該公司的投訴總數
(
SELECT COUNT(*)
FROM complaint
WHERE company_id = c.company_id
) AS 公司投訴總數,
-- 計算該公司的待處理投訴數
(
SELECT COUNT(*)
FROM complaint
WHERE company_id = c.company_id
AND status = 'pending'
) AS 公司待處理數,
-- 計算該公司的緊急投訴數
(
SELECT COUNT(*)
FROM complaint
WHERE company_id = c.company_id
AND severity = 'critical'
) AS 公司緊急投訴數,
-- 計算該公司的平均處理天數
(
SELECT ROUND(AVG(DATEDIFF(resolved_date, complaint_date)), 1)
FROM complaint
WHERE company_id = c.company_id
AND status = 'resolved'
AND resolved_date IS NOT NULL
) AS 公司平均處理天數,
-- ============================================================
-- 投訴類型層級的統計（使用相關子查詢）
-- ============================================================
-- 計算該投訴類型的總數
(
SELECT COUNT(*)
FROM complaint
WHERE complaint_type = cp.complaint_type
) AS 該類型投訴總數
FROM
company c
-- INNER JOIN 只顯示有投訴的公司
-- 如果需要顯示所有公司，改用 LEFT JOIN
INNER JOIN complaint cp ON c.company_id = cp.company_id;
-- ============================================================
-- 使用分析檢視進行複雜查詢
-- ============================================================
-- 範例 1: 查詢待處理的投訴，按經過天數排序
SELECT
公司名稱,
投訴內容,
投訴日期,
經過天數,
是否逾期,
負責人員
FROM
v_complaint_detailed_analysis
WHERE
處理狀態 = '待處理'
ORDER BY
經過天數 DESC;
-- 範例 2: 查詢各公司的投訴處理效率
SELECT
公司名稱,
公司投訴總數,
公司待處理數,
公司平均處理天數,
-- 計算待處理佔比
ROUND(公司待處理數 * 100.0 / 公司投訴總數, 1) AS 待處理佔比
FROM
v_complaint_detailed_analysis
GROUP BY
公司名稱,
公司投訴總數,
公司待處理數,
公司平均處理天數
HAVING
公司投訴總數 > 0
ORDER BY
待處理佔比 DESC;
-- 範例 3: 查詢逾期未處理的緊急投訴
SELECT
公司名稱,
投訴內容,
嚴重程度,
投訴日期,
經過天數,
負責人員
FROM
v_complaint_detailed_analysis
WHERE
是否逾期 = '是'
AND 嚴重程度 IN ('critical', 'high')
ORDER BY
經過天數 DESC, 嚴重程度;
-- 範例 4: 投訴類型分析
SELECT
投訴類型,
該類型投訴總數,
COUNT(*) AS 該類型待處理數,
ROUND(AVG(經過天數), 1) AS 平均經過天數
FROM
v_complaint_detailed_analysis
WHERE
處理狀態 = '待處理'
GROUP BY
投訴類型,
該類型投訴總數
ORDER BY
該類型投訴總數 DESC;
這個詳細分析檢視展示了如何將複雜的業務邏輯封裝在檢視中。透過使用相關子查詢、CASE 表達式與日期計算函式，我們在檢視層面就完成了大量的資料處理與計算工作。應用程式只需要執行簡單的 SELECT 語句，就能獲得豐富的分析資訊。
在設計這類分析檢視時，需要特別注意效能問題。相關子查詢可能會導致較差的執行效能，特別是當資料量很大時。可以考慮使用 JOIN 代替相關子查詢，或是在基礎表格上建立適當的索引。在某些情況下，可能需要使用物化檢視（Materialized View）或定期更新的匯總表格來提升效能。
可更新檢視的設計與限制
資料庫檢視不僅可以用於查詢資料，在某些情況下還可以用於更新底層表格的資料。這種功能讓檢視不僅是一個唯讀的資料視角，更可以作為資料修改的控制介面。然而，可更新檢視有嚴格的限制條件，需要仔細設計才能正確運作。
可更新檢視必須滿足幾個基本條件。首先，檢視必須只參照單一基礎表格，不能包含多表格聯結。其次，檢視不能使用聚合函式（如 COUNT、SUM、AVG）、DISTINCT 關鍵字、GROUP BY 或 HAVING 子句。第三，檢視不能包含子查詢或 UNION 等集合運算。最後，檢視的 SELECT 清單中的所有欄位都必須直接對應到基礎表格的欄位，不能是運算式或函式的結果。
在我們的投訴管理系統中，我們可以建立一個可更新的檢視，讓應用程式能夠更新公司的基本資訊，但不能更新敏感的聯絡資料。這種設計可以進一步強化資料安全性，即使應用程式有漏洞，也無法透過這個檢視修改敏感欄位。
-- ============================================================
-- 建立可更新的公司基本資訊檢視
-- ============================================================
-- 此檢視只包含可以被一般業務人員更新的欄位
-- 敏感的聯絡資訊被排除在外，無法透過此檢視更新
CREATE VIEW v_company_editable AS
SELECT
-- 主鍵（不可更新，但需要包含以識別記錄）
company_id,
-- 可更新的基本欄位
company_name,
owner,
address,
business_type,
employee_count,
-- 時間戳記欄位
created_at,
updated_at
-- 注意：以下敏感欄位不包含在檢視中
-- 無法透過此檢視查看或更新
-- - owner_phone_number
-- - owner_email
-- - owner_id_number
FROM
company;
-- ============================================================
-- 透過檢視更新資料
-- ============================================================
-- 範例 1: 更新公司地址
-- 這會更新底層的 company 表格
UPDATE v_company_editable
SET address = '台北市大安區忠孝東路四段500號15樓'
WHERE company_id = 1;
-- 範例 2: 更新多個欄位
UPDATE v_company_editable
SET
business_type = '通訊服務業',
employee_count = 180
WHERE company_id = 1;
-- 範例 3: 嘗試更新不存在的敏感欄位會失敗
-- 這個操作會產生錯誤，因為 owner_phone_number 不在檢視中
-- UPDATE v_company_editable
-- SET owner_phone_number = '0912-000-000'
-- WHERE company_id = 1;
-- 錯誤: Unknown column 'owner_phone_number' in 'field list'
-- ============================================================
-- 透過檢視插入資料
-- ============================================================
-- 範例 4: 插入新公司記錄
-- 注意：所有 NOT NULL 且沒有預設值的欄位都必須提供
INSERT INTO v_company_editable (
company_name,
owner,
address,
business_type,
employee_count
)
VALUES (
'創新科技股份有限公司',
'趙六明',
'台中市西屯區台灣大道四段100號',
'軟體開發業',
50
);
-- ============================================================
-- 透過檢視刪除資料
-- ============================================================
-- 範例 5: 刪除記錄
-- 注意：如果有外鍵約束（如 complaint 參照 company），
-- 且設定為 RESTRICT，則無法刪除有關聯記錄的公司
DELETE FROM v_company_editable
WHERE company_id = 99; -- 假設這是一個測試記錄
-- ============================================================
-- 驗證更新結果
-- ============================================================
-- 檢視更新後的資料
SELECT
company_id,
company_name,
owner,
address,
business_type,
employee_count,
updated_at
FROM
v_company_editable
WHERE
company_id = 1;
在使用可更新檢視時，還需要注意一些進階的控制機制。WITH CHECK OPTION 選項可以確保透過檢視插入或更新的資料符合檢視的 WHERE 條件。這個選項有兩種模式：LOCAL 與 CASCADE。LOCAL 模式只檢查當前檢視的 WHERE 條件，而 CASCADE 模式會檢查整個檢視層次結構中所有檢視的 WHERE 條件。
-- ============================================================
-- 使用 WITH CHECK OPTION 的檢視
-- ============================================================
-- 此檢視只顯示電信服務業的公司
-- WITH CHECK OPTION 確保只能插入或更新為電信服務業
CREATE VIEW v_telecom_companies AS
SELECT
company_id,
company_name,
owner,
address,
business_type,
employee_count
FROM
company
WHERE
business_type = '電信服務業'
WITH CHECK OPTION;
-- ============================================================
-- 測試 WITH CHECK OPTION 的效果
-- ============================================================
-- 範例 1: 成功的更新（符合 WHERE 條件）
UPDATE v_telecom_companies
SET employee_count = 200
WHERE company_id = 1;
-- 範例 2: 失敗的更新（違反 WHERE 條件）
-- 這個操作會失敗，因為嘗試將 business_type 改為非電信服務業
-- UPDATE v_telecom_companies
-- SET business_type = '零售業'
-- WHERE company_id = 1;
-- 錯誤: CHECK OPTION failed
-- 範例 3: 成功的插入（符合 WHERE 條件）
INSERT INTO v_telecom_companies (
company_name,
owner,
address,
business_type,
employee_count
)
VALUES (
'未來通訊',
'周七星',
'高雄市前鎮區中山路888號',
'電信服務業',
120
);
-- 範例 4: 失敗的插入（違反 WHERE 條件）
-- 這個操作會失敗
-- INSERT INTO v_telecom_companies (
-- company_name,
-- owner,
-- address,
-- business_type,
-- employee_count
-- )
-- VALUES (
-- '一般商店',
-- '吳八福',
-- '台南市中西區民生路200號',
-- '零售業',
-- 10
-- );
-- 錯誤: CHECK OPTION failed
可更新檢視提供了一個強大的資料存取控制機制，但也需要謹慎使用。在設計可更新檢視時，建議明確文件化哪些欄位可以透過檢視更新，以及是否有使用 WITH CHECK OPTION。此外，應該搭配適當的權限管理，確保只有授權的使用者能夠透過檢視修改資料。
巢狀檢視的設計模式與應用
巢狀檢視是指建立在其他檢視之上的檢視，形成多層次的資料抽象結構。這種設計模式在複雜的系統中特別有用，可以將大型的查詢邏輯分解為多個較小、更易於管理的元件。每一層檢視專注於特定的資料處理或轉換，上層檢視則組合下層檢視的結果，逐步建構出所需的資料視角。
巢狀檢視的優勢在於模組化與可重用性。底層的基礎檢視可以被多個上層檢視使用，避免重複的查詢邏輯。當業務需求變化時，我們可能只需要修改某一層的檢視，而不需要重寫整個查詢體系。此外，巢狀檢視也提供了更清晰的邏輯結構，讓開發者更容易理解與維護複雜的資料處理流程。
然而，巢狀檢視也有其限制。過多的層次可能會影響查詢效能，因為資料庫需要展開整個檢視層次結構來產生最終的執行計畫。此外，除錯與效能調校也會變得更加困難，因為問題可能出現在檢視層次結構中的任何一層。因此，建議將巢狀層次控制在三層以內，並在必要時使用 EXPLAIN 等工具分析查詢效能。
-- ============================================================
-- 巢狀檢視範例：三層架構
-- ============================================================
-- ============================================================
-- 第一層：基礎統計檢視
-- ============================================================
-- 計算每個公司的基本投訴統計數據
CREATE VIEW v_company_complaint_stats AS
SELECT
company_id,
-- 投訴總數
COUNT(*) AS total_complaints,
-- 各狀態的投訴數
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
SUM(CASE WHEN status = 'processing' THEN 1 ELSE 0 END) AS processing_count,
SUM(CASE WHEN status = 'resolved' THEN 1 ELSE 0 END) AS resolved_count,
SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) AS closed_count,
-- 各嚴重程度的投訴數
SUM(CASE WHEN severity = 'critical' THEN 1 ELSE 0 END) AS critical_count,
SUM(CASE WHEN severity = 'high' THEN 1 ELSE 0 END) AS high_count,
-- 時間統計
MIN(complaint_date) AS first_complaint_date,
MAX(complaint_date) AS last_complaint_date,
-- 處理天數統計（只計算已解決的）
AVG(
CASE
WHEN status = 'resolved' AND resolved_date IS NOT NULL
THEN DATEDIFF(resolved_date, complaint_date)
ELSE NULL
END
) AS avg_resolution_days
FROM
complaint
GROUP BY
company_id;
-- ============================================================
-- 第二層：結合公司資訊的檢視
-- ============================================================
-- 將公司基本資訊與統計數據結合
CREATE VIEW v_company_performance AS
SELECT
c.company_id,
c.company_name,
c.owner,
c.business_type,
c.employee_count,
-- 從第一層檢視取得統計數據
COALESCE(s.total_complaints, 0) AS total_complaints,
COALESCE(s.pending_count, 0) AS pending_count,
COALESCE(s.processing_count, 0) AS processing_count,
COALESCE(s.resolved_count, 0) AS resolved_count,
COALESCE(s.critical_count, 0) AS critical_count,
COALESCE(s.high_count, 0) AS high_count,
-- 計算解決率（百分比）
CASE
WHEN s.total_complaints > 0 THEN
ROUND(s.resolved_count * 100.0 / s.total_complaints, 1)
ELSE NULL
END AS resolution_rate,
-- 計算待處理佔比
CASE
WHEN s.total_complaints > 0 THEN
ROUND(s.pending_count * 100.0 / s.total_complaints, 1)
ELSE NULL
END AS pending_rate,
-- 平均處理天數
ROUND(s.avg_resolution_days, 1) AS avg_resolution_days,
-- 投訴密度（每百名員工的投訴數）
CASE
WHEN c.employee_count > 0 THEN
ROUND(s.total_complaints * 100.0 / c.employee_count, 2)
ELSE NULL
END AS complaint_density,
s.first_complaint_date,
s.last_complaint_date
FROM
company c
LEFT JOIN v_company_complaint_stats s ON c.company_id = s.company_id;
-- ============================================================
-- 第三層：績效評級檢視
-- ============================================================
-- 根據各項指標計算績效評級
CREATE VIEW v_company_rating AS
SELECT
company_id,
company_name,
business_type,
total_complaints,
resolution_rate,
pending_rate,
avg_resolution_days,
complaint_density,
-- 計算綜合評級（A-F）
CASE
-- A級：解決率 >= 90%，平均處理天數 <= 3
WHEN resolution_rate >= 90 AND avg_resolution_days <= 3 THEN 'A'
-- B級：解決率 >= 80%，平均處理天數 <= 5
WHEN resolution_rate >= 80 AND avg_resolution_days <= 5 THEN 'B'
-- C級：解決率 >= 70%，平均處理天數 <= 7
WHEN resolution_rate >= 70 AND avg_resolution_days <= 7 THEN 'C'
-- D級：解決率 >= 60%
WHEN resolution_rate >= 60 THEN 'D'
-- F級：解決率 < 60% 或其他情況
ELSE 'F'
END AS performance_grade,
-- 計算風險等級
CASE
-- 高風險：有緊急投訴且待處理率高
WHEN critical_count > 0 AND pending_rate > 30 THEN '高風險'
-- 中風險：待處理率較高
WHEN pending_rate > 20 THEN '中風險'
-- 低風險：其他情況
ELSE '低風險'
END AS risk_level,
-- 建議改善項目
CASE
WHEN avg_resolution_days > 7 THEN '需加速處理流程'
WHEN pending_rate > 30 THEN '待處理案件過多'
WHEN critical_count > 2 THEN '需關注緊急投訴'
WHEN resolution_rate < 70 THEN '需提升解決率'
ELSE '表現良好'
END AS improvement_suggestion
FROM
v_company_performance;
-- ============================================================
-- 使用巢狀檢視進行查詢
-- ============================================================
-- 範例 1: 查詢所有公司的績效評級
SELECT
company_name AS 公司名稱,
business_type AS 業務類型,
total_complaints AS 投訴總數,
resolution_rate AS 解決率,
performance_grade AS 績效評級,
risk_level AS 風險等級,
improvement_suggestion AS 改善建議
FROM
v_company_rating
ORDER BY
performance_grade, resolution_rate DESC;
-- 範例 2: 查詢高風險公司
SELECT
company_name,
total_complaints,
pending_rate,
critical_count,
risk_level,
improvement_suggestion
FROM
v_company_rating
WHERE
risk_level = '高風險'
ORDER BY
critical_count DESC, pending_rate DESC;
-- 範例 3: 按業務類型統計績效分布
SELECT
business_type,
performance_grade,
COUNT(*) AS company_count,
AVG(resolution_rate) AS avg_resolution_rate
FROM
v_company_rating
GROUP BY
business_type,
performance_grade
ORDER BY
business_type, performance_grade;
這個三層巢狀檢視的範例展示了如何逐步建構複雜的分析邏輯。第一層專注於基礎的統計計算，第二層結合公司資訊並計算衍生指標，第三層則根據各項指標產生評級與建議。每一層都有明確的職責，使得整個架構清晰易懂。
在實務應用中，巢狀檢視特別適合建立資料倉儲或商業智慧系統的語意層。透過多層次的檢視，我們可以將原始的交易資料逐步轉換為高階的業務指標與分析結果，讓業務使用者能夠直接查詢他們需要的資訊，而不需要理解底層複雜的資料結構。
檢視的管理與維護實務
隨著系統的演進，檢視的數量可能會逐漸增加，建立完善的管理機制變得非常重要。檢視的管理包含檢視的建立與修改、定義的查詢與文件化、權限的分配與控制，以及效能的監控與最佳化。良好的管理實務可以確保檢視體系保持可維護性，避免因檢視過度增長而導致的混亂。
檢視的修改可以使用 CREATE OR REPLACE VIEW 語法，這個語法會在檢視存在時替換其定義，不存在時則建立新檢視。這種方式比先刪除後建立更安全，因為它不會影響檢視的權限設定。此外，MySQL 也提供了 ALTER VIEW 語法來修改檢視定義。在修改檢視時，建議先在測試環境中驗證新的定義，確保不會破壞依賴該檢視的應用程式。
-- ============================================================
-- 檢視管理操作
-- ============================================================
-- ============================================================
-- 1. 查看檢視定義
-- ============================================================
-- 查看特定檢視的建立語句
SHOW CREATE VIEW v_complaint_public_statistics;
-- 從資訊架構查詢所有檢視
SELECT
TABLE_NAME AS view_name,
VIEW_DEFINITION AS definition
FROM
information_schema.VIEWS
WHERE
TABLE_SCHEMA = DATABASE()
ORDER BY
TABLE_NAME;
-- 查詢特定檢視的詳細資訊
SELECT
TABLE_NAME AS view_name,
VIEW_DEFINITION AS definition,
CHECK_OPTION AS check_option,
IS_UPDATABLE AS is_updatable,
DEFINER AS definer,
SECURITY_TYPE AS security_type
FROM
information_schema.VIEWS
WHERE
TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'v_complaint_public_statistics';
-- ============================================================
-- 2. 修改檢視定義
-- ============================================================
-- 使用 CREATE OR REPLACE 修改檢視
-- 如果檢視存在則替換，不存在則建立
CREATE OR REPLACE VIEW v_complaint_public_statistics AS
SELECT
c.company_id,
c.company_name AS 公司名稱,
c.owner AS 業主姓名,
c.address AS 公司地址,
c.business_type AS 業務類型,
COUNT(cp.complaint_id) AS 投訴總數,
SUM(CASE WHEN cp.status = 'pending' THEN 1 ELSE 0 END) AS 待處理數,
SUM(CASE WHEN cp.status = 'resolved' THEN 1 ELSE 0 END) AS 已解決數,
MAX(cp.complaint_date) AS 最近投訴日期,
-- 新增欄位：計算解決率
CASE
WHEN COUNT(cp.complaint_id) > 0 THEN
ROUND(
SUM(CASE WHEN cp.status = 'resolved' THEN 1 ELSE 0 END) * 100.0 /
COUNT(cp.complaint_id),
1
)
ELSE NULL
END AS 解決率
FROM
company c
LEFT JOIN complaint cp ON c.company_id = cp.company_id
GROUP BY
c.company_id,
c.company_name,
c.owner,
c.address,
c.business_type;
-- 使用 ALTER VIEW 修改檢視（另一種方式）
ALTER VIEW v_company_business_masked AS
SELECT
company_id,
company_name AS 公司名稱,
owner AS 業主姓名,
address AS 公司地址,
-- 修改電話遮罩方式
CONCAT('****-***-', RIGHT(REPLACE(owner_phone_number, '-', ''), 4)) AS 聯絡電話
FROM
company;
-- ============================================================
-- 3. 刪除檢視
-- ============================================================
-- 刪除單一檢視
DROP VIEW IF EXISTS v_test_view;
-- 刪除多個檢視
DROP VIEW IF EXISTS
v_temp_view_1,
v_temp_view_2,
v_temp_view_3;
-- ============================================================
-- 4. 檢視相依性分析
-- ============================================================
-- 查詢檢視依賴的基礎表格
SELECT
TABLE_NAME AS view_name,
REFERENCED_TABLE_NAME AS base_table
FROM
information_schema.VIEW_TABLE_USAGE
WHERE
TABLE_SCHEMA = DATABASE()
ORDER BY
view_name, base_table;
-- ============================================================
-- 5. 檢視權限管理
-- ============================================================
-- 建立專用的資料庫使用者
CREATE USER IF NOT EXISTS 'app_readonly'@'localhost'
IDENTIFIED BY 'SecurePassword123!';
-- 授予特定檢視的 SELECT 權限
GRANT SELECT ON complaint_db.v_complaint_public_statistics
TO 'app_readonly'@'localhost';
GRANT SELECT ON complaint_db.v_company_business_masked
TO 'app_readonly'@'localhost';
-- 授予所有檢視的 SELECT 權限
-- 注意：這會授予所有現有和未來建立的檢視的權限
GRANT SELECT ON complaint_db.* TO 'app_readonly'@'localhost';
-- 撤銷權限
REVOKE SELECT ON complaint_db.v_complaint_public_statistics
FROM 'app_readonly'@'localhost';
-- 查看使用者權限
SHOW GRANTS FOR 'app_readonly'@'localhost';
-- ============================================================
-- 6. 檢視效能分析
-- ============================================================
-- 使用 EXPLAIN 分析檢視查詢的執行計畫
EXPLAIN SELECT
公司名稱,
投訴總數,
解決率
FROM
v_complaint_public_statistics
WHERE
投訴總數 > 5
ORDER BY
解決率 DESC;
-- 分析複雜巢狀檢視的效能
EXPLAIN SELECT *
FROM v_company_rating
WHERE performance_grade IN ('A', 'B')
ORDER BY resolution_rate DESC;
在管理大量檢視時，建議建立命名慣例與分類體系。例如，可以使用前綴來區分不同類型的檢視：v_public_ 用於公開檢視、v_masked_ 用於遮罩檢視、v_admin_ 用於管理檢視等。此外，應該為每個檢視建立文件，說明檢視的用途、包含的欄位、預期的使用者角色，以及任何特殊的限制或注意事項。
效能最佳化策略與最佳實踐
檢視雖然提供了強大的資料抽象能力，但如果使用不當，可能會對查詢效能產生負面影響。理解檢視的執行機制與最佳化技巧，對於建構高效能的資料存取層至關重要。效能最佳化需要從多個層面著手，包含檢視定義的優化、基礎表格的索引設計、查詢模式的調整，以及在必要時考慮替代方案。
檢視效能的第一個關鍵是基礎表格的索引設計。由於檢視不儲存資料，每次查詢都需要從基礎表格中擷取，因此基礎表格的索引直接影響檢視的查詢效能。應該根據檢視中常用的 WHERE 條件、JOIN 條件與 ORDER BY 子句來建立適當的索引。特別是對於經常被聯結的欄位與過濾條件中使用的欄位，建立索引可以顯著提升查詢速度。
-- ============================================================
-- 效能最佳化策略
-- ============================================================
-- ============================================================
-- 策略 1: 為基礎表格建立適當的索引
-- ============================================================
-- 分析檢視的查詢模式，建立對應的索引
-- 針對 complaint 表格的常用查詢建立索引
CREATE INDEX idx_complaint_company_status
ON complaint(company_id, status);
CREATE INDEX idx_complaint_severity_date
ON complaint(severity, complaint_date);
CREATE INDEX idx_complaint_type_status
ON complaint(complaint_type, status);
-- 針對 company 表格建立索引
CREATE INDEX idx_company_business_type
ON company(business_type);
CREATE INDEX idx_company_name
ON company(company_name);
-- 建立覆蓋索引以提升特定查詢的效能
-- 覆蓋索引包含查詢所需的所有欄位，避免回表查詢
CREATE INDEX idx_complaint_coverage
ON complaint(company_id, status, complaint_date, severity);
-- ============================================================
-- 策略 2: 優化檢視定義
-- ============================================================
-- 避免在檢視中使用 SELECT *
-- 明確指定需要的欄位可以減少資料傳輸量
-- 不良實踐
CREATE VIEW v_bad_example AS
SELECT * FROM company;
-- 良好實踐：明確指定欄位
CREATE VIEW v_good_example AS
SELECT
company_id,
company_name,
owner,
business_type
FROM company;
-- ============================================================
-- 策略 3: 避免過深的巢狀檢視
-- ============================================================
-- 巢狀層次應控制在 3 層以內
-- 對於複雜的統計查詢，考慮使用物化檢視的替代方案
-- 建立模擬的物化檢視（使用實體表格）
CREATE TABLE mv_company_statistics AS
SELECT
c.company_id,
c.company_name,
c.business_type,
COUNT(cp.complaint_id) AS total_complaints,
SUM(CASE WHEN cp.status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
SUM(CASE WHEN cp.status = 'resolved' THEN 1 ELSE 0 END) AS resolved_count,
ROUND(
AVG(CASE WHEN cp.status = 'resolved' THEN
DATEDIFF(cp.resolved_date, cp.complaint_date)
ELSE NULL END),
1
) AS avg_resolution_days,
CURRENT_TIMESTAMP AS last_updated
FROM
company c
LEFT JOIN complaint cp ON c.company_id = cp.company_id
GROUP BY
c.company_id,
c.company_name,
c.business_type;
-- 為物化檢視建立索引
CREATE INDEX idx_mv_company_stats_business
ON mv_company_statistics(business_type);
CREATE INDEX idx_mv_company_stats_complaints
ON mv_company_statistics(total_complaints);
-- 建立更新物化檢視的預存程序
DELIMITER //
CREATE PROCEDURE sp_refresh_company_statistics()
BEGIN
-- 清空現有資料
TRUNCATE TABLE mv_company_statistics;
-- 重新計算統計資料
INSERT INTO mv_company_statistics
SELECT
c.company_id,
c.company_name,
c.business_type,
COUNT(cp.complaint_id) AS total_complaints,
SUM(CASE WHEN cp.status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
SUM(CASE WHEN cp.status = 'resolved' THEN 1 ELSE 0 END) AS resolved_count,
ROUND(
AVG(CASE WHEN cp.status = 'resolved' THEN
DATEDIFF(cp.resolved_date, cp.complaint_date)
ELSE NULL END),
1
) AS avg_resolution_days,
CURRENT_TIMESTAMP AS last_updated
FROM
company c
LEFT JOIN complaint cp ON c.company_id = cp.company_id
GROUP BY
c.company_id,
c.company_name,
c.business_type;
END //
DELIMITER ;
-- 設定定期更新物化檢視（使用 Event Scheduler）
CREATE EVENT evt_refresh_statistics
ON SCHEDULE EVERY 1 HOUR
DO CALL sp_refresh_company_statistics();
-- 啟用 Event Scheduler
SET GLOBAL event_scheduler = ON;
-- ============================================================
-- 策略 4: 查詢模式最佳化
-- ============================================================
-- 在查詢檢視時，盡可能在 WHERE 子句中提供過濾條件
-- 讓資料庫最佳化器能夠下推條件到基礎表格
-- 不良實踐：查詢所有資料後在應用程式中過濾
-- SELECT * FROM v_complaint_public_statistics;
-- 良好實踐：在資料庫層面過濾
SELECT
公司名稱,
投訴總數,
解決率
FROM
v_complaint_public_statistics
WHERE
業務類型 = '電信服務業'
AND 投訴總數 > 5
ORDER BY
解決率 DESC
LIMIT 10;
-- ============================================================
-- 策略 5: 監控檢視效能
-- ============================================================
-- 定期檢查慢查詢日誌
-- 識別效能不佳的檢視查詢
-- 查看最近的慢查詢
SELECT
sql_text,
rows_examined,
rows_sent,
query_time
FROM
mysql.slow_log
WHERE
sql_text LIKE '%v_complaint%'
ORDER BY
query_time DESC
LIMIT 10;
-- 使用 EXPLAIN 分析查詢計畫
EXPLAIN FORMAT=JSON
SELECT *
FROM v_complaint_detailed_analysis
WHERE 處理狀態 = '待處理'
ORDER BY 經過天數 DESC;
在某些情況下，檢視可能不是最佳的解決方案。對於需要頻繁查詢但更新較少的統計資料，物化檢視（或用實體表格模擬的物化檢視）可能提供更好的效能。物化檢視實際儲存查詢結果，查詢時直接讀取預先計算好的資料，而不需要每次都執行複雜的計算。代價是需要定期更新物化檢視以保持資料的新鮮度，以及額外的儲存空間消耗。
綜合應用案例與實務建議
在理解了檢視的各種應用技術後，我們可以設計一個完整的多層次檢視架構，為不同角色的使用者提供適當的資料存取介面。這個架構應該平衡資料安全性、查詢便利性與系統效能，形成一個可擴展且易於維護的資料存取層。
在投訴管理系統的完整實作中，我們可以為三種主要角色建立對應的檢視：一般使用者使用公開統計檢視，只能看到不包含敏感資料的投訴統計；客服人員使用業務處理檢視，可以看到部分遮罩的聯絡資訊以進行業務處理；系統管理員使用完整資料檢視，可以存取所有資料進行系統管理與深度分析。
-- ============================================================
-- 綜合應用案例：完整的多層次檢視架構
-- ============================================================
-- ============================================================
-- 角色 1: 一般使用者檢視（公開資訊）
-- ============================================================
-- 完全隱藏敏感資料，只提供統計資訊
CREATE VIEW v_public_complaint_info AS
SELECT
c.company_name AS 公司名稱,
c.business_type AS 業務類型,
c.address AS 公司地址,
-- 投訴統計
COUNT(cp.complaint_id) AS 投訴數量,
-- 按狀態分類統計
SUM(CASE WHEN cp.status = 'resolved' THEN 1 ELSE 0 END) AS 已解決數,
SUM(CASE WHEN cp.status IN ('pending', 'processing') THEN 1 ELSE 0 END) AS 處理中數,
-- 解決率
ROUND(
SUM(CASE WHEN cp.status = 'resolved' THEN 1 ELSE 0 END) * 100.0 /
COUNT(cp.complaint_id),
1
) AS 解決率,
-- 最近投訴日期
MAX(cp.complaint_date) AS 最近投訴日期
FROM company c
LEFT JOIN complaint cp ON c.company_id = cp.company_id
GROUP BY c.company_id, c.company_name, c.business_type, c.address;
-- ============================================================
-- 角色 2: 客服人員檢視（業務處理）
-- ============================================================
-- 部分遮罩敏感資料，提供業務處理所需資訊
CREATE VIEW v_customer_service_view AS
SELECT
c.company_id,
c.company_name AS 公司名稱,
c.owner AS 業主姓名,
c.business_type AS 業務類型,
-- 遮罩的聯絡資訊
CONCAT('****-***-', RIGHT(REPLACE(c.owner_phone_number, '-', ''), 4)) AS 聯絡電話,
CONCAT(LEFT(c.owner_email, 1), '***@', SUBSTRING_INDEX(c.owner_email, '@', -1)) AS 電子郵件,
-- 投訴詳情
cp.complaint_id AS 投訴編號,
cp.complaint_desc AS 投訴內容,
cp.complaint_date AS 投訴日期,
cp.complaint_type AS 投訴類型,
cp.severity AS 嚴重程度,
cp.status AS 狀態,
cp.assigned_to AS 負責人員,
-- 處理天數
DATEDIFF(COALESCE(cp.resolved_date, CURRENT_DATE), cp.complaint_date) AS 經過天數
FROM company c
LEFT JOIN complaint cp ON c.company_id = cp.company_id;
-- ============================================================
-- 角色 3: 管理員檢視（完整權限）
-- ============================================================
-- 包含所有資料的完整檢視
CREATE VIEW v_admin_full_access AS
SELECT
c.company_id,
c.company_name,
c.owner,
c.owner_phone_number,
c.owner_email,
c.owner_id_number,
c.address,
c.business_type,
c.employee_count,
-- 投訴完整資訊
cp.complaint_id,
cp.complaint_desc,
cp.complaint_date,
cp.complaint_type,
cp.severity,
cp.status,
cp.resolution,
cp.resolved_date,
cp.assigned_to,
-- 時間戳記
c.created_at AS 公司建立時間,
c.updated_at AS 公司更新時間,
cp.created_at AS 投訴建立時間,
cp.updated_at AS 投訴更新時間
FROM company c
LEFT JOIN complaint cp ON c.company_id = cp.company_id;
-- ============================================================
-- 建立對應的資料庫使用者與權限分配
-- ============================================================
-- 建立一般使用者帳號
CREATE USER IF NOT EXISTS 'public_user'@'%'
IDENTIFIED BY 'PublicPassword123!';
-- 只授予公開檢視的查詢權限
GRANT SELECT ON complaint_db.v_public_complaint_info
TO 'public_user'@'%';
-- 建立客服人員帳號
CREATE USER IF NOT EXISTS 'cs_staff'@'%'
IDENTIFIED BY 'CSPassword123!';
-- 授予客服檢視的查詢與更新權限
GRANT SELECT, UPDATE ON complaint_db.v_customer_service_view
TO 'cs_staff'@'%';
-- 建立管理員帳號
CREATE USER IF NOT EXISTS 'admin_user'@'localhost'
IDENTIFIED BY 'AdminPassword123!';
-- 授予管理員檢視的完整權限
GRANT ALL PRIVILEGES ON complaint_db.v_admin_full_access
TO 'admin_user'@'localhost';
-- 授予管理員對所有檢視的管理權限
GRANT CREATE VIEW, ALTER VIEW, DROP VIEW
ON complaint_db.*
TO 'admin_user'@'localhost';
在實務應用中，這種多層次的檢視架構提供了靈活且安全的資料存取控制。應用程式可以根據使用者的角色選擇查詢對應的檢視，而不需要在應用程式層面實作複雜的資料過濾邏輯。這不僅簡化了應用程式的程式碼，也提升了安全性，因為資料存取控制在資料庫層面實施，更難被繞過。
建議在專案開始時就規劃好檢視的架構，包含命名慣例、分層策略、權限分配等。隨著系統的演進，定期審查檢視的使用情況，移除不再使用的檢視，優化效能不佳的檢視，並根據新的業務需求建立新的檢視。透過持續的維護與優化，檢視體系可以長期為系統提供價值，成為資料存取層的堅實基礎。
結論與最佳實踐總結
資料庫檢視是實現資料安全、查詢簡化與架構抽象化的強大工具。透過本文的完整介紹，我們深入探討了檢視在各種實務場景中的應用技術，從基礎的敏感欄位隱藏到進階的資料遮罩、複雜查詢封裝、可更新檢視設計與巢狀檢視應用。每種技術都有其適用的場景與設計考量，理解這些技術的優勢與限制，對於建構高效且安全的資料存取層至關重要。
在台灣的企業環境中，個人資料保護法的要求日益嚴格，建立完善的資料存取控制機制已經成為合規經營的必要條件。資料庫檢視提供了在資料庫層面實施細緻化存取控制的能力，讓企業能夠在滿足業務需求的同時，確保敏感資料得到適當的保護。透過建立多層次的檢視架構，為不同角色的使用者提供適當的資料視角，既能保護隱私又能提供必要的業務資訊，這是現代資料治理的最佳實踐。
在效能方面，檢視的使用需要謹慎設計。適當的索引規劃、合理的檢視定義、避免過深的巢狀層次，以及在必要時使用物化檢視等替代方案，都是確保檢視系統高效運作的關鍵。定期監控檢視的查詢效能，使用資料庫提供的分析工具識別瓶頸，並及時進行最佳化，可以確保系統長期保持良好的回應速度。
在維護層面，建立清晰的命名慣例、完善的文件記錄、合理的權限分配機制，以及定期的審查與清理流程，可以確保檢視體系保持可管理性。隨著業務的演進，檢視可能需要調整或重構，良好的管理實務可以讓這些變更更加平順，降低對應用程式的影響。
建議開發團隊將檢視視為資料存取架構的重要組成部分，而不僅僅是一個便利的查詢工具。在系統設計階段就規劃好檢視的架構，將檢視的建立與維護納入開發流程，並建立相應的測試與部署機制。透過這種系統化的方法，檢視可以成為支撐應用程式長期穩定運作的堅實基礎，為企業的資料管理與安全保護提供可靠的保障。
