南大通用GBase 8a检查视图View里面引用的表或视图是否存在

GBase 8a支持视图View。 在创建视图时，其内部引用的表或其它视图必须存在。但如果创建成功后，引用的表或视图被删除了，则会导致查询视图报错。GBase 8a可以通过check table来检查视图引用内容是否正确。

语法

check table [vc.][db.]VIEW_NAME

样例

如下创建一个视图，然后将引用的表删除，看看check table的返回结果。

gbase> create table a(id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.07)

gbase> insert into a values(8);
Query OK, 1 row affected (Elapsed: 00:00:00.07)

gbase> select * from a;
+------+
| id   |
+------+
|    8 |
+------+
1 row in set (Elapsed: 00:00:00.02)

gbase> create view v_a as select * from a;
Query OK, 0 rows affected (Elapsed: 00:00:00.06)

gbase> select * from v_a;
+------+
| id   |
+------+
|    8 |
+------+
1 row in set (Elapsed: 00:00:00.02)

gbase> check table v_a;
+----------+-------+----------+----------------------------------------------------------------------------------+
| Table    | Op    | Msg_type | Msg_text                                                                         |
+----------+-------+----------+----------------------------------------------------------------------------------+
| test.v_a | check | Error    | (GBA-02SC-1001) The query includes syntax that is not supported by the gcluster. |
| test.v_a | check | status   | OK                                                                               |
+----------+-------+----------+----------------------------------------------------------------------------------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> drop table a;
Query OK, 0 rows affected (Elapsed: 00:00:00.06)

gbase> check table v_a;
+----------+-------+----------+----------------------------------------------------------------------------------------------------------------------------+
| Table    | Op    | Msg_type | Msg_text                                                                                                                   |
+----------+-------+----------+----------------------------------------------------------------------------------------------------------------------------+
| test.v_a | check | Error    | Table 'vc1.test.a' doesn't exist                                                                                           |
| test.v_a | check | Error    | View 'test.v_a' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
| test.v_a | check | error    | Corrupt                                                                                                                    |
+----------+-------+----------+----------------------------------------------------------------------------------------------------------------------------+
3 rows in set (Elapsed: 00:00:00.00)

gbase>

总结

视图内缺失的表或其它视图，可以用check table检查出来，方便视图依赖故障的排查。