【Clickhouse】clickhouse 数据类型

当前版本SELECT version()为 22.3.3.44
整理日期 2022-04
最新信息请查看官网，新版本可能会添加新的数据类型
https://clickhouse.com/docs/en/sql-reference/data-types/

1 基础类型 1.1 数值类型 1.1.1 整数 有符号数整形

Int8, Int16, Int32, Int64, Int128, Int256

名称 大小(字节) 范围 普遍概念 Int8 1 [-128 : 127] TINYINT, BOOL, BOOLEAN, INT1 Int16 2 [-32768 : 32767] SMALLINT, INT2 Int32 4 [-2147483648 : 2147483647] INT, INT4, INTEGER Int64 8 [-9223372036854775808 : 9223372036854775807] BIGINT 无符号数整型

UInt8, UInt16, UInt32, UInt64, UInt128, UInt256

名称 大小(字节) 范围 普遍概念 UInt8 1 [0 : 255] TINYINT Unsined UInt16 2 [0 : 65535] SMALLINTUnsined UInt32 4 [0 : 4294967295] INT Unsined UInt64 8 [0 : 18446744073709551615] BIGINT Unsined Boolean

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。

1.1.2 浮点数

建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示.

名称 大小(字节) 有效精度（位数） 普遍概念 Float32 4 7 Float Float64 8 16 Double

浮点型进行计算时可能引起四舍五入的误差。

ac27f97f6a77 :) SELECT 1 - 0.9

SELECT 1 - 0.9

Query id: 7cc88fd3-1e0b-4ec1-aa23-57fd333ec1f7

┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘

1 rows in set. Elapsed: 0.035 sec. 

ClickHouse直接使用Float32和Float64代表单精度浮点数以及双精度浮点数，Float32从小数点后第8位起及Float64从小数点后第17位起，会产生数据溢出

ac27f97f6a77 :) SELECT toFloat32('0.12345678901234567890') as a , toTypeName(a)

SELECT
    toFloat32('0.12345678901234567890') AS a,
    toTypeName(a)

Query id: c5d87476-acfb-4704-bb6f-c92483761808

┌──────────a─┬─toTypeName(toFloat32('0.12345678901234567890'))─┐
│ 0.12345679 │ Float32                                         │
└────────────┴─────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.032 sec. 

ac27f97f6a77 :) SELECT toFloat64('0.12345678901234567890') as a , toTypeName(a)

SELECT
    toFloat64('0.12345678901234567890') AS a,
    toTypeName(a)

Query id: a3839d5e-7252-4118-b1f2-85de2f910cc2

┌───────────────────a─┬─toTypeName(toFloat64('0.12345678901234567890'))─┐
│ 0.12345678901234568 │ Float64                                         │
└─────────────────────┴─────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.026 sec.

ClickHouse的浮点数支持正无穷、负无穷以及非数字的表达方式.

-- 正无穷 inf
ac27f97f6a77 :) SELECT 0.1/0

SELECT 0.1 / 0

Query id: aa43028a-8778-49dc-8f73-7b2a7c8d37bd

┌─divide(0.1, 0)─┐
│            inf │
└────────────────┘

1 rows in set. Elapsed: 0.033 sec. 

-- 负无穷 -inf
ac27f97f6a77 :) SELECT -0.1/0

SELECT -0.1 / 0

Query id: 84a1070b-09c7-4153-a743-c69a0dc67c19

┌─divide(-0.1, 0)─┐
│            -inf │
└─────────────────┘

1 rows in set. Elapsed: 0.032 sec. 

-- 非数字 nan
ac27f97f6a77 :) SELECT 0/0

SELECT 0 / 0

Query id: 925823e7-f76c-4a73-af16-9ced6ec5769e

┌─divide(0, 0)─┐
│          nan │
└──────────────┘

1 rows in set. Elapsed: 0.032 sec. 

1.1.3 定点数 定点数表示形式

有符号的浮点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃(不舍入)。
如果要求更高精度的数值运算，则需要使用定点数。ClickHouse提供了Decimal32、Decimal64、Decimal128和Decimal256四种精度的定点数。
可以通过两种形式声明定点：

• 简写方式有Decimal32(S)、Decimal64(S)、Decimal128(S)、Decimal256(S)四种
• 原生方式为Decimal(P,S)，其中
  • P代表精度，决定总位数（整数部分+小数部分），取值范围是[1:76]
  • S代表规模，决定小数位数，取值范围是[0:P]。

例如：Decimal32(4) 可以表示数字（-99999.9999, 99999.9999) , 步长为0.0001

**名称 等效声明 范围 精度P Decimal32(S) Decimal(9-s,s) ( -1 * 10^(9 - S), 1 * 10^(9 - S) ) [ 1 : 9 ] Decimal64(S) Decimal(18-s,s) ( -1 * 10^(18 - S), 1 * 10^(18 - S) ) [ 10 : 18 ] Decimal128(S) Decimal(38-s,s) ( -1 * 10^(38 - S), 1 * 10^(38 - S) ) [ 19 : 38 ] Decimal256(S) Decimal(76-s,s) ( -1 * 10^(76 - S), 1 * 10^(76 - S) ) [ 39 : 76 ]

使用场景: 一般金额字段、汇率、利率等字段为了保证小数点精度，都使用 Decimal进行存储

在使用定点数时还有一点值得注意：由于现代计算器系统只支持32位和64位CPU，所以Decimal128是在软件层面模拟实现的，它的速度会明显慢于Decimal32与Decimal64。

定点数运算及结果类型

在使用两个不同精度的定点数进行四则运算的时:

• 会得到更宽的结果类型(无关顺序)
  • Decimal64(S1) Decimal32(S2) -> Decimal64(S)
  • Decimal128(S1) Decimal32(S2) -> Decimal128(S)
  • Decimal128(S1) Decimal64(S2) -> Decimal128(S)
• 小数点位数S会发生变化

名称 精度变化规则 加减法 S = max(S1, S2) 乘法 S = S1 + S2 除法 S = S1

加法示例
Decemal32 op Decemal64 = Decemal64
S=max(S1, S2)=max(4, 2) = 4

-- 加法
840013eee323 :) SELECT toDecimal64(2,4) + toDecimal32(2,2) as a, toTypeName(a)

SELECT
    toDecimal64(2, 4) + toDecimal32(2, 2) AS a,
    toTypeName(a)

Query id: 31c174e5-e50f-4ac4-8642-e263854470c9

┌─a─┬─toTypeName(plus(toDecimal64(2, 4), toDecimal32(2, 2)))─┐
│ 4 │ Decimal(18, 4)                                         │
└───┴────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.012 sec.

减法示例
Decemal32 op Decemal64 = Decemal64
S=max(S1, S2)=max(2, 4) = 4

-- 减法
840013eee323 :) SELECT toDecimal64(4,2) - toDecimal32(2,4) as a, toTypeName(a)

SELECT
    toDecimal64(4, 2) - toDecimal32(2, 4) AS a,
    toTypeName(a)

Query id: 7188e58d-ee0b-4893-a48e-1717f90cd14e

┌─a─┬─toTypeName(minus(toDecimal64(4, 2), toDecimal32(2, 4)))─┐
│ 2 │ Decimal(18, 4)                                          │
└───┴─────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.031 sec.

乘法示例
Decemal32 op Decemal64 = Decemal64
S=S1+S2=4+2 = 6

840013eee323 :)  SELECT toDecimal64(2,4) * toDecimal32(2,2) as a, toTypeName(a)

SELECT
    toDecimal64(2, 4) * toDecimal32(2, 2) AS a,
    toTypeName(a)

Query id: 0c99d332-233c-4d14-964e-c9ba7a012029

┌─a─┬─toTypeName(multiply(toDecimal64(2, 4), toDecimal32(2, 2)))─┐
│ 4 │ Decimal(18, 6)                                             │
└───┴────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.015 sec.

除法示例
Decemal32 op Decemal64 = Decemal64
S=S1 = 4

840013eee323 :)  SELECT toDecimal64(2,4) / toDecimal32(2,2) as a, toTypeName(a)

SELECT
    toDecimal64(2, 4) / toDecimal32(2, 2) AS a,
    toTypeName(a)

Query id: 48020990-1e82-4682-837b-50b81cb054f5

┌─a─┬─toTypeName(divide(toDecimal64(2, 4), toDecimal32(2, 2)))─┐
│ 1 │ Decimal(18, 4)                                           │
└───┴──────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.013 sec. 

溢出检查

对 Decimal 类型执行操作时，数值可能会发生溢出。分数中的过多数字被丢弃（不是舍入的）。整数中的过多数字将导致异常

-- 默认溢出检查是打开的
840013eee323 :) SELECT toDecimal32(2, 4) AS x, x / 3

SELECT
    toDecimal32(2, 4) AS x,
    x / 3

Query id: 44ec8d15-7db1-43bd-88c1-1d50a7afecf3

┌─x─┬─divide(toDecimal32(2, 4), 3)─┐
│ 2 │                       0.6666 │
└───┴──────────────────────────────┘

1 rows in set. Elapsed: 0.029 sec.

-- 检查溢出会导致计算变慢。如果已知溢出不可能，则可以通过设置decimal_check_overflow来禁用溢出检查，在这种情况下，溢出将导致结果不正确：
840013eee323 :) SET decimal_check_overflow = 0;

SET decimal_check_overflow = 0

Query id: 74b7b860-250f-42fe-9421-dba591f2a913

Ok.

0 rows in set. Elapsed: 0.019 sec.
840013eee323 :) SELECT toDecimal32(4.2, 8) AS x, 6 * x

SELECT
    toDecimal32(4.2, 8) AS x,
    6 * x

Query id: c195a43d-3ba7-4f19-b161-e965c18c51e1

┌───x─┬─multiply(6, toDecimal32(4.2, 8))─┐
│ 4.2 │                     -17.74967296 │
└─────┴──────────────────────────────────┘

1 rows in set. Elapsed: 0.015 sec. 


--溢出检查不仅发生在算术运算上，还发生在比较运算上：
SET decimal_check_overflow = 1; --恢复默认溢出检查
840013eee323 :) SELECT toDecimal32(1, 8) < 100

SELECT toDecimal32(1, 8) < 100

Query id: 0e3b112f-1603-4cc9-b264-c8be6d419e55


0 rows in set. Elapsed: 0.037 sec. 

Received exception from server (version 22.3.3):
Code: 407. DB::Exception: Received from localhost:9000. DB::Exception: Can't compare decimal number due to overflow: While processing toDecimal32(1, 8) < 100. (DECIMAL_OVERFLOW)

1.2 字符串类型 1.2.1 String

String是不限长的，它可以包含任意的字节集，包含空字节。
String — LONGTEXT, MEDIUMTEXT, TINYTEXT, TEXT, LONGBLOB, MEDIUMBLOB, TINYBLOB, BLOB, VARCHAR, CHAR

1.2.2 FixedString

固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的 字符串时候，将返回错误消息。
与 String 相比，极少会使用 FixedString，因为使用起来不是很方便。
选择数据时，ClickHouse 不会删除字符串末尾的空字节。如果使用该WHERE子句，则应手动添加空字节以匹配该FixedString值。以下示例说明了如何将WHERE子句与FixedString.

CREATE TABLE FixedStringTable
(
    `s` FixedString(2)
)
ENGINE = Memory

1.2.3 UUID

UUID是一种数据库常见的主键类型，在ClickHouse中直接把它作为一种数据类型。UUID共有32位，它的格式为8-4-4-4-12。如果一个UUID类型的字段在写入数据时没有被赋值，则会依照格式使用0填充

-- 创建一个测试表
CREATE TABLE t_uuid (
    x UUID,
    y String
) ENGINE = Memory;

-- 插入一条数据，使用generateUUIDv4（）生成uuid
INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1'

-- select查看帮我们生成了UUID
840013eee323 :) SELECT * FROM t_uuid
                
SELECT *
FROM t_uuid

Query id: 5435e8e0-592f-4c8d-b09f-a027dcc9a688

┌─x────────────────────────────────────┬─y─────────┐
│ f349e2b9-5caf-4e16-a192-1573b19b19c3 │ Example 1 │
└──────────────────────────────────────┴───────────┘

1 rows in set. Elapsed: 0.015 sec. 

-- 再插入一条数据，UUID不输入值
INSERT INTO t_uuid (y) VALUES ('Example 2')

840013eee323 :) SELECT * FROM t_uuid
                
-- 再次查询发现，不插入UUID，会按UUID格式全部赋值为0
SELECT *
FROM t_uuid

Query id: c8751de4-0646-4707-9923-e219f8709853

┌─x────────────────────────────────────┬─y─────────┐
│ f349e2b9-5caf-4e16-a192-1573b19b19c3 │ Example 1 │
└──────────────────────────────────────┴───────────┘
┌─x────────────────────────────────────┬─y─────────┐
│ 00000000-0000-0000-0000-000000000000 │ Example 2 │
└──────────────────────────────────────┴───────────┘

2 rows in set. Elapsed: 0.012 sec. 

1.3 时间类型

时间类型分为DateTime、DateTime64、Date、Date32四类。ClickHouse目前没有时间戳类型。时间类型最高的精度是秒，也就是说，如果需要处理毫秒、微秒等大于秒分辨率的时间，则只能借助UInt类型实现。

1.3.1 DateTime

时间戳类型。用四个字节（无符号的）存储 Unix 时间戳。精确到秒。
值的范围: [1970-01-01 00:00:00, 2106-02-07 06:28:15]

Tips：

• 将 datetime 作为整数插入时，它被视为 Unix Timestamp (UTC)。1546300800代表'2019-01-01 00:00:00'UTC。但是，由于timestamp列已Asia/Istanbul指定 (UTC+3) 时区，当输出为字符串时，值将显示为'2019-01-01 03:00:00'
• 将字符串值作为日期时间插入时，它被视为在列时区中。'2019-01-01 00:00:00'将被视为处于Asia/Istanbul时区并保存为1546290000.

-- 支持的时区可通过SELECT * FROM system.time_zones查看，默认使用服务端时区，clickhouse-client可通过--use_client_time_zone使用
-- 这里指定时区Asia/Istanbul
CREATE TABLE dt_time
(
    `timestamp` DateTime('Asia/Istanbul'),
    `event_id` UInt8
)
ENGINE = Memory;

INSERT INTO dt_time Values (1546300800, 1), ('2019-01-01 00:00:00', 2);

-- 查看
840013eee323 :)  SELECT timestamp, event_id, toTypeName(timestamp) FROM dt_time;

SELECT
    timestamp,
    event_id,
    toTypeName(timestamp)
FROM dt_time

Query id: 94617fa9-ab85-4933-ac23-e89584059e3b

┌───────────timestamp─┬─event_id─┬─toTypeName(timestamp)─────┐
│ 2019-01-01 03:00:00 │        1 │ DateTime('Asia/Istanbul') │ 
│ 2019-01-01 00:00:00 │        2 │ DateTime('Asia/Istanbul') │
└─────────────────────┴──────────┴───────────────────────────┘

2 rows in set. Elapsed: 0.033 sec. 

-- 日期过滤查询
SELECT * FROM dt_time WHERE timestamp = toDateTime('2019-01-01 00:00:00', 'Asia/Istanbul')
SELECT * FROM dt_time WHERE timestamp = '2019-01-01 00:00:00'
-- 获取时区时间
SELECT toDateTime(now(), 'Asia/Istanbul') AS column, toTypeName(column) AS x
SELECT toDateTime(now()) AS column, toTypeName(column) AS x
-- 时区转化
SELECT
toDateTime(timestamp, 'Europe/London') as lon_time,
toDateTime(timestamp, 'Asia/Istanbul') as mos_time
FROM dt_time

1.3.2 DateTime64

DateTime64可以记录亚秒，它在DateTime之上增加了精度的设置。
精度：10-precision，有效范围：[ 0 : 9 ]
常用precision - 3（毫秒）、6（微秒）、9（纳秒）
语法：DateTime64(precision, [timezone])

CREATE TABLE dt_time64
(
    `timestamp` DateTime64(3, 'Asia/Istanbul'),
    `event_id` UInt8
)
ENGINE = Memory;

INSERT INTO dt_time64 Values (1546300800000, 1), ('2019-01-01 00:00:00', 2);
--查看
840013eee323 :) SELECT timestamp, event_id, toTypeName(timestamp) FROM dt_time64;
                

SELECT
    timestamp,
    event_id,
    toTypeName(timestamp)
FROM dt_time64

Query id: 58bb31be-7bb8-465b-bc9f-6afc18c72374

┌───────────────timestamp─┬─event_id─┬─toTypeName(timestamp)──────────┐
│ 2019-01-01 03:00:00.000 │        1 │ DateTime64(3, 'Asia/Istanbul') │
│ 2019-01-01 00:00:00.000 │        2 │ DateTime64(3, 'Asia/Istanbul') │
└─────────────────────────┴──────────┴────────────────────────────────┘

2 rows in set. Elapsed: 0.038 sec. 

1.3.3 Date

日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。
取值范围: [1970-01-01, 2149-06-06]。
注意，其实写入一条超过时间范围的数值也是能写进去，并能查出来的，如：INSERT INTO dt VALUES ('2050-01-01', 3);

CREATE TABLE dt
(
    `timestamp` Date,
    `event_id` UInt8
)
ENGINE = Memory;
INSERT INTO dt VALUES (1546300800, 1), ('2019-01-01', 2);

--查看
840013eee323 :) SELECT timestamp, event_id, toTypeName(timestamp) FROM dt;

SELECT
    timestamp,
    event_id,
    toTypeName(timestamp)
FROM dt

Query id: b42b081d-de80-4a85-b735-b6f1d3d91ef1

┌──timestamp─┬─event_id─┬─toTypeName(timestamp)─┐
│ 2019-01-01 │        1 │ Date                  │
│ 2019-01-01 │        2 │ Date                  │
└────────────┴──────────┴───────────────────────┘

2 rows in set. Elapsed: 0.029 sec. 

1.3.4 Date32

日期类型，用四个字节存储，表示从从 1925-01-01 (无符号) 到当前的日期值。
取值范围同DateTime64的日期范围 [1925-01-01, 2283-11-11]

CREATE TABLE dt32
(
    `timestamp` Date32,
    `event_id` UInt8
)
ENGINE = Memory;

INSERT INTO dt32 VALUES (7258118400, 1), ('2200-01-01', 2);

--查看
840013eee323 :) SELECT timestamp, event_id, toTypeName(timestamp) FROM dt32;

SELECT
    timestamp,
    event_id,
    toTypeName(timestamp)
FROM dt32

Query id: 39b2b956-6c28-4036-a5c3-d8cd2fe00006

┌──timestamp─┬─event_id─┬─toTypeName(timestamp)─┐
│ 2106-02-07 │        1 │ Date32                │
│ 2200-01-01 │        2 │ Date32                │
└────────────┴──────────┴───────────────────────┘

2 rows in set. Elapsed: 0.028 sec. 

2 复合类型 2.1 Array(T)

clickhouse起始数组索引为1，类型T可以是任何数据类型。
创建数组的两种方式：

• 通过函数创建：array(T)
• 使用方扩号：[]

-- 在动态创建数组时，ClickHouse 会自动将参数类型定义为可以存储所有列出的参数的最窄数据类型，即以最小存储代价为原则
-- array(T)
840013eee323 :) SELECT array(1, 2) AS x, toTypeName(x)
                
SELECT
    [1, 2] AS x,
    toTypeName(x)

Query id: aaaf4542-b5bd-4c54-b7e4-42a95b65baa7

┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8)       │
└───────┴────────────────────┘

1 rows in set. Elapsed: 0.041 sec. 

-- 方扩号 []
-- 同一数组可以有不同类型，但是类型必须兼容，如果[1, 'a']就会报错
840013eee323 :) SELECT [1, 2.0] AS x, toTypeName(x)

SELECT
    [1, 2.] AS x,
    toTypeName(x)

Query id: fcb358f3-89de-4907-9a2d-2c581bdf98ab

┌─x─────┬─toTypeName([1, 2.])─┐
│ [1,2] │ Array(Float64)      │
└───────┴─────────────────────┘

1 rows in set. Elapsed: 0.017 sec.

-- 如果有任何Nullable或文字NULL值，则数组元素的类型会统一变为Nullable
-- 如果 ClickHouse 无法确定数据类型，则会抛出异常
840013eee323 :) SELECT array(1, 2, NULL) AS x, toTypeName(x)
                

SELECT
    [1, 2, NULL] AS x,
    toTypeName(x)

Query id: 535c4f47-c756-4903-846f-efd019a90b4d

┌─x──────────┬─toTypeName([1, 2, NULL])─┐
│ [1,2,NULL] │ Array(Nullable(UInt8))   │
└────────────┴──────────────────────────┘

1 rows in set. Elapsed: 0.024 sec. 

-- 对于多维数组，您可以使用sizeN-1指定维度，获取该维度的大小，而不需要读取整列
CREATE TABLE t_arr (`arr` Array(Array(Array(UInt32)))) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO t_arr VALUES ([[[12, 13, 0, 1],[12]]]);

840013eee323 :) SELECT arr.size0, arr.size1, arr.size2 FROM t_arr;          

SELECT
    arr.size0,
    arr.size1,
    arr.size2
FROM t_arr

Query id: ecb76832-6005-403a-8b9e-6ffb4ead225b

┌─arr.size0─┬─arr.size1─┬─arr.size2─┐
│         1 │ [2]       │ [[4,1]]   │
└───────────┴───────────┴───────────┘
1 rows in set. Elapsed: 0.051 sec.

2.2 Tuple

元组类型由1～n个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间不要求兼容。元组同样支持类型推断，其推断依据仍然以最小存储代价为原则。
元组定义方式：

• 函数方式：tuple(T1, T2, ...)
• 圆扩号：（T1, T2, ...）

840013eee323 :) SELECT tuple(1,'a') AS x, toTypeName(x)
                
SELECT
    (1, 'a') AS x,
    toTypeName(x)

Query id: 1b470c56-d8d0-47dc-8ac5-c7cd853b41f1

┌─x───────┬─toTypeName((1, 'a'))─┐
│ (1,'a') │ Tuple(UInt8, String) │
└─────────┴──────────────────────┘

1 rows in set. Elapsed: 0.039 sec. 

-- 查询
CREATE TABLE named_tuples (`a` Tuple(s String, i Int64)) ENGINE = Memory;

INSERT INTO named_tuples VALUES (('y', 10)), (('x',-10));

840013eee323 :) SELECT a.s FROM named_tuples;

SELECT a.s
FROM named_tuples

Query id: b964f84a-b6de-47b2-a587-4ba790623d78

┌─a.s─┐
│ y   │
│ x   │
└─────┘

2 rows in set. Elapsed: 0.018 sec.

840013eee323 :) SELECT a.2 FROM named_tuples;

SELECT a.2
FROM named_tuples

Query id: a2f16f29-ea87-4bb5-9f24-d3e1bf845a5a

┌─tupleElement(a, 2)─┐
│                 10 │
│                -10 │
└────────────────────┘

2 rows in set. Elapsed: 0.035 sec. 

2.3 Enum

ClickHouse支持枚举类型，这是一种在定义常量时经常会使用的数据类型。
ClickHouse支持两种枚举类型，它们除了取值范围不同之外，别无二致：

• 8 位Enum. 它最多可以包含在该[-128, 127]范围内枚举的 256 个值
• 16 位Enum。它最多可以包含在该[-32768, 32767]范围内枚举的 65536 个值

枚举固定使用'string' = integer键值对的形式定义数据。

可能有人会觉得，完全可以使用String代替枚举，为什么还需要专门的枚举类型呢？这是出于性能的考虑。因为虽然枚举定义中的Key属于String类型，但是在后续对枚举的所有操作中（包括排序、分组、去重、过滤等），会使用Int类型的Value值。

CREATE TABLE t_enum
(
    x Enum('hello' = 1, 'world' = 2)
)
ENGINE = Memory

INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')

840013eee323 :) INSERT INTO t_enum values('a')
                
-- 插入不存在的值会报错
840013eee323 :) INSERT INTO t_enum values('a')
               
INSERT INTO t_enum FORMAT Values

Query id: c23a0ac1-75c5-4e5b-a9f5-c359ab77f62d

Exception on client:
Code: 36. DB::Exception: Unknown element 'a' for enum: While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (BAD_ARGUMENTS)

-- 查询元素得到的是字符串name
840013eee323 :) SELECT * FROM t_enum
                

SELECT *
FROM t_enum

Query id: c4dc6929-5b77-4571-9fd1-5e4f0f9c0f26

Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.3.3 revision 54455.

┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘

3 rows in set. Elapsed: 0.042 sec. 

-- 如果您需要查等效数字，则必须将Enum值转换为整数类型
840013eee323 :) SELECT CAST(x, 'Int8') FROM t_enum               

SELECT CAST(x, 'Int8')
FROM t_enum

Query id: 865f450b-b725-48e2-860d-b4a17c165b9b

┌─CAST(x, 'Int8')─┐
│               1 │
│               2 │
│               1 │
└─────────────────┘

3 rows in set. Elapsed: 0.041 sec. 

-- 在查询中创建Enum，需要CAST
840013eee323 :) SELECT toTypeName(CAST('a', 'Enum(\'a\' = 1, \'b\' = 2)'))
                

SELECT toTypeName(CAST('a', 'Enum(\'a\' = 1, \'b\' = 2)'))

Query id: 9b91895a-2200-436b-96fb-c314b3af40a1

┌─toTypeName(CAST('a', 'Enum(\'a\' = 1, \'b\' = 2)'))─┐
│ Enum8('a' = 1, 'b' = 2)                             │
└─────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.029 sec. 

2.4 Nested

嵌套类型，顾名思义是一种嵌套表结构。一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系，使用嵌套类型也是一种不错的选择。

CREATE TABLE nested_test
(
    name String,
    age UInt8,
    dept Nested
    (
        id UInt8,
        name String
    )
) ENGINE = Memory

-- 注意：nested_test与dept并不是一对一的包含关系，这样写入会报错的

840013eee323 :) INSERT INTO nested_test VALUES ('nauu',18, 10000, '研发部');

INSERT INTO nested_test FORMAT Values

Query id: 7e766cc1-f146-4542-80c2-4e104e51b9bb

Exception on client:
Code: 53. DB::Exception: Type mismatch in IN or VALUES section. Expected: Array(UInt8). Got: UInt64: While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (TYPE_MISMATCH)

--  嵌套类型本质是一种多维数组的结构，嵌套表中的每个字段都是一个数组，并且行与行之间数组的长度无须对齐，但是行内数组字段的长度没有对齐
INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001,10002], ['研发部','技术支持中心','测试部']);
INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001], ['研发部','技术支持中心']);
840013eee323 :) INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001], ['研发部','技术支持中心','测试部']); 

INSERT INTO nested_test FORMAT Values

Query id: e9de8279-7b4d-4ff3-8082-f22f7b0bc135


1 rows in set. Elapsed: 0.021 sec. 

Received exception from server (version 22.3.3):
Code: 190. DB::Exception: Received from localhost:9000. DB::Exception: Elements 'dept.id' and 'dept.name' of Nested data structure 'dept' (Array columns) have different array sizes.. (SIZES_OF_ARRAYS_DOESNT_MATCH)

-- 查询
840013eee323 :) SELECT name, dept.id, dept.name FROM nested_test

SELECT
    name,
    dept.id,
    dept.name
FROM nested_test

Query id: b00fbc7a-bef1-4b8b-b3f6-a33bb3f76a06

┌─name──┬─dept.id────┬─dept.name──────────────────────────┐
│ bruce │ [16,17,18] │ ['研发部','技术支持中心','测试部'] │
└───────┴────────────┴────────────────────────────────────┘
┌─name──┬─dept.id─┬─dept.name─────────────────┐
│ bruce │ [16,17] │ ['研发部','技术支持中心'] │
└───────┴─────────┴───────────────────────────┘

2 rows in set. Elapsed: 0.033 sec. 

默认 flatten_nested = 1

-- flatten_nested = 1
SET flatten_nested = 1;
CREATE TABLE t_nest (`n` Nested(a UInt32, b UInt32)) ENGINE = MergeTree ORDER BY tuple();
840013eee323 :) SHOW CREATE TABLE t_nest;

SHOW CREATE TABLE t_nest

Query id: a9438b0c-65b0-471d-9ad0-67e0b628ae6e

┌─statement───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.t_nest
(
    `n.a` Array(UInt32),
    `n.b` Array(UInt32)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.013 sec. 

INSERT INTO t_nest VALUES ([1,2],[100,101]);

-- flatten_nested = 0
SET flatten_nested = 0;

CREATE TABLE t_nest2 (`n` Nested(a UInt32, b UInt32)) ENGINE = MergeTree ORDER BY tuple();

840013eee323 :) SHOW CREATE TABLE t_nest2;

SHOW CREATE TABLE t_nest2

Query id: 70b7cd13-ba42-40b5-933d-4035f433da1c

┌─statement───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.t_nest2
(
    `n` Nested(a UInt32, b UInt32)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.030 sec. 


INSERT INTO t_nest2 VALUES ([(1, 100)]);

2.5 Map

Map(key, value)数据类型存储key:value对

• key— 键。类型可以为：String、Integer、LowCardinality或FixedString。
• value—值。类型可以为：String、Integer、Array、LowCardinality或FixedString

CREATE TABLE table_map (a Map(String, UInt64)) ENGINE=Memory;
INSERT INTO table_map VALUES ({'key1':1, 'key2':10}), ({'key1':2,'key2':20}), ({'key1':3,'key2':30});

-- 查询
840013eee323 :) SELECT a['key2'] FROM table_map;
                

SELECT a['key2']
FROM table_map

Query id: 781bacf5-b11e-4b4a-9924-28e5e007f80a

┌─arrayElement(a, 'key2')─┐
│                      10 │
│                      20 │
│                      30 │
└─────────────────────────┘

3 rows in set. Elapsed: 0.020 sec.


-- 如果列中没有此类key，则为数值类型返回0，空字符串或者空数组。
INSERT INTO table_map VALUES ({'key3':100}), ({});
840013eee323 :) SELECT a['key3'] FROM table_map;

SELECT a['key3']
FROM table_map

Query id: 605a8e55-182b-489d-b566-a14e0a0408cc

┌─arrayElement(a, 'key3')─┐
│                       0 │
│                       0 │
│                       0 │
└─────────────────────────┘
┌─arrayElement(a, 'key3')─┐
│                     100 │
│                       0 │
└─────────────────────────┘

5 rows in set. Elapsed: 0.032 sec.

3 特殊类型 3.1 Nullable

通过存储特殊值NULL来表示缺失值，NULL是任何Nullable类型的默认值，除非在 ClickHouse 服务器配置中另有指定。需要与数据类型一起搭配使用。

• Nullable(TypeName),这里TypeName只能是基础类型而不能是复合数据类型Array和Tuple，如：Nullable(Int8)类型列可以存储Int8类型值，而没有值的行将存储NULL
• 但复合数据类型可以包含Nullable类型值，例如Array(Nullable(Int8))

注意：使用Nullable几乎总是会对性能产生负面影响，在设计数据库时请记住这一点。

• 它只能和基础类型搭配使用，不能用于数组和元组这些复合类型，也不能作为索引字段
• 应该慎用Nullable类型，包括Nullable的数据表，不然会使查询和写入性能变慢。
  • 因为在正常情况下，每个列字段的数据会被存储在对应的[Column].bin文件中。如果一个列字段被Nullable类型修饰后，会额外生成一个[Column].null.bin文件专门保存它的Null值。这意味着在读取和写入数据时，需要一倍的额外文件操作

CREATE TABLE nullable (`n` Nullable(UInt32)) ENGINE = MergeTree ORDER BY tuple();

INSERT INTO nullable VALUES (1) (NULL) (2) (NULL);

-- 可以通过使用null子列而不读取整列来查找列中的NULL值。如果对应的值是NULL，则返回1，否则返回0
840013eee323 :) SELECT n.null FROM nullable;

SELECT n.`null`
FROM nullable

Query id: daea8aa1-82ed-41ae-90a9-8b89186970d5

┌─n.null─┐
│      0 │
│      1 │
│      0 │
│      1 │
└────────┘

4 rows in set. Elapsed: 0.031 sec. 

-- 使用示例
CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE Memory;
INSERT INTO t_null VALUES (1, NULL), (2, 3)

840013eee323 :) SELECT x + y FROM t_null;

SELECT x + y
FROM t_null

Query id: 041a6863-e49e-41c5-993c-9f53a304b37a

┌─plus(x, y)─┐
│       ᴺᵁᴸᴸ │
│          5 │
└────────────┘

2 rows in set. Elapsed: 0.031 sec.

3.2 Domain

• IPv4是基于UInt32类型的域
• IPv6是基于FixedString(16)类型的域

为什么不用字符串类型代替Domain?

1. 出于便捷的考量，Domain类型支持格式校验，人性化的输入输出格式
2. 出于性能的考量，Pv4使用UInt32存储，IPv6类型是基于FixedString(16)，相比String更紧凑，占用空间少，查询性能快。

CREATE TABLE hits (url String, from4 IPv4, from6 IPv6) ENGINE = MergeTree() ORDER BY url;

840013eee323 :) DESCRIBE TABLE hits;

DESCRIBE TABLE hits

Query id: 4bb5b4df-31b0-4fe1-8723-b54dbfaf1d1a

┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ url   │ String │              │                    │         │                  │                │
│ from4 │ IPv4   │              │                    │         │                  │                │
│ from6 │ IPv6   │              │                    │         │                  │                │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

3 rows in set. Elapsed: 0.023 sec. 

INSERT INTO hits (url, from4,from6) VALUES ('https://wikipedia.org', '116.253.40.133', '2a02:aa08:e000:3100::2')('https://clickhouse.com', '183.247.232.58', '2001:44c8:129:2632:33:0:252:2');


840013eee323 :) SELECT url, from4, toTypeName(from4), from6, toTypeName(from6) FROM hits;

SELECT
    url,
    from4,
    toTypeName(from4),
    from6,
    toTypeName(from6)
FROM hits

Query id: e9c2c4d2-62af-4282-91d8-0071c3d54f22

┌─url────────────────────┬─from4──────────┬─toTypeName(from4)─┬─from6─────────────────────────┬─toTypeName(from6)─┐
│ https://clickhouse.com │ 183.247.232.58 │ IPv4              │ 2001:44c8:129:2632:33:0:252:2 │ IPv6              │
│ https://wikipedia.org  │ 116.253.40.133 │ IPv4              │ 2a02:aa08:e000:3100::2        │ IPv6              │
└────────────────────────┴────────────────┴───────────────────┴───────────────────────────────┴───────────────────┘

2 rows in set. Elapsed: 0.048 sec. 

Snow nothing, reap nothing.