CREATE TABLE

创建一个新表。默认情况下，表只会在当前服务器上创建。分布式 DDL 查询通过 ON CLUSTER 子句来实现，相关内容另文说明。

语法格式

该查询可根据不同的用例采用多种语法格式。

使用显式 schema 创建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [COMMENT 'comment for column'] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [COMMENT 'comment for column'] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine
  [COMMENT 'comment for table']

在 db 数据库中创建一个名为 table_name 的表；如果未设置 db，则在当前数据库中创建。该表使用括号中指定的结构和 engine 引擎。表结构由列描述、二级索引、投影和约束组成。如果该引擎支持主键，则会将其标明为表引擎的参数。最简单情况下，列描述的形式为 name type。示例：RegionID UInt32。类型后面的修饰符——COMMENT、compression_codec、STATISTICS、TTL、COLLATE、PRIMARY KEY 和按列 SETTINGS——可以按任意顺序编写，并且每个最多只能出现一次。例如，RegionID UInt32 CODEC(ZSTD) COMMENT 'comment for column' 和 RegionID UInt32 COMMENT 'comment for column' CODEC(ZSTD) 是相同的。请注意，SHOW CREATE TABLE 会规范化列声明：其中保留的修饰符始终按规范顺序 COMMENT、CODEC、STATISTICS、TTL、COLLATE、SETTINGS 输出，而按列 PRIMARY KEY 会从列声明中移至表级 PRIMARY KEY 子句。也可以为默认值定义表达式 (见下文) 。如有需要，可以指定主键，其中包含一个或多个键表达式。可以为列和表添加注释。

使用现有表的 schema 创建表

CREATE TABLE [IF NOT EXISTS] [db2.]table_clone AS [db.]table [ENGINE = engine]

ClickHouse 支持复制现有表的 schema 和数据。要复制现有表的 schema：这会创建一个与另一个表结构相同的表。

使用现有表的 schema 和数据创建表

若要复制现有表的 schema 和数据：

CREATE TABLE [IF NOT EXISTS] [db2.]table_clone CLONE AS [db.]table [ENGINE = engine]

这会创建一个与现有表具有相同 schema 和数据的表。新表创建后，db.table 中的所有分区都会附加到该表。换句话说，在创建时，db.table 的数据会被克隆到 db2.table_clone。该查询等同于以下内容：

CREATE TABLE [IF NOT EXISTS] [db2.]table_clone AS [db.]table [ENGINE = engine];
ALTER TABLE [db2.]table_clone ATTACH PARTITION ALL FROM [db.]table;

对于这两项功能，你都可以为该表指定不同的引擎。如果未指定引擎，则默认使用与原始表 (db.table) 相同的引擎。

使用表函数创建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS table_function()

创建一个表，其结果与指定的表函数相同。创建后的表也会像所指定的对应表函数一样工作。

使用 SELECT 查询创建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name[(name1 [type1], name2 [type2], ...)] ENGINE = engine AS SELECT ...

使用 engine 引擎创建一个表，其结构与 SELECT 查询结果类似，并使用 SELECT 的数据填充该表。你也可以显式指定列描述。如果表已存在且指定了 IF NOT EXISTS，则该查询不会执行任何操作。查询中在 ENGINE 子句之后还可以有其他子句。有关如何创建表的详细文档，请参阅表引擎说明。示例

Query

CREATE TABLE t1 (x String) ENGINE = Memory AS SELECT 1;
SELECT x, toTypeName(x) FROM t1;

Response

┌─x─┬─toTypeName(x)─┐
│ 1 │ String        │
└───┴───────────────┘

指定列默认值

列描述可以通过 DEFAULT expr、MATERIALIZED expr 或 ALIAS expr 的形式指定默认值表达式。例如：URLDomain String DEFAULT domain(URL)。表达式 expr 是可选的。如果省略，则必须显式指定列类型，此时默认值分别为：数值列为 0，字符串列为 '' (空字符串) ，数组列为 [] (空数组) ，日期列为 1970-01-01，Nullable 列为 NULL。默认值列的列类型可以省略，这种情况下会根据 expr 的类型自动推断。例如，列 EventDate DEFAULT toDate(EventTime) 的类型将为日期类型。如果同时指定了数据类型和默认值表达式，系统会插入一个隐式类型转换函数，将表达式转换为指定类型。例如：Hits UInt32 DEFAULT 0 在内部会表示为 Hits UInt32 DEFAULT toUInt32(0)。默认值表达式 expr 可以引用任意表列和常量。ClickHouse 会检查对表结构的修改不会在表达式计算中引入循环。对于 INSERT，它还会检查这些表达式是否可解析——也就是说，用于计算它们的所有列都必须已传入。

DEFAULT

DEFAULT expr 普通默认值。如果在 INSERT 查询中未指定此类列的值，则会根据 expr 计算。示例：

CREATE OR REPLACE TABLE test
(
    id UInt64,
    updated_at DateTime DEFAULT now(),
    updated_at_date Date DEFAULT toDate(updated_at)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test (id) VALUES (1);

SELECT * FROM test;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│  1 │ 2023-02-24 17:06:46 │      2023-02-24 │
└────┴─────────────────────┴─────────────────┘

MATERIALIZED

MATERIALIZED expr 物化表达式。插入行时，这类列的值会根据指定的物化表达式自动计算，不能在 INSERT 时显式指定。此外，此类默认值列不会包含在 SELECT * 的结果中。这是为了保持这样一个不变性：SELECT * 的结果始终都可以通过 INSERT 再次插入到表中。可通过设置 asterisk_include_materialized_columns 禁用此行为。示例：

CREATE OR REPLACE TABLE test
(
    id UInt64,
    updated_at DateTime MATERIALIZED now(),
    updated_at_date Date MATERIALIZED toDate(updated_at)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test VALUES (1);

SELECT * FROM test;
┌─id─┐
│  1 │
└────┘

SELECT id, updated_at, updated_at_date FROM test;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│  1 │ 2023-02-24 17:08:08 │      2023-02-24 │
└────┴─────────────────────┴─────────────────┘

SELECT * FROM test SETTINGS asterisk_include_materialized_columns=1;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│  1 │ 2023-02-24 17:08:08 │      2023-02-24 │
└────┴─────────────────────┴─────────────────┘

EPHEMERAL

EPHEMERAL [expr] 临时列。此类型的列不会存储在表中，也无法对其执行 SELECT。临时列的唯一用途，是基于它们构建其他列的默认值表达式。执行未显式指定列的插入时，会跳过此类型的列。这样做是为了保持这样一个不变性：SELECT * 的结果始终都可以通过 INSERT 再次插回表中。示例：

CREATE OR REPLACE TABLE test
(
    id UInt64,
    unhexed String EPHEMERAL,
    hexed FixedString(4) DEFAULT unhex(unhexed)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test (id, unhexed) VALUES (1, '5a90b714');

SELECT
    id,
    hexed,
    hex(hexed)
FROM test
FORMAT Vertical;

Row 1:
──────
id:         1
hexed:      Z��
hex(hexed): 5A90B714

ALIAS

ALIAS expr 计算列 (同义概念) 。这种类型的列不会存储在表中，也无法向其中 INSERT 值。当 SELECT 查询显式引用这种类型的列时，其值会在查询时根据 expr 计算。默认情况下，SELECT * 会排除 ALIAS 列。可通过设置 asterisk_include_alias_columns 禁用此行为。使用 ALTER 查询添加新列时，不会为这些列补写旧数据。相反，在读取不包含这些新列值的旧数据时，默认会动态计算表达式。不过，如果计算这些表达式需要查询中未指出的其他列，则还会额外读取这些列，但仅限于需要它们的数据块。如果向表中添加了一个新列，但之后又更改了它的默认表达式，那么旧数据使用的值也会发生变化 (即那些值未存储在磁盘上的数据) 。请注意，在执行后台合并时，如果参与合并的某个 parts 中缺少某列的数据，则会将该列的数据写入合并后的 part。无法为嵌套数据结构中的元素设置默认值。

CREATE OR REPLACE TABLE test
(
    id UInt64,
    size_bytes Int64,
    size String ALIAS formatReadableSize(size_bytes)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO test VALUES (1, 4678899);

SELECT id, size_bytes, size FROM test;
┌─id─┬─size_bytes─┬─size─────┐
│  1 │    4678899 │ 4.46 MiB │
└────┴────────────┴──────────┘

SELECT * FROM test SETTINGS asterisk_include_alias_columns=1;
┌─id─┬─size_bytes─┬─size─────┐
│  1 │    4678899 │ 4.46 MiB │
└────┴────────────┴──────────┘

使用 NULL 或 NOT NULL 修饰符

在列定义中，数据类型后面的 NULL 和 NOT NULL 修饰符用于控制该列是否可以为 Nullable。如果该类型不是 Nullable，并且指定了 NULL，则会将其视为 Nullable；如果指定了 NOT NULL，则不会。例如，INT NULL 等同于 Nullable(INT)。如果该类型本身就是 Nullable，再指定 NULL 或 NOT NULL 修饰符时，则会抛出异常。另请参见 data_type_default_nullable 设置。

主键

你可以在创建表时定义主键。主键可以通过两种方式指定：

在列列表中

CREATE TABLE [db.]table_name
(
    name1 type1, name2 type2, ...,
    PRIMARY KEY(expr1[, expr2,...])
)
ENGINE = engine;

不在列列表中

CREATE TABLE [db.]table_name
(
    name1 type1, name2 type2, ...
)
ENGINE = engine
PRIMARY KEY(expr1[, expr2,...]);

不能在一次查询中同时使用这两种方式。

指定表约束

除列描述外，还可以定义约束：

CONSTRAINT

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
    ...
    CONSTRAINT constraint_name_1 CHECK boolean_expr_1,
    ...
) ENGINE = engine

boolean_expr_1 可以是任意布尔表达式。如果为该表定义了约束，那么在执行 INSERT 查询时，每一行都会检查所有约束。如果有任何约束不满足，server 将抛出异常，并给出约束名称和检查表达式。添加大量约束可能会对大型 INSERT 查询的性能产生负面影响。可以通过 system.constraints 表查看所有表中现有的约束。

ASSUME

ASSUME 子句用于在表上定义一个被假定为真的 CONSTRAINT。优化器随后可以利用该约束来提升 SQL 查询性能。以下示例展示了在创建 users_a 表时如何使用 ASSUME CONSTRAINT：

CREATE TABLE users_a (
    uid Int16, 
    name String, 
    age Int16, 
    name_len UInt8 MATERIALIZED length(name), 
    CONSTRAINT c1 ASSUME length(name) = name_len
) 
ENGINE=MergeTree 
ORDER BY (name_len, name);

这里，ASSUME CONSTRAINT 用于断言 length(name) 函数的结果始终等于 name_len 列的值。这意味着，每当在查询中调用 length(name) 时，ClickHouse 都可以将其替换为 name_len，这样通常会更快，因为不必调用 length() 函数。随后，在执行查询 SELECT name FROM users_a WHERE length(name) < 5; 时，ClickHouse 可以根据 ASSUME CONSTRAINT 将其优化为 SELECT name FROM users_a WHERE name_len < 5;。这样可以让查询运行得更快，因为无需为每一行计算 name 的长度。 ASSUME CONSTRAINT 不会强制执行该约束，它只是告知优化器该约束成立。如果该约束实际上并不成立，查询结果可能会不正确。因此，只有在你确定该约束确实成立时，才应使用 ASSUME CONSTRAINT。

使用 TTL 定义存储时长

定义值的存储时长。只能为 MergeTree 家族表指定。有关详细说明，请参阅列和表的 TTL。

选择列压缩编解码器

默认情况下，自管理版本的 ClickHouse 使用 lz4 压缩，ClickHouse Cloud 则使用 zstd。您还可以在 CREATE TABLE 查询中为每一列指定压缩方法：

CREATE TABLE codec_example
(
    dt Date CODEC(ZSTD),
    ts DateTime CODEC(LZ4HC),
    float_value Float32 CODEC(NONE),
    double_value Float64 CODEC(LZ4HC(9)),
    value Float32 CODEC(Delta, ZSTD)
)
ENGINE = <Engine>
...

有关可用的通用、专用和加密编解码器，请参阅列压缩编解码器。

创建临时表

ClickHouse 支持临时表，会话结束后这些表将自动消失。详情请参阅 CREATE TEMPORARY TABLE。

使用 REPLACE TABLE 以原子方式更新表

REPLACE 语句允许你以原子方式更新表。有关详细信息，请参阅 REPLACE TABLE。

添加表注释

您可以在创建表时添加注释。语法

CREATE TABLE [db.]table_name
(
    name1 type1, name2 type2, ...
)
ENGINE = engine
COMMENT 'Comment'

COMMENT 子句必须在 PARTITION BY、ORDER BY 以及存储专用 SETTINGS 等所有存储相关子句之后指定。在 COMMENT 子句之后，只会解析查询专用 SETTINGS (如 max_threads 等) ，不会解析存储相关设置。这意味着，正确的子句顺序是：

ENGINE
存储子句
COMMENT
查询设置 (如有)

示例

Query

CREATE TABLE t1 (x String) ENGINE = Memory COMMENT 'The temporary table';
SELECT name, comment FROM system.tables WHERE name = 't1';

Response

┌─name─┬─comment─────────────┐
│ t1   │ The temporary table │
└──────┴─────────────────────┘

博客：使用 schema 和编解码器优化 ClickHouse
博客：在 ClickHouse 中处理时间序列数据

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

语法格式

使用显式 schema 创建表

使用现有表的 schema 创建表

使用现有表的 schema 和数据创建表

使用表函数创建表

使用 SELECT 查询创建表

指定列默认值

DEFAULT

MATERIALIZED

EPHEMERAL

ALIAS

使用 NULL 或 NOT NULL 修饰符

主键

指定表约束

CONSTRAINT

ASSUME

使用 TTL 定义存储时长

选择列压缩编解码器

创建临时表

使用 REPLACE TABLE 以原子方式更新表

添加表注释

​语法格式

​使用显式 schema 创建表

​使用现有表的 schema 创建表

​使用现有表的 schema 和数据创建表

​使用表函数创建表

​使用 SELECT 查询创建表

​指定列默认值

​DEFAULT

​MATERIALIZED

​EPHEMERAL

​ALIAS

​使用 NULL 或 NOT NULL 修饰符

​主键

​指定表约束

​CONSTRAINT

​ASSUME

​使用 TTL 定义存储时长

​选择列压缩编解码器

​创建临时表

​使用 REPLACE TABLE 以原子方式更新表

​添加表注释

​相关内容

语法格式

使用显式 schema 创建表

使用现有表的 schema 创建表

使用现有表的 schema 和数据创建表

使用表函数创建表

使用 SELECT 查询创建表

指定列默认值

DEFAULT

MATERIALIZED

EPHEMERAL

ALIAS

使用 NULL 或 NOT NULL 修饰符

主键

指定表约束

CONSTRAINT

ASSUME

使用 TTL 定义存储时长

选择列压缩编解码器

创建临时表

使用 REPLACE TABLE 以原子方式更新表

添加表注释

相关内容