点击: information_schema.KEY_COLUMN_USAGE

Question

在MySQL中，我们有information_schema.KEY_COLUMN_USAGE。我们可以在click house找到相同的信息？select * from information_schema.KEY_COLUMN_USAGE in MySQL，通过执行此命令，我们将获得结果。我想知道在clickhouse中给出相同结果的查询是什么。

Answer 1

CH提供了几种获取表的元数据的方法。

让我们创建test-table：

CREATE TABLE test_001
(
    `id` Int32 CODEC(Delta, LZ4), 
    CONSTRAINT id_should_be_positive CHECK id > 0
)
ENGINE = MergeTree()
PARTITION BY tuple()
ORDER BY id

看看下面这些方法：

SELECT *
FROM system.tables
WHERE name = 'test_001'
FORMAT Vertical
/*
Row 1:
──────
database:                   default
name:                       test_001
uuid:                       00000000-0000-0000-0000-000000000000
engine:                     MergeTree
is_temporary:               0
data_paths:                 ['/var/lib/clickhouse/data/default/test_001/']
metadata_path:              /var/lib/clickhouse/metadata/default/test_001.sql
metadata_modification_time: 2020-07-21 12:42:07
dependencies_database:      []
dependencies_table:         []
create_table_query:         CREATE TABLE default.test_001 (`id` Int32 CODEC(Delta(4), LZ4),  CONSTRAINT id_should_be_positive CHECK id > 0) ENGINE = MergeTree() PARTITION BY tuple() ORDER BY id SETTINGS index_granularity = 8192
engine_full:                MergeTree() PARTITION BY tuple() ORDER BY id SETTINGS index_granularity = 8192
partition_key:              tuple()
sorting_key:                id
primary_key:                id
sampling_key:               
storage_policy:             default
total_rows:                 0
total_bytes:                0
*/

SELECT *
FROM system.columns
WHERE table = 'test_001'
FORMAT Vertical
/*
Row 1:
──────
database:                default
table:                   test_001
name:                    id
type:                    Int32
default_kind:            
default_expression:      
data_compressed_bytes:   0
data_uncompressed_bytes: 0
marks_bytes:             0
comment:                 
is_in_partition_key:     0
is_in_sorting_key:       1
is_in_primary_key:       1
is_in_sampling_key:      0
compression_codec:       CODEC(Delta(4), LZ4)
*/

DESCRIBE TABLE test_001
/*
┌─name─┬─type──┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ Int32 │              │                    │         │ Delta(4), LZ4    │                │
└──────┴───────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
*/

SHOW CREATE TABLE test_001
/*
┌─statement──────────────────────────────────────────┐
│ CREATE TABLE default.test_001
(
    `id` Int32 CODEC(Delta(4), LZ4), 
    CONSTRAINT id_should_be_positive CHECK id > 0
)
ENGINE = MergeTree()
PARTITION BY tuple()
ORDER BY id
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────┘
*/