SHOW
SHOW 关键字提供数据库和表信息。
SHOW DATABASES
展示所有数据库：
SHOW [FULL] DATABASES;
+---------+
| Schemas |
+---------+
| public |
+---------+
1 row in set (0.01 sec)
展示名称符合 LIKE 模式的数据库：
SHOW DATABASES LIKE 'p%';
根据 where 表达式展示数据库：
SHOW DATABASES WHERE Schemas='test_public_schema';
展示所有数据库，包括它们的选项：
create database with(ttl='7d');
SHOW FULL DATABASES;
+--------------------+-------------+
| Database | Options |
+--------------------+-------------+
| greptime_private | |
| information_schema | |
| public | |
| test | ttl='7days' |
+--------------------+-------------+
SHOW TABLES
展示所有表：
SHOW TABLES;
+---------+
| Tables |
+---------+
| numbers |
| scripts |
+---------+
2 rows in set (0.00 sec)
展示 test 数据库中的所有表：
SHOW TABLES FROM test;
展示名称符合 LIKE 模式的表：
SHOW TABLES like '%prometheus%';
根据 where 表达式展示表：
SHOW TABLES FROM test WHERE Tables='numbers';
SHOW FULL TABLES
SHOW FULL TABLES [IN | FROM] [DATABASE] [LIKE pattern] [WHERE query]
将会展示指定数据库（或者默认 public）中所有的表及其类型：
SHOW FULL TABLES;
+---------+------------+
| Tables | Table_type |
+---------+------------+
| monitor | BASE TABLE |
| numbers | TEMPORARY |
+---------+------------+
2 rows in set (0.00 sec)
Tables: 表的名称Table_type: 表的类型，例如BASE_TABLE,TEMPORARY和VIEW等等。
同样也支持 like 和 where 查询：
SHOW FULL TABLES FROM public like '%mo%';
+---------+------------+
| Tables | Table_type |
+---------+------------+
| monitor | BASE TABLE |
+---------+------------+
1 row in set (0.01 sec)
SHOW FULL TABLES WHERE Table_type='BASE TABLE';
+---------+------------+
| Tables | Table_type |
+---------+------------+
| monitor | BASE TABLE |
+---------+------------+
1 row in set (0.01 sec)
SHOW CREATE TABLE
展示创建指定表的 CREATE TABLE 语句：
SHOW CREATE TABLE [table]
例如：
SHOW CREATE TABLE system_metrics;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| system_metrics | CREATE TABLE IF NOT EXISTS `system_metrics` (
`host` STRING NULL,
`idc` STRING NULL,
`cpu_util` DOUBLE NULL,
`memory_util` DOUBLE NULL,
`disk_util` DOUBLE NULL,
`ts` TIMESTAMP(3) NOT NULL DEFAULT current_timestamp(),
TIME INDEX (`ts`),
PRIMARY KEY (`host`, `idc`)
)
ENGINE=mito
WITH(
regions = 1
) |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Table: 表的名称Create Table: 用于创建该表的 SQL
SHOW CREATE FLOW
展示创建指定 Flow 任务的 CREATE FLOW 语句。
比如：
public=> SHOW CREATE FLOW filter_numbers;
Flow | Create Flow
----------------+-------------------------------------------------------
filter_numbers | CREATE OR REPLACE FLOW IF NOT EXISTS filter_numbers +
| SINK TO out_num_cnt +
| AS SELECT number FROM numbers_input WHERE number > 10
(1 row)
SHOW FLOWS
展示当前所有 Flow 任务：
public=> SHOW FLOWS;
Flows
----------------
filter_numbers
(1 row)
同样也支持 LIKE 表达式：
public=> show flows like "filter%";
Flows
----------------
filter_numbers
(1 row)
SHOW CREATE VIEW
用于显示视图（View）的定义：
SHOW CREATE VIEW cpu_monitor;
+-------------+--------------------------------------------------------------+
| View | Create View |
+-------------+--------------------------------------------------------------+
| cpu_monitor | CREATE VIEW cpu_monitor AS SELECT cpu, host, ts FROM monitor |
+-------------+--------------------------------------------------------------+
SHOW VIEWS
列出所有视图：
SHOW VIEWS;
+----------------+
| Views |
+----------------+
| cpu_monitor |
| memory_monitor |
+----------------+
当然，它也支持 LIKE 查询：
SHOW VIEWS LIKE 'cpu%';
+-------------+
| Views |
+-------------+
| cpu_monitor |
+-------------+
以及 WHERE 条件：
SHOW VIEWS WHERE Views = 'memory_monitor';
+----------------+
| Views |
+----------------+
| memory_monitor |
+----------------+
SHOW 语句的扩展
与 MySQL 类似，一些 SHOW 语句的扩展伴随着 INFORMATION_SCHEMA 的实现，它们还接受 WHERE 子句，提供了在指定显示的行时更大的灵活性。
GreptimeDB 为 MySQL 兼容性实现了这些扩展的一部分，这对于像 Navicat for MySQL 或 dbeaver 这样的工具连接 GreptimeDB 非常有用。
SHOW CHARACTER SET;
输出类似于 INFORMATION_SCHEMA.CHARACTER_SETS 表：
+---------+---------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8 | UTF-8 Unicode | utf8_bin | 4 |
+---------+---------------+-------------------+--------+
使用 SHOW COLLATION 来查看 INFORMATION_SCHEMA.COLLATIONS 表。
SHOW INDEX FROM monitor;
列出表中的所有索引：
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+----------------------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+----------------------------+---------+---------------+---------+------------+
| monitor | 1 | PRIMARY | 1 | host | A | NULL | NULL | NULL | YES | greptime-inverted-index-v1 | | | YES | NULL |
| monitor | 1 | TIME INDEX | 1 | ts | A | NULL | NULL | NULL | NO | greptime-inverted-index-v1 | | | YES | NULL |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+----------------------------+---------+---------------+---------+------------+
这是 INFORMATION_SCHEMA.TABLE_CONSTRAINTS 的扩展。
列出表中的所有列：
SHOW COLUMNS FROM monitor;
输出类似于 INFORMATION_SCHEMA.COLUMNS：
+--------+--------------+------+------------+---------------------+-------+----------------------+
| Field | Type | Null | Key | Default | Extra | Greptime_type |
+--------+--------------+------+------------+---------------------+-------+----------------------+
| cpu | double | Yes | | 0 | | Float64 |
| host | string | Yes | PRI | NULL | | String |
| memory | double | Yes | | NULL | | Float64 |
| ts | timestamp(3) | No | TIME INDEX | current_timestamp() | | TimestampMillisecond |
+--------+--------------+------+------------+---------------------+-------+----------------------+
所有这些 SHOW 扩展 都接受 WHERE 子句：
SHOW COLUMNS FROM monitor WHERE Field = 'cpu';
+-------+--------+------+------+---------+-------+---------------+
| Field | Type | Null | Key | Default | Extra | Greptime_type |
+-------+--------+------+------+---------+-------+---------------+
| cpu | double | Yes | | 0 | | Float64 |
+-------+--------+------+------+---------+-------+---------------+
其他 SHOW 扩展语句：
SHOW STATUS和SHOW VARIABLES不支持，仅返回空结果。SHOW TABLE STATUS是INFORMATION_SCHEMA.TABLES的扩展。