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 的扩展。