基础类型

整数

声明 大小(字节) 范围
Int8 1 -128 到 127
UInt8 1 0 到 255
Int16 2 -32768 到 32767
UInt16 2 0 到 65535
Int32 4 -2147483648 到 2147483647
UInt32 4 0 到 4294967295
Int64 8 -9223372036854775808 到 9223372036854775807
UInt64 8 0 到 18446744073709551615

浮点数

声明 大小(字节) 有效精度(位数)
Float32 4 7
Float64 8 16
  • 正无穷: SELECT 0.8/0
  • 负无穷: SELECT -0.8/0
  • 非数字: SELECT 0/0

定点数

  • 原生声明: Decimal(P,S)

    • P: 总位数(整数+小数)，取值范围 1~38
    • S: 小数位数，取值范围 0~P

  • 其他声明

    • Decimal32(S): -10^(9-S) 到 10^(9-S)
    • Decimal64(S): -10^(18-S) 到 10^(18-S)
    • Decimal128(S): -10^(18-S) 到 10^(38-S)

字符串

声明 备注
String 长度不固定，不限字符集，建议 UTF-8
FixedString(N) 长度固定，null 字节填充
UUID 格式是 8-4-4-4-12，0 填充

时间

声明 精度 示例
Datetime 秒 2020-09-18 19:59:00
Datetime64(N) 亚秒 2020-09-18 19:59:00.00
Date 日 2020-09-18

复合类型

数组

  • 声明: [T], Array(T)
  • 查询时会以最小储存代价为原则推断类型
  • 元素类型可以不同，但必须兼容

元组

  • 声明: (T), tuple(T)
  • 查询时会以最小储存代价为原则推断类型
  • 元素类型可以不同，且无须兼容

枚举

声明 Key 类型 Value 类型
Enum8(‘k1’=1,‘k2’=2, … ,‘kN’=N) String Int8
Enum16(‘k1’=1,‘k2’=2, … ,‘kN’=N) String Int16
  • 枚举的所有后续操作，都会使用 Int 类型的 Value 值

嵌套

  • 声明 
    1
2
3
4

    Nested(
    column1 T,
    column2 T
)
  • 嵌套字段中的每一列期望写入的是数组: Array(T)
  • 同一行数据内，嵌套字段中的每一列的数组长度必须相等
  • 访问嵌套字段中的列时用点(.)连接

可空类型

  • 声明: Nullable(T)
  • 只能和基础类型搭配使用，不用用于复合类型和索引字段
  • 慎用，会额外生成 [Column].null.bin 文件保存 null 值，导致双倍文件操作，使查询和写入变慢

域名类型

声明 封装类型
IPv4 UInt32
IPv6 FixedString(16)
  • Domain 类型不是字符串，不支持自动类型转换
  • 调用 IPv4NumToString 或 IPv6NumToString 函数返回 IP 的字符串形式

数据库

操作

  • 创建

    1

    CREATE DATABASE [IF NOT EXISTS] db_name [ENGINE = engine];

  • 查看数据库列表

    1

    SHOW DATABASES;

  • 切换

    1

    USE db_name;

  • 查看当前数据库中的数据表列表

    1

    SHOW TABLES;

引擎

  • Ordinary: 默认引擎，无须刻意声明，可以使用任意类型表引擎
  • Dictionary: 字典引擎，自动为所有数据字典创建数据表
  • Memory: 内存引擎，存放临时数据，数据只停留在内存中
  • Lazy: 日志引擎，只能使用 Log 系列的表引擎
  • MySQL: MySQL 引擎，自动拉取远端 MySQL 中的数据，并创建 MySQL 表引擎的数据表

数据表

操作

  • 常规建表，默认在 default 数据库中创建

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

    CREATE TABLE [IF NOT EXISTS] [db_name.]table_name {
    column1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    column1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    ...
    columnM Nested(
        column11 [type],
        column22 [type],
        ...
    ),
    ...
} ENGINE = engine;

  • 复制其他表的结构

    1
2
3

    CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
    AS [db_name1.]table_name1
    [ENGINE = engine];

  • 通过 SELECT 字句建表，顺带写入 SELECT 子查询的数据

    1
2
3

    CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
    ENGINE = engine
    AS SELECT ... ;

  • 删表

    1

    DROP TABLE [IF EXISTS] [db_name.]table_name;

  • 增加表字段，默认值补全

    1
2

    ALTER TABLE [db_name.]table_name ADD COLUMN [IF NOT EXISTS] 
    col_name [type] [default_expr] [AFTER col_name_after];

  • 修改表字段

    1
2

    ALTER TABLE [db_name.]table_name MODIFY COLUMN [IF EXISTS]
    col_name [type] [default_expr];

  • 修改备注

    1
2

    ALTER TABLE [db_name.]table_name COMMENT COLUMN [IF EXISTS]
    name 'some comment';

  • 删除字段

    1

    ALTER TABLE [db_name.]table_name DROP COLUMN [IF EXISTS] name;

  • 移动数据表，只能在单节点内移动

    1
2
3

    RENAME TABLE [db_name.]tb_name TO [db_name1.]tb_name1,
    [db_name2.]tb_name2 TO [db_name3.]tb_name3,
    ... ;

  • 清空数据表

    1

    TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name;

表字段默认值

  • 声明: DEFAULT、MATERIALIZED、ALIAS
  • 如果表字段没有明确类型定义，则可根据默认值进行类型推断
  • 写入数据时，只有 DEFAULT 类型字段可以 INSERT
  • 查询数据时，只有 DEFUALT 类型字段可以 SELECT * 返回
  • DEFAULT 和 MATERIALIZED 类型字段可以持久化

临时表

操作

  • 创建 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

    CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name {
    column1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    column1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    ...
    columnM Nested(
        column11 [type],
        column22 [type],
        ...
    ),
    ...
};

其他

  • 临时表只支持 Memory 表引擎，与会话绑定
  • 临时表不属于任何数据库，创建时无数据库参数和表引擎参数
  • 临时表优先级大于普通表

分区表

操作

  • 创建

    1
2
3
4
5
6
7

    CREATE TABLE partition_v1 (
    ID String,
    URL String,
    EventTime Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMMM(EventTime)
GROUP BY ID;

  • 查看分区状态

    1

    SELECT table,partition,path from system.parts WHERE table = 'partition_v1'

  • 删除分区

    1

    ALTER TABLE tb_name DROP PARTITION part_expr;

  • 复制分区，前提是两表的结构和分区键相同

    1

    ALTER TABLE table_name1 REPLACE PARTITION part_expr FROM table_name;

  • 重置分区数据

    1

    ALTER TABLE table_name CLEAR COLUMN col_name IN PARTITION part_expr;

  • 卸载分区

    1

    ALTER TABLE table_name DETACH PARTITION part_expr;

  • 装载分区

    1

    ALTER TABLE table_name ATTACH PARTITION part_expr;

其他

  • 分区支持删除、替换和重置，只有 MergeTree 系列表引擎支持

视图

操作

  • 创建普通视图

    1

    CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ... ;

  • 创建物化视图

    1
2
3

    CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name
    [TO [db.]name] [ENGINE = engine] [POPULATE] AS SELECT ... ;
# 如果使用了 POPULATE 修饰符，那么在创建视图时会一并导入 SELECT 结果集

  • 查看物化视图列表

    1
2

    SHOW TABLES
# 输出前缀是 .inner.

  • 删除视图

    1

    DROP TABLE view_name

其他

  • 普通视图只是查询代理
  • 物化视图有独立存储，不支持同步删除

分布式 DDL

  • 使用 ON CLUSTER cluster_name 声明语句 
    1
2
3
4
5

    CREATE TABLE table_name ON CLUSTER cluster_name(
    col1 [type],
    col2 [type],
    ...
) ENGINE = engine ... ;

写入数据

  • INSERT 语句三种语法 
    1
2
3
4
5
6
7

    INSERT INTO [db_name.]table_name [(c1,c2,c3 ...)]
    VALUES (v1,v2,v3 ...), (v4,v5,v6 ...) ... ;

INSERT INTO [db_name.]table_name [(c1,c2,c3 ...)]
    FORMAT format_name data_set;

INSERT INTO [db_name.]table_name [(c1,c2,c3 ...)] SELECT ... ;

修改和删除数据

操作

  • 删除

    1

    ALTER TABLE [db_name.]table_name DELETE WHERE filter_expr;

  • 修改

    1
2

    ALTER TABLE [db_name.]table_name UPDATE col1 = expr1 [, ...]
    WHERE filter_expr;

其他

  • mutation 操作很重，后台执行，语句提交后立即返回，不支持事务，不能回滚
  • 通过 system.mutations 系统表查询进度