南大通用GBase8a 集群查看库里有哪些视图(类似 show views)

GBase 8a数据库集群，提供了元数据表 information_schema.view 来获得视图的情况。请注意在show tables时，默认也会把view视图显示出来，可以通过table_type的值进行区分。

注意：因为视图和表在相同的目录下，如表很多，查询会耗时很长。目前没有类似table_distributon样的表来提升性能。

参考

GBase 8a的show命令列表和使用

建议：在表很多时，将视图建立到【独立的数据库】下面。比如业务库叫testdb,视图库叫v_testdb。

语法

通过SHOW 命令

类似表，将类型改成VIEW就可以了。

show tables where table_type='VIEW'

等同于 show views的效果

通过TABLES系统元数据表查询

随意过滤。

gbase> select table_schema,table_name,table_type from information_schema.tables where table_type='view';
+--------------+------------+------------+
| table_schema | table_name | table_type |
+--------------+------------+------------+
| testdb       | v_t1       | VIEW       |
| testdb       | v_t2       | VIEW       |
| testmirror   | v_t1       | VIEW       |
+--------------+------------+------------+
3 rows in set (Elapsed: 00:00:00.02)

通过VIEWS的元数据表

gbase> select * from information_schema.views;
+---------------+----------+--------------+------------+-------------------------------------------------------------------------------------------------------------------+--------------+--------------+---------+---------------+----------------------+----------------------+
| TABLE_CATALOG | TABLE_VC | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION                                                                                                   | CHECK_OPTION | IS_UPDATABLE | DEFINER | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+---------------+----------+--------------+------------+-------------------------------------------------------------------------------------------------------------------+--------------+--------------+---------+---------------+----------------------+----------------------+
| NULL          | vc01     | testdb       | v_t1       | select `vc00001`.`testdb`.`t1`.`id` AS `id`,`vc00001`.`testdb`.`t1`.`name` AS `name` from `vc00001`.`testdb`.`t1` | NONE         | NO           | root@%  | DEFINER       | utf8                 | utf8_general_ci      |
| NULL          | vc01     | testdb       | v_t2       | select `vc00001`.`testdb`.`t2`.`id` AS `id` from `vc00001`.`testdb`.`t2`                                          | NONE         | NO           | root@%  | DEFINER       | utf8                 | utf8_general_ci      |
| NULL          | vc01     | testmirror   | v_t1       | select `vc00001`.`testmirror`.`t1`.`id` AS `id` from `vc00001`.`testmirror`.`t1`                                  | NONE         | NO           | root@%  | DEFINER       | utf8                 | utf8_general_ci      |
+---------------+----------+--------------+------------+-------------------------------------------------------------------------------------------------------------------+--------------+--------------+---------+---------------+----------------------+----------------------+
3 rows in set (Elapsed: 00:00:00.01)

参考

GBase8a 集群查看库里有哪些表，或某张表是否存在

GBase 8a 元数据表介绍 Information_schema