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