跳到主要内容

INFORMATION_SCHEMA

INFORMATION_SCHEMA(或:information_schema)是一个系统数据库,它提供了一个(某种程度上)标准化、与DBMS无关的视图,用于查看数据库对象的元数据。INFORMATION_SCHEMA 中的视图通常不如正常的系统表好,但工具可以使用它们以跨DBMS的方式获取基本信息。INFORMATION_SCHEMA 中视图的结构和内容应该以向后兼容的方式发展,即只添加新功能,而不更改或删除现有功能。在内部实现方面,INFORMATION_SCHEMA 中的视图通常映射到正常的系统表,如system.columnssystem.databasessystem.tables

SHOW TABLES FROM INFORMATION_SCHEMA;

-- or:
SHOW TABLES FROM information_schema;
┌─name────────────────────┐
│ COLUMNS │
│ KEY_COLUMN_USAGE │
│ REFERENTIAL_CONSTRAINTS │
│ SCHEMATA │
| STATISTICS |
│ TABLES │
│ VIEWS │
│ columns │
│ key_column_usage │
│ referential_constraints │
│ schemata │
| statistics |
│ tables │
│ views │
└─────────────────────────┘

INFORMATION_SCHEMA 包含以下视图

出于与其他数据库兼容性的原因,提供了不区分大小写的等效视图,例如 INFORMATION_SCHEMA.columns。这同样适用于这些视图中的所有列 - 提供了小写(例如,table_name)和大写(TABLE_NAME)变体。

COLUMNS

包含从system.columns 系统表读取的列以及 ClickHouse 不支持或没有意义(始终为 NULL)但必须符合标准的列。

  • table_catalog (String) — 表所在的数据库的名称。
  • table_schema (String) — 表所在的数据库的名称。
  • table_name (String) — 表名。
  • column_name (String) — 列名。
  • ordinal_position (UInt64) — 表中列的序号,从 1 开始。
  • column_default (String) — 默认值的表达式,如果未定义则为空字符串。
  • is_nullable (UInt8) — 指示列类型是否为 Nullable 的标志。
  • data_type (String) — 列类型。
  • character_maximum_length (Nullable(UInt64)) — 二进制数据、字符数据或文本数据和图像的最大长度(以字节为单位)。在 ClickHouse 中,仅对 FixedString 数据类型有意义。否则,返回 NULL 值。
  • character_octet_length (Nullable(UInt64)) — 二进制数据、字符数据或文本数据和图像的最大长度(以字节为单位)。在 ClickHouse 中,仅对 FixedString 数据类型有意义。否则,返回 NULL 值。
  • numeric_precision (Nullable(UInt64)) — 近似数值数据、精确数值数据、整数数据或货币数据的精度。在 ClickHouse 中,它是整数类型的位宽和 Decimal 类型的十进制精度。否则,返回 NULL 值。
  • numeric_precision_radix (Nullable(UInt64)) — 数制基数是近似数值数据、精确数值数据、整数数据或货币数据的精度。在 ClickHouse 中,整数类型为 2,Decimal 类型为 10。否则,返回 NULL 值。
  • numeric_scale (Nullable(UInt64)) — 近似数值数据、精确数值数据、整数数据或货币数据的规模。在 ClickHouse 中,仅对 Decimal 类型有意义。否则,返回 NULL 值。
  • datetime_precision (Nullable(UInt64)) — DateTime64 数据类型的十进制精度。对于其他数据类型,返回 NULL 值。
  • character_set_catalog (Nullable(String)) — NULL,不支持。
  • character_set_schema (Nullable(String)) — NULL,不支持。
  • character_set_name (Nullable(String)) — NULL,不支持。
  • collation_catalog (Nullable(String)) — NULL,不支持。
  • collation_schema (Nullable(String)) — NULL,不支持。
  • collation_name (Nullable(String)) — NULL,不支持。
  • domain_catalog (Nullable(String)) — NULL,不支持。
  • domain_schema (Nullable(String)) — NULL,不支持。
  • domain_name (Nullable(String)) — NULL,不支持。
  • extra (Nullable(String)) — 对于 MATERIALIZED 类型列为 STORED GENERATED,对于 ALIAS 类型列为 VIRTUAL GENERATED,对于 DEFAULT 类型列为 DEFAULT_GENERATED,或 NULL

示例

查询

SELECT table_catalog,
table_schema,
table_name,
column_name,
ordinal_position,
column_default,
is_nullable,
data_type,
character_maximum_length,
character_octet_length,
numeric_precision,
numeric_precision_radix,
numeric_scale,
datetime_precision,
character_set_catalog,
character_set_schema,
character_set_name,
collation_catalog,
collation_schema,
collation_name,
domain_catalog,
domain_schema,
domain_name,
column_comment,
column_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (table_schema = currentDatabase() OR table_schema = '')
AND table_name NOT LIKE '%inner%'
LIMIT 1
FORMAT Vertical;

结果

Row 1:
──────
table_catalog: default
table_schema: default
table_name: describe_example
column_name: id
ordinal_position: 1
column_default:
is_nullable: 0
data_type: UInt64
character_maximum_length: ᴺᵁᴸᴸ
character_octet_length: ᴺᵁᴸᴸ
numeric_precision: 64
numeric_precision_radix: 2
numeric_scale: 0
datetime_precision: ᴺᵁᴸᴸ
character_set_catalog: ᴺᵁᴸᴸ
character_set_schema: ᴺᵁᴸᴸ
character_set_name: ᴺᵁᴸᴸ
collation_catalog: ᴺᵁᴸᴸ
collation_schema: ᴺᵁᴸᴸ
collation_name: ᴺᵁᴸᴸ
domain_catalog: ᴺᵁᴸᴸ
domain_schema: ᴺᵁᴸᴸ
domain_name: ᴺᵁᴸᴸ

SCHEMATA

包含从system.databases系统表读取的列,以及ClickHouse不支持或没有意义(始终为NULL)但标准要求必须包含的列。

  • catalog_name (String) — 数据库名称。
  • schema_name (String) — 数据库名称。
  • schema_owner (String) — 模式所有者名称,始终为'default'
  • default_character_set_catalog (Nullable(String)) — NULL,不支持。
  • default_character_set_schema (Nullable(String)) — NULL,不支持。
  • default_character_set_name (Nullable(String)) — NULL,不支持。
  • sql_path (Nullable(String)) — NULL,不支持。

示例

查询

SELECT catalog_name,
schema_name,
schema_owner,
default_character_set_catalog,
default_character_set_schema,
default_character_set_name,
sql_path
FROM information_schema.schemata
WHERE schema_name ilike 'information_schema'
LIMIT 1
FORMAT Vertical;

结果

Row 1:
──────
catalog_name: INFORMATION_SCHEMA
schema_name: INFORMATION_SCHEMA
schema_owner: default
default_character_set_catalog: ᴺᵁᴸᴸ
default_character_set_schema: ᴺᵁᴸᴸ
default_character_set_name: ᴺᵁᴸᴸ
sql_path: ᴺᵁᴸᴸ

TABLES

包含从system.tables系统表读取的列。

  • table_catalog (String) — 表所在的数据库的名称。
  • table_schema (String) — 表所在的数据库的名称。
  • table_name (String) — 表名。
  • table_type (String) — 表类型。可能的值
    • BASE TABLE
    • VIEW
    • FOREIGN TABLE
    • LOCAL TEMPORARY
    • SYSTEM VIEW
  • table_rows (Nullable(UInt64)) — 行总数。如果无法确定,则为 NULL。
  • data_length (Nullable(UInt64)) — 磁盘上的数据大小。如果无法确定,则为 NULL。
  • table_collation (Nullable(String)) — 表的默认排序规则。始终为 utf8mb4_0900_ai_ci
  • table_comment (Nullable(String)) — 创建表时使用的注释。

示例

查询

SELECT table_catalog, 
table_schema,
table_name,
table_type,
table_collation,
table_comment
FROM INFORMATION_SCHEMA.TABLES
WHERE (table_schema = currentDatabase() OR table_schema = '')
AND table_name NOT LIKE '%inner%'
LIMIT 1
FORMAT Vertical;

结果

Row 1:
──────
table_catalog: default
table_schema: default
table_name: describe_example
table_type: BASE TABLE
table_collation: utf8mb4_0900_ai_ci
table_comment:

VIEWS

当使用表引擎View时,包含从system.tables系统表读取的列。

  • table_catalog (String) — 表所在的数据库的名称。
  • table_schema (String) — 表所在的数据库的名称。
  • table_name (String) — 表名。
  • view_definition (String) — 视图的SELECT查询。
  • check_option (String) — NONE,不进行检查。
  • is_updatable (Enum8) — NO,视图不可更新。
  • is_insertable_into (Enum8) — 显示创建的视图是否为物化视图。可能的值
    • NO — 创建的视图不是物化视图。
    • YES — 创建的视图是物化视图。
  • is_trigger_updatable (Enum8) — NO,触发器不会更新。
  • is_trigger_deletable (Enum8) — NO,触发器不会删除。
  • is_trigger_insertable_into (Enum8) — NO,不会将数据插入触发器。

示例

查询

CREATE VIEW v (n Nullable(Int32), f Float64) AS SELECT n, f FROM t;
CREATE MATERIALIZED VIEW mv ENGINE = Null AS SELECT * FROM system.one;
SELECT table_catalog,
table_schema,
table_name,
view_definition,
check_option,
is_updatable,
is_insertable_into,
is_trigger_updatable,
is_trigger_deletable,
is_trigger_insertable_into
FROM information_schema.views
WHERE table_schema = currentDatabase()
LIMIT 1
FORMAT Vertical;

结果

Row 1:
──────
table_catalog: default
table_schema: default
table_name: mv
view_definition: SELECT * FROM system.one
check_option: NONE
is_updatable: NO
is_insertable_into: YES
is_trigger_updatable: NO
is_trigger_deletable: NO
is_trigger_insertable_into: NO

KEY_COLUMN_USAGE

包含来自system.tables系统表的列,这些列受约束限制。

  • constraint_catalog (String) — 当前未使用。始终为 def
  • constraint_schema (String) — 约束所属的模式(数据库)的名称。
  • constraint_name (Nullable(String)) — 约束的名称。
  • table_catalog (String) — 当前未使用。始终为 def
  • table_schema (String) — 表所属的模式(数据库)的名称。
  • table_name (String) — 包含约束的表的名称。
  • column_name (Nullable(String)) — 包含约束的列的名称。
  • ordinal_position (UInt32) — 当前未使用。始终为 1
  • position_in_unique_constraint (Nullable(UInt32)) — 当前未使用。始终为 NULL
  • referenced_table_schema (Nullable(String)) — 当前未使用。始终为 NULL。
  • referenced_table_name (Nullable(String)) — 当前未使用。始终为 NULL。
  • referenced_column_name (Nullable(String)) — 当前未使用。始终为 NULL。

示例

CREATE TABLE test (i UInt32, s String) ENGINE MergeTree ORDER BY i;
SELECT constraint_catalog,
constraint_schema,
constraint_name,
table_catalog,
table_schema,
table_name,
column_name,
ordinal_position,
position_in_unique_constraint,
referenced_table_schema,
referenced_table_name,
referenced_column_name
FROM information_schema.key_column_usage
WHERE table_name = 'test'
FORMAT Vertical;

结果

Row 1:
──────
constraint_catalog: def
constraint_schema: default
constraint_name: PRIMARY
table_catalog: def
table_schema: default
table_name: test
column_name: i
ordinal_position: 1
position_in_unique_constraint: ᴺᵁᴸᴸ
referenced_table_schema: ᴺᵁᴸᴸ
referenced_table_name: ᴺᵁᴸᴸ
referenced_column_name: ᴺᵁᴸᴸ

REFERENTIAL_CONSTRAINTS

包含有关外键的信息。目前返回空结果(无行),这足以与 Tableau Online 等第三方工具兼容。

  • constraint_catalog (String) — 当前未使用。
  • constraint_schema (String) — 当前未使用。
  • constraint_name (Nullable(String)) — 当前未使用。
  • unique_constraint_catalog (String) — 当前未使用。
  • unique_constraint_schema (String) — 当前未使用。
  • unique_constraint_name (Nullable(String)) — 当前未使用。
  • match_option (String) — 当前未使用。
  • update_rule (String) — 当前未使用。
  • delete_rule (String) — 当前未使用。
  • table_name (String) — 当前未使用。
  • referenced_table_name (String) — 当前未使用。

STATISTICS

提供有关表索引的信息。目前返回空结果(无行),这足以与 Tableau Online 等第三方工具兼容。

  • table_catalog (String) — 当前未使用。
  • table_schema (String) — 当前未使用。
  • table_name (String) — 当前未使用。
  • non_unique (Int32) — 当前未使用。
  • index_schema (String) — 当前未使用。
  • index_name (Nullable(String)) — 当前未使用。
  • seq_in_index (UInt32) — 当前未使用。
  • column_name (Nullable(String)) — 当前未使用。
  • collation (Nullable(String)) — 当前未使用。
  • cardinality (Nullable(Int64)) — 当前未使用。
  • sub_part (Nullable(Int64)) — 当前未使用。
  • packed (Nullable(String)) — 当前未使用。
  • nullable (String) — 当前未使用。
  • index_type (String) — 当前未使用。
  • comment (String) — 当前未使用。
  • index_comment (String) — 当前未使用。
  • is_visible (String) — 当前未使用。
  • expression (Nullable(String)) — 当前未使用。