GBase 8a提供了show 管理命令，来查看各种数据库信息，本文只是语法级的简单介绍和一些使用样例。
目录导航
通用语法说明
指一些功能的参数，有相同的语法结构，后面就不每个都单独写一次了。
full
显示更完整的信息。默认显示的是精简的标准信息，full显示的列更多一些。
like
支持模糊匹配的部分，比如表名，列名等。
where
属于like的增强，不单单是名字，还可以对返回信息的多个列进行SQL级别的组合过滤。需要注意的是，列名字有时是关键字，建议用栓引号包围起来，比如
where "table_name" like '%abcd%'vc
对应V95版本开始支持的虚拟集群；
1、V8没有这部分
2、如果V9没有启用虚拟集群，这部分也可以忽略
database
数据库名。如果不指定则默认是当前数据库。
show columns
显示指定表的列信息
gbase> show columns from t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| v1 | varchar(10) | YES | | NULL | |
| v2 | varchar(10) | YES | | NULL | |
| v3 | varchar(10) | YES | | NULL | |
| v4 | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (Elapsed: 00:00:00.00)
gbase> show columns from t2 from testdb;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| v1 | varchar(10) | YES | | NULL | |
| v2 | varchar(10) | YES | | NULL | |
| v3 | varchar(10) | YES | | NULL | |
| v4 | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (Elapsed: 00:00:00.00)
gbase> show columns from t2 like '%1%';
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| v1 | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show full columns from testdb.t2 where type like '%varchar%' and "key"='';
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| v1 | varchar(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| v2 | varchar(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| v3 | varchar(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| v4 | varchar(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set (Elapsed: 00:00:00.00)
gbase>
show create
显示对象的创建语句。格式全部是
show create OBJECT_TYPE objectName
SHOW CREATE FUNCTION
自定义函数的创建语句
SHOW CREATE PROCEDURE
存储过程创建语句
SHOW CREATE PUBLIC SYNONYM
光有的同义词创建语句
SHOW CREATE SYNONYM
私有的同义词创建语句
SHOW CREATE VIEW
视图创建语句。
show databases;
显示GBase里，当前用户能访问的数据库，有哪些库。
gbase> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| abc |
| db1 |
| db2 |
| dbmain |
| gbase |
| gclusterdb |
| gctmpdb |
| test |
| testdb |
+--------------------+
11 rows in set (Elapsed: 00:00:00.00)
gbase> show databases like '%db%';
+-----------------+
| Database (%db%) |
+-----------------+
| db1 |
| db2 |
| dbmain |
| gclusterdb |
| gctmpdb |
| testdb |
+-----------------+
6 rows in set (Elapsed: 00:00:00.00)
show distribution tables
显示express表的列表，包括分布表和复制表。
gbase> create table db1_t2_rep(id int)replicated;
Query OK, 0 rows affected (Elapsed: 00:00:01.27)
gbase> show distribution tables;
+--------+------------+-------------+
| dbName | tbName | isReplicate |
+--------+------------+-------------+
| db1 | db1_t2_rep | YES |
| db1 | db1_t1 | NO |
+--------+------------+-------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> show distribution tables like '%1%';
+--------+------------+-------------+
| dbName | tbName | isReplicate |
+--------+------------+-------------+
| db1 | db1_t2_rep | YES |
| db1 | db1_t1 | NO |
+--------+------------+-------------+
2 rows in set (Elapsed: 00:00:00.00)
show engines
gbase> show engines;
+-----------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+-----------+---------+-----------------------------------------------------------+--------------+------+------------+
| MRG_GSSYS | YES | Collection of identical GsSYS tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| EXPRESS | DEFAULT | Express storage engine | YES | YES | NO |
| GsSYS | YES | GsSYS engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+-----------+---------+-----------------------------------------------------------+--------------+------+------------+
5 rows in set (Elapsed: 00:00:00.00)
show errors
显示错误信息
gbase> show errors;
Empty set (Elapsed: 00:00:00.00)
gbase> show errors limit 3,1;
Empty set (Elapsed: 00:00:00.00)
show fields
与show columns一样，别名。
show full tables
包含表类型， 视图是VIEW, 表是BASE TABLE
gbase> show full tables;
+------------------+------------+
| Tables_in_testdb | Table_type |
+------------------+------------+
| hash | BASE TABLE |
| nodedatamap | BASE TABLE |
| t | BASE TABLE |
| t1 | BASE TABLE |
| t2 | BASE TABLE |
| t3 | BASE TABLE |
| t4 | BASE TABLE |
| tdecimal | BASE TABLE |
| tdecimal2 | BASE TABLE |
| thash | BASE TABLE |
| tlike | BASE TABLE |
| tlike2 | BASE TABLE |
| tmp_nodedatamap | BASE TABLE |
| tr | BASE TABLE |
| tsi | BASE TABLE |
| tt | BASE TABLE |
| tti | BASE TABLE |
| ttt | BASE TABLE |
| v_tr | VIEW |
+------------------+------------+
19 rows in set (Elapsed: 00:00:00.00)
show function status
显示自定义函数的列表。
gbase> delimiter //
gbase> create function f_now() returns datetime begin return now();end //
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
gbase> delimiter ;
gbase> select f_now();
+---------------------+
| f_now() |
+---------------------+
| 2020-12-14 14:54:43 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show function status;
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| db1 | f_now | FUNCTION | root@% | 2020-12-14 14:54:26 | 2020-12-14 14:54:26 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
| testdb | gcadmin | FUNCTION | root@% | 2020-11-19 10:19:34 | 2020-11-19 10:19:34 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> show function status from db1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your GBase server version for the right syntax to use near 'from db1' at line 1
gbase> show function status where db='db1';
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| db1 | f_now | FUNCTION | root@% | 2020-12-14 14:54:26 | 2020-12-14 14:54:26 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase>
show gcluster entry
显示连接数最少的节点信息。
gbase> show gcluster entry;
+------------+------+
| IP | Port |
+------------+------+
| 10.0.2.201 | 5258 |
+------------+------+
1 row in set (Elapsed: 00:00:00.00)
show gcluster nodes
获得集群所有调度/管理cooridinator节点
gbase> show gcluster nodes;
+------------+------------+--------------+--------+-----------+
| Id | ip | name | status | datastate |
+------------+------------+--------------+--------+-----------+
| 1694629898 | 10.0.2.101 | coordinator1 | online | 0 |
+------------+------------+--------------+--------+-----------+
1 row in set (Elapsed: 00:00:00.00)
show grants
显示用户的授权情况。
gbase> show grants;
+-----------------------------------------------------------------------------+
| Grants for root@% |
+-----------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION TASK_PRIORITY 2 |
+-----------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show grants for abc;
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for abc@% |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'abc'@'%' IDENTIFIED BY PASSWORD '*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WITH TASK_PRIORITY 2 |
| GRANT ALL PRIVILEGES ON "abc".* TO 'abc'@'%' |
| GRANT ALL PRIVILEGES ON "testdb".* TO 'abc'@'%' |
+-------------------------------------------------------------------------------------------------------------------------+
3 rows in set (Elapsed: 00:00:00.00)
show indexs
查看索引。目前express表只有global hash索引和grouped的行存列两种。
gbase> create index idx_v2 on t2(v2) key_block_size=4096 using hash global;
Query OK, 0 rows affected (Elapsed: 00:00:01.05)
Records: 0 Duplicates: 0 Warnings: 0
gbase> alter table t2 add grouped grp_234(v2,v3,v4);
Query OK, 1 row affected (Elapsed: 00:00:00.51)
Records: 1 Duplicates: 1 Warnings: 0
gbase> show index from testdb.t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
| t2 | 1 | idx_v2 | 1 | v2 | NULL | NULL | NULL | NULL | YES | GLOBAL HASH | |
| t2 | 1 | grp_234 | 1 | v2 | NULL | NULL | NULL | NULL | YES | GROUPED | |
| t2 | 1 | grp_234 | 2 | v3 | NULL | NULL | NULL | NULL | YES | GROUPED | |
| t2 | 1 | grp_234 | 3 | v4 | NULL | NULL | NULL | NULL | YES | GROUPED | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
4 rows in set (Elapsed: 00:00:00.00)
show nodes
显示计算节点的信息；
gbase> show nodes;
+------------+------------+-------+--------------+----------------+--------+-----------+
| Id | ip | name | primary part | duplicate part | status | datastate |
+------------+------------+-------+--------------+----------------+--------+-----------+
| 3389128714 | 10.0.2.202 | node2 | n1 | n2 | online | 0 |
| 3372351498 | 10.0.2.201 | node1 | n2 | n1 | online | 0 |
+------------+------------+-------+--------------+----------------+--------+-----------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> show gcluster nodes;
+------------+------------+--------------+--------+-----------+
| Id | ip | name | status | datastate |
+------------+------------+--------------+--------+-----------+
| 3372351498 | 10.0.2.201 | coordinator1 | online | 0 |
+------------+------------+--------------+--------+-----------+
1 row in set (Elapsed: 00:00:00.00)
show open tables
gbase> show open tables;
+------------+-------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+------------+-------------------+--------+-------------+
| testdb | t1 | 0 | 0 |
| testdb | t2 | 0 | 0 |
| gbase | event | 0 | 0 |
| gclusterdb | audit_log_express | 0 | 0 |
+------------+-------------------+--------+-------------+
4 rows in set (Elapsed: 00:00:00.00)
gbase> show open tables from testdb;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| testdb | t1 | 0 | 0 |
| testdb | t2 | 0 | 0 |
+----------+-------+--------+-------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> show open tables from testdb where "table" like '%1%' and in_use=0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| testdb | t1 | 0 | 0 |
+----------+-------+--------+-------------+
1 row in set (Elapsed: 00:00:00.00)
SHOW PRIORITIES
显示资源管控的当前默认优先级。
gbase> SHOW PRIORITIES ;
+-----------+-------+----------+-----------------+--------+------------------------------------------------------------------------------------------------------------------------+
| node_name | group | priority | priority_weight | status | description |
+-----------+-------+----------+-----------------+--------+------------------------------------------------------------------------------------------------------------------------+
| node1 | 0 | 0 | 20 | OFF | control group path: "gbase/gbase/ugroup0/upriority0 " parameters: " cpuset= memset= cpu_shares= blkio_weight= " |
| node1 | 0 | 1 | 40 | OFF | control group path: "gbase/gbase/ugroup0/upriority1 " parameters: " cpuset= memset= cpu_shares= blkio_weight= " |
| node1 | 0 | 2 | 60 | OFF | control group path: "gbase/gbase/ugroup0/upriority2 " parameters: " cpuset= memset= cpu_shares= blkio_weight= " |
| node1 | 0 | 3 | 80 | OFF | control group path: "gbase/gbase/ugroup0/upriority3 " parameters: " cpuset= memset= cpu_shares= blkio_weight= " |
........... 忽略
| node2 | 15 | 2 | 60 | OFF | control group path: "gbase/gbase/ugroup15/upriority2 " parameters: " cpuset= memset= cpu_shares= blkio_weight= " |
| node2 | 15 | 3 | 80 | OFF | control group path: "gbase/gbase/ugroup15/upriority3 " parameters: " cpuset= memset= cpu_shares= blkio_weight= " |
+-----------+-------+----------+-----------------+--------+------------------------------------------------------------------------------------------------------------------------+
128 rows in set (Elapsed: 00:00:00.00)
show procedure status
显示自定义存储过程的列表。
gbase> delimiter //
gbase> create procedure p_now() begin select now(); end;//
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> delimiter ;
gbase> call p_now();
+---------------------+
| now() |
+---------------------+
| 2020-12-14 14:58:13 |
+---------------------+
1 row in set (Elapsed: 00:00:00.01)
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> show procedure status;
+------------------------------------------------------------------+------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------------------------------------------------------------+------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| db1 | p_now | PROCEDURE | root@% | 2020-12-14 14:58:05 | 2020-12-14 14:58:05 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+------------------------------------------------------------------+------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (Elapsed: 00:00:00.00)
show processlist
显示进程信息。请参考 GBase8a 显示集群正在跑的SQL进程show [full | detail] processlist
show schemas
和 show databases完全相同，显示数据库。
show status
显示运行状态信息。参考 GBase 8a集群查看当前运行状态，内存使用情况
show table locks
显示表的节点锁使用情况。用于在计算节点gnode查看锁。
show table status
显示表的当前详细状态，包括磁盘占用等。数据来源于information_schema.tables的元数据表。
提示：如果表很多，比如几万以上，不建议不带条件，避免磁盘大量随机读取。
gbase> use db1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase>
gbase> show table status;
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Limit_storage_size | storage_size | table_data_size | Comment | local_hash_index_file_size | global_hash_index_file_size |
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| db1_t1 | EXPRESS | 10 | Compressed | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2020-12-14 08:28:45 | 2020-12-14 08:28:45 | NULL | utf8_general_ci | NULL | avg_row_length=5 | 0 | 414 | 0 | | 0 | 0 |
| v_t1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL |
| v_t1_178291728712 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL |
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
3 rows in set (Elapsed: 00:00:00.00)
gbase> show table status from db1;
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Limit_storage_size | storage_size | table_data_size | Comment | local_hash_index_file_size | global_hash_index_file_size |
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| db1_t1 | EXPRESS | 10 | Compressed | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2020-12-14 08:28:45 | 2020-12-14 08:28:45 | NULL | utf8_general_ci | NULL | avg_row_length=5 | 0 | 414 | 0 | | 0 | 0 |
| v_t1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL |
| v_t1_178291728712 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL |
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
3 rows in set (Elapsed: 00:00:00.01)
gbase> show table status from db1 like '%1%';
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Limit_storage_size | storage_size | table_data_size | Comment | local_hash_index_file_size | global_hash_index_file_size |
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| db1_t1 | EXPRESS | 10 | Compressed | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2020-12-14 08:28:45 | 2020-12-14 08:28:45 | NULL | utf8_general_ci | NULL | avg_row_length=5 | 0 | 414 | 0 | | 0 | 0 |
| v_t1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL |
| v_t1_178291728712 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL |
+-------------------+---------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+------------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
3 rows in set (Elapsed: 00:00:00.00)
gbase> show table status from db1 where name like '%1%' and comment='VIEW';
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Limit_storage_size | storage_size | table_data_size | Comment | local_hash_index_file_size | global_hash_index_file_size |
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
| v_t1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL |
| v_t1_178291728712 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW | NULL | NULL |
+-------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+--------------------+--------------+-----------------+---------+----------------------------+-----------------------------+
2 rows in set (Elapsed: 00:00:00.00)
查看某个表的当前状态
gbase> show table status where name='t1'\G;
*************************** 1. row ***************************
Name: t1
Engine: EXPRESS
Version: 10
Row_format: Compressed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-01-25 09:44:04
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: avg_row_length=5
Limit_storage_size: 0
storage_size: 285
table_data_size: 0
Comment:
local_hash_index_file_size: 0
global_hash_index_file_size: 0
tablespace_name: NULL
tablespace_path: NULL
1 row in set (Elapsed: 00:00:00.00)
show tables
查看表名字。 包括 show full tables
提示：如果表很多，比如几万以上，不建议不带条件，避免磁盘大量随机读取。
gbase> use db1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> show tables;
+-------------------+
| Tables_in_db1 |
+-------------------+
| db1_t1 |
| v_t1 |
| v_t1_178291728712 |
+-------------------+
3 rows in set (Elapsed: 00:00:00.00)
gbase> show full tables;
+-------------------+------------+
| Tables_in_db1 | Table_type |
+-------------------+------------+
| db1_t1 | BASE TABLE |
| v_t1 | VIEW |
| v_t1_178291728712 | VIEW |
+-------------------+------------+
3 rows in set (Elapsed: 00:00:00.00)
gbase> show full tables like '%t1%';
+----------------------+------------+
| Tables_in_db1 (%t1%) | Table_type |
+----------------------+------------+
| db1_t1 | BASE TABLE |
| v_t1 | VIEW |
| v_t1_178291728712 | VIEW |
+----------------------+------------+
3 rows in set (Elapsed: 00:00:00.00)
gbase> show full tables where tables_in_db1 like '%t1%';
+-------------------+------------+
| Tables_in_db1 | Table_type |
+-------------------+------------+
| db1_t1 | BASE TABLE |
| v_t1 | VIEW |
| v_t1_178291728712 | VIEW |
+-------------------+------------+
3 rows in set (Elapsed: 00:00:00.00)
gbase> show full tables where tables_in_db1 like '%t1%' and table_type='VIEW';
+-------------------+------------+
| Tables_in_db1 | Table_type |
+-------------------+------------+
| v_t1 | VIEW |
| v_t1_178291728712 | VIEW |
+-------------------+------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> show full tables where tables_in_db1 like '%t1%' and table_type='view';
+-------------------+------------+
| Tables_in_db1 | Table_type |
+-------------------+------------+
| v_t1 | VIEW |
| v_t1_178291728712 | VIEW |
+-------------------+------------+
2 rows in set (Elapsed: 00:00:00.00)
show tablespaces
查看表空间，从V95版本才支持。
gbase> use testdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> show tablespaces;
+----------------------+
| Tablespace_in_testdb |
+----------------------+
| sys_tablespace |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> create tablespace tablespace_ssd datadir '/opt/gbase/ssd';
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> show tablespaces;
+----------------------+
| Tablespace_in_testdb |
+----------------------+
| sys_tablespace |
| tablespace_ssd |
+----------------------+
2 rows in set (Elapsed: 00:00:00.00)
show variables
显示参数设置。参考 GBase8a 集群查看数据库参数的当前值
show vcs
显示VC列表。 V95支持vC的版本才有。
gbase> show vcs;
+---------+--------------+---------+
| id | name | default |
+---------+--------------+---------+
| vc00001 | vcname000001 | Y |
+---------+--------------+---------+
1 row in set (Elapsed: 00:00:00.00)
show warnings
显示告警信息
gbase> show warnings;
Empty set (Elapsed: 00:00:00.00)
gbase> show warnings limit 3,1;
Empty set (Elapsed: 00:00:00.00)
show views
显示视图。当前版本尚不支持这个写法，但可以通过如下方法获得
show full tables where table_type='VIEW'
gbase> create view testdb.v_tr as select * from tr;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> show full tables where table_type='VIEW';
+------------------+------------+
| Tables_in_testdb | Table_type |
+------------------+------------+
| v_tr | VIEW |
+------------------+------------+
1 row in set (Elapsed: 00:00:00.00)