文档

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的默认行为,符合标准,避免非预期的情况发生。并尽量避免在视图中使用*,带来不确定的语意和报错。

说明

查看企业版湖仓版集群的内核版本,请参见如何查看实例版本信息。如需升级内核版本,请联系技术支持。

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高权限账号执行如下操作:

    1. 创建账号user1,语句如下:

      CREATE USER user1 IDENTIFIED BY 'user1_pwd';
    2. 已创建数据库adb_demo,并在库中创建表t1,建表语句如下:

      Create Table `t1` (
       `id` bigint AUTO_INCREMENT,
       `id_province` bigint NOT NULL,
       `user_info` varchar,
       primary key (`id`)
      ) DISTRIBUTE 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 SECURITYINVOKER,语句如下:

      CREATE SQL SECURITY INVOKER VIEW v1 
        AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
    • 创建视图v2时,设置SQL SECURITYDEFINER,语句如下:

      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语句查询v1v3视图数据时则会报错。查询语句如下:

      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账号能够查询全部视图v1v2v3的数据,查询语句如下:

      SELECT * FROM v1

      SELECT * FROM v2

      SELECT * FROM v3

      执行上述3条查询语句均会返回相同的结果,结果如下:

      +-------------+-----------+
      | ID_PROVINCE | USER_INFO |
      +-------------+-----------+
      |           1 | Tom       |
      |           1 | Jerry     |
      +-------------+-----------+

最佳实践

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