GBase 8a数据库集群,提供了元数据表 information_schema.view 来获得视图的情况。请注意在show tables时,默认也会把view视图显示出来,可以通过table_type的值进行区分。
注意:因为视图和表在相同的目录下,如表很多,查询会耗时很长。目前没有类似table_distributon样的表来提升性能。
目录导航
参考
建议:在表很多时,将视图建立到【独立的数据库】下面。比如业务库叫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 集群查看库里有哪些视图(类似 show views)》有2条评论
评论已关闭。