CREATE VIEW语法与使用-云原生数据仓库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;

最佳实践

更多详情，请参见通过视图管控数据权限。