【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)
- Decimal64(S1)
- 小数点位数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?
- 出于便捷的考量，Domain类型支持格式校验，人性化的输入输出格式
- 出于性能的考量，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.
