[[407941]]

本文轉載自微信公眾號「數據與智能」，作者Alan Beaulieu。轉載本文請聯系數據與智能公眾號。

設計良好的應用程序通常會在保持實現細節私有性的同時公開一個公共接口，從而在不影響終端用戶的情況下支持將來的設計變動。在設計數據庫時，通過保持表的私有性并允許用戶僅通過一組視圖訪問數據，你可以獲得類似的結果。本章致力于定義什么是視圖、如何創建它們、何時使用它們以及如何使用它們。

1. 什么是視圖

視圖其實就是一種數據查詢機制。與表不同，視圖不涉及數據存儲，所以不必擔心視圖會占用磁盤空間。可以通過命名select語句來創建視圖，將其保存以供其他人使用。其他用戶可以使用該視圖訪問數據，就像他們直接查詢表一樣(實際上，他們甚至可能不知道自己正在使用視圖)。

舉一個簡單的例子，假設你希望部分隱藏customer表中的電子郵件地址。例如，市場營銷部門可能需要訪問電子郵件地址才能發布促銷廣告，但公司的隱私政策有規定必須保證這些數據的安全。因此不允許直接訪問customer表，而是定義一個名為customer_vw的視圖，并授權給所有非營銷人員使用以訪問客戶數據。視圖定義如下：

CREATE VIEW customer_vw 
 (customer_id, 
 first_name, 
 last_name, 
 email  
 ) 
AS 
SELECT  
 customer_id, 
 first_name, 
 last_name, 
 concat(substr(email,1,2), '*****', substr(email, -4)) email 
FROM customer; 

語句的第一部分列出了視圖的列名，這些列名可能與基礎表的列名不同。語句的第二部分是select語句，它必須為視圖中的每一列提供一個表達式。email列的生成方法是：獲取電子郵件地址的前兩個字符，與“*****”連接，然后與電子郵件地址的最后四個字符連接。

執行create view語句時，數據庫服務器只簡單地存儲視圖定義以供將來使用。若不執行查詢，也就不會檢索或存儲任何數據。創建視圖后，用戶可以像查詢表一樣使用它進行查詢，如下所示：

mysql> SELECT first_name, last_name, email 
 -> FROM customer_vw; 
+-------------+--------------+-------------+ 
| first_name | last_name | email | 
+-------------+--------------+-------------+ 
| MARY | SMITH | MA*****.org | 
| PATRICIA | JOHNSON | PA*****.org | 
| LINDA | WILLIAMS | LI*****.org | 
| BARBARA | JONES | BA*****.org | 
| ELIZABETH | BROWN | EL*****.org | 
... 
| ENRIQUE | FORSYTHE | EN*****.org | 
| FREDDIE | DUGGAN | FR*****.org | 
| WADE | DELVALLE | WA*****.org | 
| AUSTIN | CINTRON | AU*****.org | 
+-------------+--------------+-------------+ 
599 rows in set (0.00 sec) 

盡管customer_vw視圖定義包含customer表的四列，但前面的查詢只檢索其中三列。正如你將在本章后面看到的，如果視圖中的某些列被附加到函數或子查詢，那么這會是一個重要的區別。

從用戶的角度來看，視圖看起來就像一個表。要想知道視圖中有哪些列是可用的，可以使用MySQL(或Oracle)的describe命令查看：

mysql> describe customer_vw; 
+-------------+----------------------+------+-----+---------+-------+ 
| Field | Type | Null | Key | Default | Extra | 
+-------------+----------------------+------+-----+---------+-------+ 
| customer_id | smallint(5) unsigned | NO | | 0 | | 
| first_name | varchar(45) | NO | | NULL | | 
| last_name | varchar(45) | NO | | NULL | | 
| email | varchar(11) | YES | | NULL | | 
+-------------+----------------------+------+-----+---------+-------+ 
4 rows in set (0.00 sec) 

在通過視圖進行查詢時，可以自由使用select語句中的任何子句，包括group by、having和order by。舉個例子：

mysql> SELECT first_name, count(*), min(last_name), max(last_name) 
 -> FROM customer_vw 
 -> WHERE first_name LIKE 'J%' 
 -> GROUP BY first_name 
 -> HAVING count(*) > 1 
 -> ORDER BY 1; 
+------------+----------+----------------+----------------+ 
| first_name | count(*) | min(last_name) | max(last_name) | 
+------------+----------+----------------+----------------+ 
| JAMIE | 2 | RICE | WAUGH | 
| JESSIE | 2 | BANKS | MILAM | 
+------------+----------+----------------+----------------+ 
2 rows in set (0.00 sec) 

此外，你還可以在查詢中連接視圖到其他表(或者甚至視圖)，如下所示：

mysql> SELECT cv.first_name, cv.last_name, p.amount 
 -> FROM customer_vw cv 
 -> INNER JOIN payment p 
 -> ON cv.customer_id = p.customer_id 
 -> WHERE p.amount >= 11; 
+------------+-----------+--------+ 
| first_name | last_name | amount | 
+------------+-----------+--------+ 
| KAREN | JACKSON | 11.99 | 
| VICTORIA | GIBSON | 11.99 | 
| VANESSA | SIMS | 11.99 | 
| ALMA | AUSTIN | 11.99 | 
| ROSEMARY | SCHMIDT | 11.99 | 
| TANYA | GILBERT | 11.99 | 
| RICHARD | MCCRARY | 11.99 | 
| NICHOLAS | BARFIELD | 11.99 | 
| KENT | ARSENAULT | 11.99 | 
| TERRANCE | ROUSH | 11.99 | 
+------------+-----------+--------+ 
10 rows in set (0.01 sec) 

此查詢將customer_vw視圖與payment表連接，以查找租賃電影花費了11美元或更多金額的客戶。

2. 為何要用視圖

在上一節中，我演示了一個簡單的視圖，它的目的是掩蓋customer.email列。雖然視圖通常被用于此種目的，但還有更多理由使用視圖，如下小節所述。

2.1 數據安全

如果你創建一個表并允許用戶查詢，那么他們將能夠訪問表中的每一列和每一行數據。但正如我前面提到的，你的表中有些列可能包含敏感信息，比如身份證號或信用卡號碼，把包括這些敏感數據在內的表數據公開給用戶訪問絕對不是一個好主意，而且還可能違反公司的隱私政策，甚至觸犯州或聯邦法律。

對于這些情況，最好的方法是保持表的私有性(即不向任何用戶授予select權限)，然后創建一個或多個視圖省略或者模糊(比如對customer_vw.email列采取'*****'替代部分內容)這些敏感信息。你還可以通過向視圖定義中添加where子句來限制一組用戶只能訪問哪些行。例如，下面的視圖定義將非活躍客戶排除在外：

CREATE VIEW active_customer_vw 
 (customer_id, 
 first_name, 
 last_name, 
 email 
 ) 
AS 
SELECT 
 customer_id, 
 first_name, 
 last_name, 
 concat(substr(email,1,2), '*****', substr(email, -4)) email 
FROM customer 
WHERE active = 1; 

如果將此視圖提供給市場營銷部門，他們將能夠避免向非活躍客戶發送信息，因為視圖的where子句中的條件將始終包含在查詢中。

注意

Oracle Database用戶還可以用另一種方法保證表中行和列的安全：虛擬私有數據庫(Virtual Private Database，VPD)。VPD允許你為表附加策略，然后服務器將根據需要修改用戶的查詢以執行此策略。例如，如果你制定了策略指定銷售和市場營銷部門的成員只能看到活躍客戶，則條件active=1將添加到他們對customer表的所有查詢中。

2.2 數據聚合

報表程序通常需要聚合數據，而視圖就是一種實現該功能的很好的方法，可以使數據看起來像是已經被預聚合并存儲在數據庫中。例如，假設一個應用程序每月生成一個報表，用于顯示每類電影的總銷售額，這樣經理就可以決定將哪些新電影添加到庫存中。你可以為他們提供以下視圖，而不是允許其直接針對表編寫查詢：

CREATE VIEW sales_by_film_category 
AS 
SELECT 
 c.name AS category, 
 SUM(p.amount) AS total_sales 
FROM payment AS p 
 INNER JOIN rental AS r ON p.rental_id = r.rental_id 
 INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id 
 INNER JOIN film AS f ON i.film_id = f.film_id 
 INNER JOIN film_category AS fc ON f.film_id = fc.film_id 
 INNER JOIN category AS c ON fc.category_id = c.category_id 
GROUP BY c.name 
ORDER BY total_sales DESC; 

這種方法給數據庫設計者提供很大的靈活性。如果將來某個時候你為了提高查詢性能，要將數據預聚合到表中而不是使用視圖求和，那么你可以創建一個film_category_sales表，用聚合數據加載該表，然后修改sales_by_film_category視圖定義以從此表中檢索數據。之后，所有使用sales_by_film_category視圖的查詢都將從新的film_category_sales表中檢索數據，這意味著用戶無需修改查詢就可以提高性能。

2.3 隱藏復雜性

部署視圖最常見的原因之一是為了保護終端用戶不受復雜性的影響。例如，假設每個月都會創建一個報表以顯示有關所有電影的信息，以及電影類別、電影中出現的演員數量、庫存中的總拷貝數以及每部電影的租賃數量。你可以提供如下視圖，而不是讓他們使用六個不同的表檢索數據：

CREATE VIEW film_stats 
AS 
SELECT f.film_id, f.title, f.description, f.rating, 
 (SELECT c.name 
 FROM category c 
 INNER JOIN film_category fc 
 ON c.category_id = fc.category_id 
 WHERE fc.film_id = f.film_id) category_name, 
 (SELECT count(*) 
 FROM film_actor fa 
 WHERE fa.film_id = f.film_id 
 ) num_actors, 
 (SELECT count(*) 
 FROM inventory i 
 WHERE i.film_id = f.film_id 
 ) inventory_cnt, 
 (SELECT count(*) 
 FROM inventory i 
 INNER JOIN rental r 
 ON i.inventory_id = r.inventory_id 
 WHERE i.film_id = f.film_id 
 ) num_rentals 
FROM film f; 

這個視圖定義很有趣，因為即使可以通過視圖檢索來自六個不同表的數據，查詢的from子句也只有一個表(film)。來自其他五個表的數據是使用標量子查詢生成的。如果有人使用此視圖但未引用category_name、num_actors、inventory_cnt或num_rentals列，則不會執行任何子查詢。這種方法允許在不連接其他五個表的情況下使用視圖從film表中提取描述性信息。

2.4 連接分區數據

一些數據庫設計將大型表分解為多個小塊以提高性能。例如，如果payment表變大了，設計者可能會決定將其分為兩個表：payment_current(保存最近六個月的數據)和payment_historical(保存六個月前的所有數據)。如果客戶希望查看某個特定客戶的所有交易，則需要同時查詢這兩個表。但是，通過創建一個視圖以查詢兩個表并組合查詢結果，可以使其看起來像是所有交易數據都存儲在一個表中。視圖定義如下：

CREATE VIEW payment_all 
 (payment_id, 
 customer_id, 
 staff_id, 
 rental_id, 
 amount, 
 payment_date, 
 last_update 
 ) 
AS 
SELECT payment_id, customer_id, staff_id, rental_id, 
 amount, payment_date, last_update 
FROM payment_historic 
UNION ALL 
SELECT payment_id, customer_id, staff_id, rental_id, 
 amount, payment_date, last_update 
FROM payment_current; 

在這種情況下使用視圖是一個好主意，因為它允許設計人員更改基礎數據結構而無需強制所有數據庫用戶修改其查詢。

3. 可更新的視圖

如果為用戶提供了一組用于數據檢索的視圖，但如果用戶還要修改同一數據，又該怎么辦呢?例如，強制用戶使用視圖檢索數據，然后允許用戶使用update或insert語句直接修改基礎表——這似乎有點奇怪。為此，MySQL、Oracle Database和SQL Server都允許在遵守某些規則的前提下通過視圖修改數據。對于MySQL，如果滿足以下條件，則視圖是可更新的：

• 不使用聚合函數(max()、min()、avg()等);
• 視圖不使用group by或having子句;
• select或from子句中不存在子查詢，且where子句中的任何子查詢都不引用from子句中的表;
• 視圖不使用union、union all或distinct;
• from子句至少包含一個表或可更新視圖;
• 如果有多個表或視圖，那么from子句只使用內部連接。

為了演示可更新視圖的實用性，我們從簡單的視圖定義開始，然后逐步深入更復雜的視圖。

3.1 更新簡單視圖

本章開頭的視圖非常簡單，讓我們看看下面這個例子：

CREATE VIEW customer_vw  
 (customer_id,  
 first_name,  
 last_name,  
 email  
 )  
AS  
SELECT  
 customer_id,  
 first_name,  
 last_name,  
 concat(substr(email,1,2), '*****', substr(email, -4)) email  
FROM customer; 

視圖customer_vw查詢單個表，其四列中只有一列是通過表達式派生出來的。此視圖定義沒有違反前面列出的任何限制，因此你可以使用它修改customer表中的數據。下面讓我們使用該視圖更新Mary Smith的姓氏(更新為Smith Allen)：

mysql> UPDATE customer_vw 
 -> SET last_name = 'SMITH-ALLEN' 
 -> WHERE customer_id = 1; 
Query OK, 1 row affected (0.11 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 

如你所見，該語句修改了一行，但我們還是檢查一下基礎customer表以驗證我們的更新：

mysql> SELECT first_name, last_name, email 
 -> FROM customer 
 -> WHERE customer_id = 1; 
+------------+-------------+-------------------------------+ 
| first_name | last_name | email | 
+------------+-------------+-------------------------------+ 
| MARY | SMITH-ALLEN | MARY.SMITH@sakilacustomer.org | 
+------------+-------------+-------------------------------+ 
1 row in set (0.00 sec) 

雖然可以以這種方式修改視圖中的大多數列，但卻無法修改email列，因為它是從表達式派生的：

mysql> UPDATE customer_vw 
 -> SET email = 'MARY.SMITH-ALLEN@sakilacustomer.org' 
 -> WHERE customer_id = 1; 
ERROR 1348 (HY000): Column 'email' is not updatable 

這種情況可能并不是一件壞事，因為創建視圖的主要原因是為了隱藏電子郵件地址。

如果要使用customer_vw視圖插入數據，那就不太合適了，因為包含派生列的視圖不能用于插入數據，即使派生列未包含在語句中。例如，下一條語句嘗試使用customer_vw視圖填充customer_id、first_name和last_name列：

mysql> INSERT INTO customer_vw  
 -> (customer_id,  
 -> first_name,  
 -> last_name)  
 -> VALUES (99999,'ROBERT','SIMPSON');  
ERROR 1471 (HY000): The target table customer_vw of the INSERT is not insertable-into 

現在你已經了解了簡單視圖的局限性，下一節將演示如何使用視圖連接多個表。

3.2 更新復雜視圖

雖然單表視圖確實很常見，但你遇到的許多視圖都會在基礎查詢的from子句中包含多個表。例如，下一個視圖連接customer、address、city和country表，以便可以方便地查詢客戶的所有數據：

CREATE VIEW customer_details  
AS  
SELECT c.customer_id,  
 c.store_id,  
 c.first_name,  
 c.last_name,  
 c.address_id,  
 c.active,  
 c.create_date,  
 a.address,  
 ct.city,  
 cn.country,  
 a.postal_code  
FROM customer c  
 INNER JOIN address a  
 ON c.address_id = a.address_id  
 INNER JOIN city ct  
 ON a.city_id = ct.city_id  
 INNER JOIN country cn  
 ON ct.country_id = cn.country_id; 

你可以使用此視圖更新customer或address表中的數據，如下語句所示：

mysql> UPDATE customer_details  
 -> SET last_name = 'SMITH-ALLEN', active = 0  
 -> WHERE customer_id = 1;  
Query OK, 1 row affected (0.10 sec)  
Rows matched: 1 Changed: 1 Warnings: 0  
mysql> UPDATE customer_details  
 -> SET address = '999 Mockingbird Lane'  
 -> WHERE customer_id = 1;  
Query OK, 1 row affected (0.06 sec)  
Rows matched: 1 Changed: 1 Warnings: 0 

第一個語句修改customer.last_name以及customer.active列，而第二條語句修改address.address列。你可能想知道，如果嘗試在一條語句中更新兩個表中的列，會發生什么情況，下面讓我們看看：

mysql> UPDATE customer_details 
 -> SET last_name = 'SMITH-ALLEN', 
 -> active = 0, 
 -> address = '999 Mockingbird Lane' 
 -> WHERE customer_id = 1; 
ERROR 1393 (HY000): Can not modify more than one base table  
 through a join view 'sakila.customer_details' 

如你所見，使用單個語句中修改兩個基礎表是不可行的，只能分別修改。接下來讓我們嘗試在兩個表中插入一些新客戶(customer_id=9998和9999)的數據：

mysql> INSERT INTO customer_details 
 -> (customer_id, store_id, first_name, last_name, 
 -> address_id, active, create_date) 
 -> VALUES (9998, 1, 'BRIAN', 'SALAZAR', 5, 1, now()); 
Query OK, 1 row affected (0.23 sec) 

此語句只填充customer表中的列并且工作正常。下面看一下如果擴展列列表以同時包含address表中的列會發生什么：

mysql> INSERT INTO customer_details  
 -> (customer_id, store_id, first_name, last_name,  
 -> address_id, active, create_date, address)  
 -> VALUES (9999, 2, 'THOMAS', 'BISHOP', 7, 1, now(),  
 -> '999 Mockingbird Lane');  
ERROR 1393 (HY000): Can not modify more than one base table   
 through a join view 'sakila.customer_details' 

此版本的語句包含跨兩個不同表的列，結果拋出異常。為了通過復雜視圖插入數據，你需要知道每個列的來源。由于創建視圖的目的是向終端用戶隱藏復雜性，因此如果用戶要熟悉視圖定義，那么這似乎就達不到隱藏復雜性的目的了。

注意

Oracle Database和SQL Server也允許通過視圖插入和更新數據，但與MySQL一樣有許多限制。如果你打算愿意編寫一些PL/SQL或Transact-SQL語句，那么可以使用名為替代觸發器(instead-of triggers)的功能，它允許你截取視圖的insert、update和delete語句，并編寫自定義代碼將這些更改包括在內。如果沒有這種類型的功能，通常會有太多的限制，使得通過視圖進行更新成為非平凡應用程序中的可行策略。