建立視圖 - AnalyticDB

視圖（View）是一種虛擬表，由一個或多個表的查詢結果構成的，但它並不儲存實際的資料。視圖可以簡化複雜查詢，增強資料安全性。本文介紹如何使用CREATE VIEW文法建立視圖。

注意事項

AnalyticDB for MySQL叢集的視圖與MySQL視圖相容性：

3.1.9.0之前版本
AnalyticDB for MySQL叢集未相容MySQL預設行為，當您的視圖有新增或刪除列的變更，在使用SELECT * FROM <view_name>;查詢時，行為上會與MySQL不一致，檢測到列數不相等，直接判定視圖不可用，出現報錯View '<view_name>' is stale; it must be re-created。
3.1.9.0及之後版本
AnalyticDB for MySQL叢集相容了MySQL預設行為。在建立視圖時，會將SQL輸出的*解析成具體列，再儲存SQL，在增加和刪除列的行為上不再產生影響。

因此，推薦使用3.1.9.0及之後版本的叢集建立視圖，以確保可以相容MySQL的預設行為，避免在視圖中使用*，出現不確定的語意和報錯等非預期的情況。

說明

查看湖倉版叢集的核心版本，請執行SELECT adb_version();。如需升級核心版本，請聯絡支援人員。

3.1.9.0及之後版本的AnalyticDB for MySQL叢集相容MySQL行為後，可能會出現特殊副作用。例如，將列名為C的列RENAME為D列時，在需要準確引用A,B,C三列，由於C列已不存在，會導致視圖不可用報錯，這是合理的預期。即使上層的查詢最終沒有使用C列也會報錯，因為裁剪列是最佳化階段，而檢查SQL文法和鑒權是解析階段。然而，3.1.9.0之前版本實現，由於只檢查視圖列數（RENAME後列數相等）和當前*所引用列數的一致性，因此所有檢查都能通過，不會報錯。視圖可用性檢查通過後，C列被映射到基表順序中的第三列，甚至，在RENAME後再查詢C列時，依然不會報錯，此時查詢的結果是非預期的。

如果您的業務確實需要相容AnalyticDB for MySQL3.1.9.0之前版本的特殊行為，在建立視圖時，可以加上特定Hint或配置控制達到預期。

單個視圖，增加/*+LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false*/。樣本如下：

/*+LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false*/
CREATE VIEW v0
AS
SELECT * FROM base0;

全域配置，通過SET ADB_CONFIG LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE = false;實現。

文法

CREATE
[OR REPLACE]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name
AS select_statement;

參數	是否必填	說明
`OR REPLACE`	選填	根據是否存在重名視圖，選擇對應的規則來建立視圖。具體規則如下：若不存在重名視圖，AnalyticDB for MySQL會直接建立一個新視圖。若存在重名視圖，AnalyticDB for MySQL會先刪除原有的重名視圖，再重新建立。說明若未設定該參數，當建立的視圖名稱與已存在視圖重名時，會建立失敗。
`[SQL SECURITY]`	選填	定義查詢檢視的資料時的安全驗證方式，支援如下取值： `INVOKER`：表示按照`INVOKER`（調用者）的身份來執行查詢SQL。該安全驗證方式下，系統會在查詢檢視資料時，驗證調用者是否擁有如下許可權：視圖的查詢許可權。視圖所引用對象的查詢許可權。僅當使用者同時擁有上述許可權時，才能查詢檢視的資料。 `DEFINER`：表示按照`DEFINER`（定義者）的身份來執行查詢SQL。該安全驗證方式下，系統會在查詢檢視資料時，驗證調用者和定義者是否具有如下許可權：調用者具備視圖的查詢許可權。定義者具備視圖所引用對象的查詢許可權。如果定義者被撤權後，即使調用者仍擁有視圖的查詢許可權，仍然會出現無法查詢檢視的問題。說明若未設定該參數，AnalyticDB for MySQL預設使用`INVOKER`安全驗證方式，即調用者查詢檢視資料時，需要同時擁有視圖的查詢許可權和視圖所引用對象的查詢許可權。需為僅V3.1.4.0或以上版本的AnalyticDB for MySQL叢集支援該配置。如何查看叢集核心版本，請參見如何查看執行個體版本資訊。如需升級核心版本，請聯絡支援人員。
`view_name`	必填	視圖的名字。說明您也可以在視圖名字前加上資料庫名稱來定義該視圖所屬的資料庫，例如`adb_demo.view`。若不添加，預設該視圖屬於當前資料庫。
`select_statement`	必填	視圖中的資料來源。

樣本

資料準備

通過AnalyticDB for MySQL高許可權帳號執行如下操作：

建立帳號user1，語句如下：

CREATE USER user1 IDENTIFIED BY 'user1_pwd';

已建立資料庫adb_demo，並在庫中建立表t1，建表語句如下：

Create Table `t1` (
 `id` bigint AUTO_INCREMENT,
 `id_province` bigint NOT NULL,
 `user_info` varchar,
 primary key (`id`)
) DISTRIBUTED BY HASH(`id`);

往表t1中插入測試資料，語句如下：

INSERT INTO t1(id_province,user_info) VALUES (1,'Tom'),(1,'Jerry'),(2,'Jerry'),(3,'Mark');

建立視圖
說明
本文樣本以在建立視圖（視圖資料來源於表t1）時設定不同的安全驗證方式為例，介紹DEFINER、INVOKER的不同許可權效果。
- 建立視圖v1時，設定SQL SECURITY為INVOKER，語句如下：
```
CREATE SQL SECURITY INVOKER VIEW v1 
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
- 建立視圖v2時，設定SQL SECURITY為DEFINER，語句如下：
```
CREATE SQL SECURITY DEFINER VIEW v2 
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
- 建立視圖v3時，不設定SQL SECURITY（即系統預設使用INVOKER），語句如下：
```
CREATE VIEW v3 
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
許可權對比
- 僅通過高許可權帳號授予user1查詢3個視圖的許可權，語句如下：
```
GRANT SELECT ON adb_demo.v1 TO 'user1'@'%';
GRANT SELECT ON adb_demo.v2 TO 'user1'@'%';
GRANT SELECT ON adb_demo.v3 TO 'user1'@'%';
```
  此時，使用user1帳號串連AnalyticDB for MySQL叢集的adb_demo資料庫後，user1僅能查詢檢視v2資料。查詢語句如下：
```
SELECT * FROM v2;
```
  查詢結果如下：
```
+-------------+-----------+
| ID_PROVINCE | USER_INFO |
+-------------+-----------+
|           1 | Tom       |
|           1 | Jerry     |
+-------------+-----------+
```
  而通過SELECT語句查詢v1或v3視圖資料時則會報錯。查詢語句如下：
```
SELECT * FROM v1
```
  或
```
SELECT * FROM v3
```
  執行上述語句進行查詢時，均會返回如下錯誤：
```
ERROR 1815 (HY000): [20049, 2021083110261019216818804803453927668] : Failed analyzing stored view
```
- 在授予user1查詢3個視圖的許可權基礎上，再通過高許可權帳號授予user1查詢t1表的許可權，語句如下：
```
GRANT SELECT ON adb_demo.t1 to user1@'%';
```
  此時，使用user1帳號串連AnalyticDB for MySQL叢集的adb_demo資料庫後，user1帳號能夠查詢全部視圖v1、v2和v3的資料，查詢語句如下：
```
SELECT * FROM v1;
```
  或
```
SELECT * FROM v2;
```
  或
```
SELECT * FROM v3;
```
  執行上述3條查詢語句均會返回相同的結果，結果如下：
```
+-------------+-----------+
| ID_PROVINCE | USER_INFO |
+-------------+-----------+
|           1 | Tom       |
|           1 | Jerry     |
+-------------+-----------+
```

常見問題

基表中設定的列名是小寫，為什麼在建立視圖後，視圖結果集中的列名會變成大寫？

AnalyticDB for MySQL視圖結果集列名預設大小寫不敏感。如果您希望視圖結果集列名為小寫，您可以修改VIEW_OUTPUT_NAME_CASE_SENSITIVE的值為true，表示大小寫敏感。修改方法如下：

SET ADB_CONFIG VIEW_OUTPUT_NAME_CASE_SENSITIVE=true;

最佳實務

更多詳情，請參見通過視圖管控資料許可權。