CREATE VIEW
视图(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;
参数 | 是否必填 | 说明 |
| 选填 | 根据是否存在重名视图,选择对应的规则来创建视图。具体规则如下:
说明 若未设置该参数,当新建的视图名称与已存在视图重名时,会创建失败。 |
| 定义查询视图的数据时的安全验证方式,支持如下取值:
说明
| |
| 必填 | 视图的名字。 说明 您也可以在视图名字前加上数据库名称来定义该视图所属的数据库,例如 |
| 视图中的数据来源。 |
示例
数据准备
通过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 | +-------------+-----------+
最佳实践
更多详情,请参见通过视图管控数据权限。