[[407941]]

本文轉(zhuǎn)載自微信公眾號(hào)「數(shù)據(jù)與智能」，作者Alan Beaulieu。轉(zhuǎn)載本文請(qǐng)聯(lián)系數(shù)據(jù)與智能公眾號(hào)。

設(shè)計(jì)良好的應(yīng)用程序通常會(huì)在保持實(shí)現(xiàn)細(xì)節(jié)私有性的同時(shí)公開(kāi)一個(gè)公共接口，從而在不影響終端用戶的情況下支持將來(lái)的設(shè)計(jì)變動(dòng)。在設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)，通過(guò)保持表的私有性并允許用戶僅通過(guò)一組視圖訪問(wèn)數(shù)據(jù)，你可以獲得類似的結(jié)果。本章致力于定義什么是視圖、如何創(chuàng)建它們、何時(shí)使用它們以及如何使用它們。

1. 什么是視圖

視圖其實(shí)就是一種數(shù)據(jù)查詢機(jī)制。與表不同，視圖不涉及數(shù)據(jù)存儲(chǔ)，所以不必?fù)?dān)心視圖會(huì)占用磁盤空間。可以通過(guò)命名select語(yǔ)句來(lái)創(chuàng)建視圖，將其保存以供其他人使用。其他用戶可以使用該視圖訪問(wèn)數(shù)據(jù)，就像他們直接查詢表一樣(實(shí)際上，他們甚至可能不知道自己正在使用視圖)。

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

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; 

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

執(zhí)行create view語(yǔ)句時(shí)，數(shù)據(jù)庫(kù)服務(wù)器只簡(jiǎn)單地存儲(chǔ)視圖定義以供將來(lái)使用。若不執(zhí)行查詢，也就不會(huì)檢索或存儲(chǔ)任何數(shù)據(jù)。創(chuàng)建視圖后，用戶可以像查詢表一樣使用它進(jìn)行查詢，如下所示：

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表的四列，但前面的查詢只檢索其中三列。正如你將在本章后面看到的，如果視圖中的某些列被附加到函數(shù)或子查詢，那么這會(huì)是一個(gè)重要的區(qū)別。

從用戶的角度來(lái)看，視圖看起來(lái)就像一個(gè)表。要想知道視圖中有哪些列是可用的，可以使用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) 

在通過(guò)視圖進(jìn)行查詢時(shí)，可以自由使用select語(yǔ)句中的任何子句，包括group by、having和order by。舉個(gè)例子：

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表連接，以查找租賃電影花費(fèi)了11美元或更多金額的客戶。

2. 為何要用視圖

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

2.1 數(shù)據(jù)安全

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

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

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; 

如果將此視圖提供給市場(chǎng)營(yíng)銷部門，他們將能夠避免向非活躍客戶發(fā)送信息，因?yàn)橐晥D的where子句中的條件將始終包含在查詢中。

注意

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

2.2 數(shù)據(jù)聚合

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

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; 

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

2.3 隱藏復(fù)雜性

部署視圖最常見(jiàn)的原因之一是為了保護(hù)終端用戶不受復(fù)雜性的影響。例如，假設(shè)每個(gè)月都會(huì)創(chuàng)建一個(gè)報(bào)表以顯示有關(guān)所有電影的信息，以及電影類別、電影中出現(xiàn)的演員數(shù)量、庫(kù)存中的總拷貝數(shù)以及每部電影的租賃數(shù)量。你可以提供如下視圖，而不是讓他們使用六個(gè)不同的表檢索數(shù)據(jù)：

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; 

這個(gè)視圖定義很有趣，因?yàn)榧词箍梢酝ㄟ^(guò)視圖檢索來(lái)自六個(gè)不同表的數(shù)據(jù)，查詢的from子句也只有一個(gè)表(film)。來(lái)自其他五個(gè)表的數(shù)據(jù)是使用標(biāo)量子查詢生成的。如果有人使用此視圖但未引用category_name、num_actors、inventory_cnt或num_rentals列，則不會(huì)執(zhí)行任何子查詢。這種方法允許在不連接其他五個(gè)表的情況下使用視圖從film表中提取描述性信息。

2.4 連接分區(qū)數(shù)據(jù)

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

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; 

在這種情況下使用視圖是一個(gè)好主意，因?yàn)樗试S設(shè)計(jì)人員更改基礎(chǔ)數(shù)據(jù)結(jié)構(gòu)而無(wú)需強(qiáng)制所有數(shù)據(jù)庫(kù)用戶修改其查詢。

3. 可更新的視圖

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

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

為了演示可更新視圖的實(shí)用性，我們從簡(jiǎn)單的視圖定義開(kāi)始，然后逐步深入更復(fù)雜的視圖。

3.1 更新簡(jiǎn)單視圖

本章開(kāi)頭的視圖非常簡(jiǎn)單，讓我們看看下面這個(gè)例子：

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查詢單個(gè)表，其四列中只有一列是通過(guò)表達(dá)式派生出來(lái)的。此視圖定義沒(méi)有違反前面列出的任何限制，因此你可以使用它修改customer表中的數(shù)據(jù)。下面讓我們使用該視圖更新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 

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

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) 

雖然可以以這種方式修改視圖中的大多數(shù)列，但卻無(wú)法修改email列，因?yàn)樗菑谋磉_(dá)式派生的：

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

這種情況可能并不是一件壞事，因?yàn)閯?chuàng)建視圖的主要原因是為了隱藏電子郵件地址。

如果要使用customer_vw視圖插入數(shù)據(jù)，那就不太合適了，因?yàn)榘缮械囊晥D不能用于插入數(shù)據(jù)，即使派生列未包含在語(yǔ)句中。例如，下一條語(yǔ)句嘗試使用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 

現(xiàn)在你已經(jīng)了解了簡(jiǎn)單視圖的局限性，下一節(jié)將演示如何使用視圖連接多個(gè)表。

3.2 更新復(fù)雜視圖

雖然單表視圖確實(shí)很常見(jiàn)，但你遇到的許多視圖都會(huì)在基礎(chǔ)查詢的from子句中包含多個(gè)表。例如，下一個(gè)視圖連接customer、address、city和country表，以便可以方便地查詢客戶的所有數(shù)據(jù)：

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表中的數(shù)據(jù)，如下語(yǔ)句所示：

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 

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

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' 

如你所見(jiàn)，使用單個(gè)語(yǔ)句中修改兩個(gè)基礎(chǔ)表是不可行的，只能分別修改。接下來(lái)讓我們嘗試在兩個(gè)表中插入一些新客戶(customer_id=9998和9999)的數(shù)據(jù)：

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) 

此語(yǔ)句只填充customer表中的列并且工作正常。下面看一下如果擴(kuò)展列列表以同時(shí)包含address表中的列會(huì)發(fā)生什么：

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' 

此版本的語(yǔ)句包含跨兩個(gè)不同表的列，結(jié)果拋出異常。為了通過(guò)復(fù)雜視圖插入數(shù)據(jù)，你需要知道每個(gè)列的來(lái)源。由于創(chuàng)建視圖的目的是向終端用戶隱藏復(fù)雜性，因此如果用戶要熟悉視圖定義，那么這似乎就達(dá)不到隱藏復(fù)雜性的目的了。

注意

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