十大数据类型
想知道Clickhouse都有哪些数据类型和哪些设置，可以通过如下SQL语句查询。
:) SELECT * FROM system.data_type_families
:) SELECT * from system.settings需要注意以下事实。
不同Clickhouse版本的数据类型数量不一样，因为它还在持续更新。
case_insensitive值为1表示数据类型不区分大小写，为0表示字段类型需要严格区分大小写。alias_to表示数据类型的别名。有些新增加的数据类型用的也不多，所以只需要关注那些常用数据类型就行了。
1. 整数
有符号整数
Int8— [-128 : 127]Int16— [-32768 : 32767]Int32— [-2147483648 : 2147483647]Int64— [-9223372036854775808 : 9223372036854775807]Int128— [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]Int256— [-57896044618658097711785492504343953926634992332820282019728792003956564819968 : 57896044618658097711785492504343953926634992332820282019728792003956564819967]
无符号整数
UInt8— [0 : 255]UInt16— [0 : 65535]UInt32— [0 : 4294967295]UInt64— [0 : 18446744073709551615]UInt128— [0 : 340282366920938463463374607431768211455]UInt256— [0 : 115792089237316195423570985008687907853269984665640564039457584007913129639935]
类型别名
Int8—TINYINT/BOOL/BOOLEAN/INT1Int16—SMALLINT/INT2Int32—INT/INT4/INTEGERInt64—BIGINT
2. 浮点数
Float
Float32：对应MySQL的float类型。
Float64：对应MySQL的double类型。
尽可能以整数形式存储数据。例如货币数量或页面加载时间用毫秒为单位表示，因为它和Java一样，也会引起精度丢失问题。
:) SELECT 1 - 0.9;
┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘Clickhouse还支持两类浮点数。
Inf：正无穷数。-Inf：负无穷数。
:) SELECT 0.5 / 0;
┌─divide(0.5, 0)─┐
│ inf │
└────────────────┘
:) SELECT -0.5 / 0;
┌─divide(-0.5, 0)─┐
│ -inf │
└─────────────────┘NaN：表示一个非数字值。
:) SELECT 0 / 0;
┌─divide(0, 0)─┐
│ nan │
└──────────────┘Decimal
Decimal(P, S)P表示精度，有效范围是1~38之间，决定整个数字有多少个十进制数字（包括分数S在内）S表示规模（Scale），也就是小数位数。例如，
Decimal(10, 2)表示小数部分2位，整数部分8位（8 = 10 - 2）。
Decimal32(S)- ( -1 * 10^(9 - S),1*10^(9-S) )Decimal64(S)- ( -1 * 10^(18 - S),1*10^(18-S) )Decimal128(S)- ( -1 * 10^(38 - S),1*10^(38-S) )
尽可能少地使用Decimal类型，因为它会出现地板除的问题，且整数中的过多数字将导致异常。
-- 地板除
:) SELECT toDecimal32(2, 4) AS x, x / 3
┌──────x─┬─divide(toDecimal32(2, 4), 3)─┐
│ 2.0000 │ 0.6666 │
└────────┴──────────────────────────────┘
-- 数值溢出
:) SELECT toDecimal32(4.2, 8) AS x, x * x
DB::Exception: Scale is out of bounds.
-- 异常
:) SELECT toDecimal32(4.2, 8) AS x, 6 * x
DB::Exception: Decimal math overflow.
-- 结果错误
:) SELECT toDecimal32(1, 8) < 100
DB::Exception: Can't compare.3. 字符串
UUID
虽然它产生一个16字节的数字，作为ID使用，但我还是把它归类为字符串一类。
:) CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLog;
:) INSERT INTO t_uuid SELECT generateUUIDv4(), 'TestUUID';
:) SELECT * FROM t_uuid;
┌────────────────────────────────────x─┬─y─────────┐
│ e0f1c1ae-0c5f-4c7d-8752-ad995b6aa38c │ TestUUID │
└──────────────────────────────────────┴───────────┘String
字符串可以是任意长度的，也可以是空字符串。
字符串类型可以代替其他DBMS系统（例如MySQL）中的VARCHAR、BLOB、CLOB等类型。
Clickhouse没有编码的概念，它会把数据按它们原本的方式进行存储和输出。所以尽量使用诸如UTF8或UTF8BM4这类事实上的标准编码。
FixedString（定长字符串）
FixedString(N)，N表示正整数。
如果字符串包含的字节数少于
N，将对字符串末尾进行空字节填充。如果字符串包含的字节数大于
N，将抛出Too large value for FixedString(N)异常。
它类似于MySQL中的char类型，所以当数据的长度恰好为N个字节时，FixedString的效率是最高的。
4. 日期时间
Date
用两个字节存储，表示从1970-01-01开始到当前的日期值，支持字符串形式写入。
其时间范围是[1970-01-01, 2149-06-06]。
DateTime
它比Date精确度高，用四个无符号字节存储Unix时间戳。
允许存储与日期类型相同范围内的值，其最小值为1970-01-01 00:00:00（没有闰秒）。
DateTime64
它允许以日期（date）加时间（time）的形式来存储某个时刻的时间值，具有亚秒级精度。
其时间范围是[1900-01-01 00:00:00, 2299-12-31 23:59:59.99999999]，最大值的精度是8。
当将整数作为日期时间类型插入时，它会被视为Unix时间戳。例如1546300800000表示 '2024-01-01 00:00:00.000' UTC。
当将字符串作为日期时间类型插入时，它会被赋予时区信息。例如2024-01-01 00:00:00将被认为处于Asia/Istanbul时区并被存储为1546290000000。
:) CREATE TABLE dt
(
timestamp DateTime64(3, 'Asia/Istanbul'),
event_id UInt8
) ENGINE = TinyLog;
:) INSERT INTO dt Values (1546300800000, 1), ('2024-01-01 00:00:00', 2);
:) SELECT * FROM dt;
┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00.000 │ 1 │
│ 2024-01-01 00:00:00.000 │ 2 │
└─────────────────────────┴──────────┘- 过滤
DateTime64类型的值。
:) SELECT * FROM dt WHERE timestamp = toDateTime64('2024-01-01 00:00:00', 3, 'Asia/Istanbul')
┌──────────────────column─┬─x──────────────────────────────┐
│ 2024-01-01 00:00:00.000 │ DateTime64(3, 'Asia/Istanbul') │
└─────────────────────────┴────────────────────────────────┘- 获取
DateTime64类型值的时区信息。
:) SELECT toDateTime64(now(), 3, 'Asia/Istanbul') AS column, toTypeName(column) AS x
┌──────────────────column─┬─x──────────────────────────────┐
│ 2024-04-04 21:12:04.000 │ DateTime64(3, 'Asia/Istanbul') │
└─────────────────────────┴────────────────────────────────┘- 时区转换。
:) SELECT
toDateTime64(timestamp, 0, 'Asia/Shanghai') as ash_time,
toDateTime64(timestamp, 0, 'Asia/Istanbul') as mos_time
FROM dt
┌───────────ash_time──┬────────────mos_time─┐
│ 2019-01-01 08:00:00 │ 2019-01-01 03:00:00 │
│ 2024-01-01 05:00:00 │ 2024-01-01 00:00:00 │
└─────────────────────┴─────────────────────┘Date32
它支持与DateTime64相同的日期范围。
其值为自1970-01-01以来的天数。其中，0表示1970-01-01，负值表示1970年之前的天数。
:) CREATE TABLE dt32
(
timestamp Date32,
event_id UInt8
)
ENGINE = TinyLog;
-- 从字符串解析日期
-- 从整形解析日期
-- 从长整型解析日期
:) INSERT INTO dt32 VALUES ('2100-01-01', 1), (47482, 2), (4102444800, 3);
:) SELECT * FROM dt32;
┌──timestamp─┬─event_id─┐
│ 2100-01-01 │ 1 │
│ 2100-01-01 │ 2 │
│ 2100-01-01 │ 3 │
└────────────┴──────────┘5. 布尔值Bool
旧版以前没有单独的类型来存储布尔值，而是使用UInt8类型来代替，取值限制为0或1。
6. 枚举Enum
枚举包括Enum8和Enum16两种类型。
Enum8用'String'= Int8描述，每个值范围是-128 ... 127。Enum16用'String'= Int16描述，每个值范围是-32768 ... 32767。
所有的字符串或者数字都必须是不一样的，允许存在空字符串，数字可以是任意顺序，因为顺序并不重要。
创建一个带有Enum8('hello' = 1, 'world' = 2)类型的列。
:) CREATE TABLE t_enum
(
x Enum8('hello' = 1, 'world' = 2)
) ENGINE = TinyLog;
-- x列只能存储枚举中列出的值（'hello'或'world'），否则Clickhouse将抛出异常
:) INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello');
:) SELECT * FROM t_enum;
┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘如果需要看到对应行的数值，则必须将Enum值转换为整数类型。
:) SELECT CAST(x, 'Int8') FROM t_enum;
┌─CAST(x, 'Int8')─┐
│ 1 │
│ 2 │
│ 1 │
└─────────────────┘Enum中的字符串和数值都不能是NULL，但Enum却包含在可为空的类型中。
-- 创建一个可为空的字段，其类型为Enum8
:) CREATE TABLE t_enum_nullable
(
x Nullable( Enum8('hello' = 1, 'world' = 2) )
) ENGINE = TinyLog;
-- 它不仅可以存储'hello'和'world'，甚至还可以存储NULL
:) INSERT INTO t_enum_nullable Values('hello'), ('world'), (NULL);
:) SELECT * FROM t_enum_nullable;
┌─Nullable( Enum8('hello' = 1, 'world' = 2) )─┐
│ hello │
│ world │
│ NULL │
└─────────────────────────────────────────────┘7. 元组Tuple(T1, T2, ...)
元组是高阶函数使用得非常普遍的容器数据类型之一，很多高阶函数的特性都是通过元组实现的。
元组的标记是('tuple', 1)——采用小括号()表示。
元组中每个元素都有单独的类型。
无法在表中存储元组，但它们可以用于临时列分组。
创建元组。
:) CREATE TABLE t_tuple
(
id UInt32,
tags Tuple(String, UInt8)
) ENGINE = MergeTree()
ORDER BY id;
:) INSERT INTO t_tuple VALUES
(1, ('xiangwang', 22)),
(2, ('itechthink', 9)),
(3, ('javabook', 17)),
(4, ('AI', 24));
┌─id─┬─tags─────────────┐
│ 1 │ ('xiangwang',22) │
│ 2 │ ('itechthink',9) │
│ 3 │ ('javabook',17) │
│ 4 │ ('AI',24) │
└────┴──────────────────┘
:) SELECT id, tags.1 as name, tags.2 as age FROM t_tuple;
┌─id─┬─name───────┬─age─┐
│ 1 │ xiangwang │ 22 │
│ 2 │ itechthink │ 9 │
│ 3 │ javabook │ 17 │
│ 4 │ AI │ 24 │
└────┴────────────┴─────┘更新和删除元组。
-- 以下两条SQL等效
:) ALTER TABLE t_tuple UPDATE tags = ('javabook2', 0) WHERE id = 3;
:) UPDATE t_tuple SET tags = ('javabook2', 0) WHERE id = 3;
┌─id─┬─name───────┬─age─┐
│ 1 │ xiangwang │ 22 │
│ 2 │ itechthink │ 9 │
│ 3 │ javabook2 │ 0 │
│ 4 │ AI │ 24 │
└────┴────────────┴─────┘
-- 以下两条SQL等效
:) ALTER TABLE t_tuple DELETE WHERE id = 1;
:) DELETE FROM t_tuple WHERE id = 1;
┌─id─┬─name───────┬─age─┐
│ 2 │ itechthink │ 9 │
│ 3 │ javabook │ 17 │
│ 4 │ AI │ 24 │
└────┴────────────┴─────┘查看元组类型。
:) SELECT tuple(1, 'a') AS x, toTypeName(x);
┌─x───────┬─toTypeName(tuple(1, 'a'))─┐
│ (1,'a') │ Tuple(UInt8, String) │
└─────────┴───────────────────────────┘在动态创建元组时，Clickhouse会自动为元组的每一个参数赋予最小可表达的类型。
所谓最小可表达的类型，就是某一数据类型的大类中精度最低的那一类，例如整数中的UInt8。
:) SELECT tuple(1, NULL) AS x, toTypeName(x);
┌─x────────┬─toTypeName(tuple(1, NULL))──────┐
│ (1,NULL) │ Tuple(UInt8, Nullable(Nothing)) │
└──────────┴─────────────────────────────────┘8. 数组array(T)
数组是高阶函数使用得非常普遍的容器数据类型之二。
数组的标记是['array1', 'array1']——采用中括号[]表示。
array(T)可以是任意类型，也可以包含数组类型，但所有的元素都只能是同一种类型。
不推荐使用多维数组，因为Clickhouse对多维数组的支持有限，且不能在MergeTree引擎中存储多维数组。
可以使用array(T)或[]来创建数组。
:) SELECT array(1, 2) AS x, toTypeName(x);
┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8) │
└───────┴─────────────────────────┘
:) SELECT [1, 2] AS x, toTypeName(x);
┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8) │
└───────┴────────────────────┘Clickhouse会自动检测数组元素，并根据元素计算出存储这些元素最小的数据类型。反之，如果Clickhouse无法确定数据类型，它将产生异常。
:) SELECT array(1, 2, NULL) AS x, toTypeName(x);
┌─x──────────┬─toTypeName(array(1, 2, NULL))─┐
│ [1,2,NULL] │ Array(Nullable(UInt8)) │
└────────────┴───────────────────────────────┘
:) SELECT array(1, 'a')
Code: 386. DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString/Enum and some of them are not: In scope SELECT [1, 'a']. (NO_COMMON_TYPE) (version 24.5.1.1763 (official build))在表中定义数组类型并插入数据。
:) CREATE TABLE t_array
(
id UInt32,
params Array(UInt32)
) ENGINE = MergeTree()
ORDER BY id;
:) INSERT INTO t_array VALUES
(1, [1, 22]),
(2, [3, 17, 9]);
:) SELECT * FROM t_array;
┌─id─┬─params───┐
│ 1 │ [1,22] │
│ 2 │ [3,17,9] │
└────┴──────────┘通常会使用arrayJoin()函数展开Array类型的列（这个很常用，将数组值展开成多条记录，用于展示或者统计）。
:) SELECT id, arrayJoin(params) AS params FROM t_array;
┌─id─┬─params─┐
│ 1 │ 1 │
│ 1 │ 22 │
│ 2 │ 3 │
│ 2 │ 17 │
│ 2 │ 9 │
└────┴────────┘也会使用arrayMap()函数对Array类型的列进行映射。
:) SELECT id, arrayMap(x -> x * 2, params) AS params FROM t_array;
┌─id─┬─params────┐
│ 1 │ [2,44] │
│ 2 │ [6,34,18] │
└────┴───────────┘将params更新为新的数组内容。
:) ALTER TABLE t_array UPDATE params = [12, 34, 56] WHERE id = 1;
:) SELECT * FROM t_array;
┌─id─┬─params─────┐
│ 1 │ [12,34,56] │
│ 2 │ [3,17,9] │
└────┴────────────┘将id = 2中的params数组增加或减少元素（最简单的方式是头尾进行，处理任意位置非常麻烦）。
:) ALTER TABLE t_array UPDATE params = arrayPushFront(params, 0) WHERE id = 2;
┌─id─┬─params─────┐
│ 1 │ [12,34,56] │
│ 2 │ [0,3,17,9] │
└────┴────────────┘
:) ALTER TABLE t_array UPDATE params = arrayPopFront(params) WHERE id = 2;
┌─id─┬─params─────┐
│ 1 │ [12,34,56] │
│ 2 │ [3,17,9] │
└────┴────────────┘
:) ALTER TABLE t_array UPDATE params = arrayPushBack(params, 0) WHERE id = 2;
┌─id─┬─params─────┐
│ 1 │ [12,34,56] │
│ 2 │ [3,17,9,0] │
└────┴────────────┘
:) ALTER TABLE t_array UPDATE params = arrayPopBack(params) WHERE id = 2;
┌─id─┬─params─────┐
│ 1 │ [12,34,56] │
│ 2 │ [3,17,9] │
└────┴────────────┘对于多维数组，可以使用size(N-1)获得子数组的大小。其中N表述数组的维度，其维度按照数字大小从低到高。
:) CREATE TABLE t_array_multi
(
arr Array(Array(Array(UInt32)))
) ENGINE = MergeTree()
ORDER BY tuple();
:) INSERT INTO t_array_multi VALUES ([[[12, 13, 0, 1], [12]]]);
:) SELECT arr.size0, arr.size1, arr.size2 FROM t_array_multi;
┌─arr.size0─┬─arr.size1─┬─arr.size2─┐
│ 1 │ [2] │ [[4,1]] │
└───────────┴───────────┴───────────┘size0表示最外层数组，size1表示中间那一层，而size2表示最里层的数组。
最里层有两个数组，分别有4个元素和1个元素，所以size2是[[4,1]]。
9. 键值对Map(key, value)
Map是高阶函数使用得非常普遍的容器数据类型之三，它用来存储key:value键值对类型的数据。
Map的标记是{'key1':value1, 'key2':value2}——采用大括号{}表示。
key的类型可以是String, Integer, LowCardinality或者FixedString。value的类型可以是String, Integer, Array, LowCardinality或者FixedString。
从Map中获取数值非常简单，就像Python或Go那样（map['key']）。
:) CREATE TABLE t_map
(
id UInt32,
pairs Map(String, UInt64)
) ENGINE = MergeTree()
ORDER BY id;
:) INSERT INTO t_map VALUES
(1, {'key1' : 1, 'key2' : 10, 'key3' : 100}),
(2, {'key4' : 2, 'key5' : 20}),
(3, {'key6' : 3}),
(4, {});
┌─id─┬─pairs───────────────────────────┐
│ 1 │ {'key1':1,'key2':10,'key3':100} │
│ 2 │ {'key4':2,'key5':20} │
│ 3 │ {'key6':3} │
│ 4 │ {} │
└────┴─────────────────────────────────┘这里要注意，由于客户端的差异，导致两种写法会有差别。
第一种写法：
'key1':1。第二种写法：
'key1' : 1。
dbeaver客户端工具只支持第二种写法，第一种则会报错。
但Clickhouse-Client两种都可以正确保存数据。
Clickhouse暂时没有提供对Map的更新操作，但删除可以。
-- 以下两条SQL等效
:) ALTER TABLE t_map DELETE WHERE id = 1;
:) DELETE FROM t_map WHERE id = 1;
┌─id─┬─pairs───────────────────────────┐
│ 2 │ {'key4':2,'key5':20} │
│ 3 │ {'key6':3} │
│ 4 │ {} │
└────┴─────────────────────────────────┘返回特定的键值对。
:) SELECT pairs['key4'], pairs['key6'] FROM t_map;
┌─kv4─┬─kv6─┐
│ 2 │ 0 │
│ 0 │ 3 │
│ 0 │ 0 │
└─────┴─────┘可以单独使用Map中的key或者value，而不需要将整个列的数据都读取出来。
:) SELECT pairs.keys, pairs.values FROM t_map;
┌─pairs.keys──────┬─pairs.values─┐
│ ['key4','key5'] │ [2,20] │
│ ['key6'] │ [3] │
│ [] │ [] │
└─────────────────┴──────────────┘确定map是否包含key。
:) SELECT id, pairs FROM t_map WHERE mapContains(pairs, 'key5');
┌─id─┬─pairs────────────────┐
│ 2 │ {'key4':2,'key5':20} │
└────┴──────────────────────┘mapKeys等价于Map.keys，而mapValues等价于 Map.values。
:) SELECT pairs.keys, mapKeys(pairs) FROM t_map;
┌─pairs.keys──────┬─mapKeys(pairs)──┐
│ ['key4','key5'] │ ['key4','key5'] │
│ ['key6'] │ ['key6'] │
│ [] │ [] │
└─────────────────┴─────────────────┘
:) SELECT pairs.values, mapValues(pairs) FROM t_map;
┌─pairs.values─┬─mapValues(pairs)─┐
│ [2,20] │ [2,20] │
│ [3] │ [3] │
│ [] │ [] │
└──────────────┴──────────────────┘也可以把元组Tuple转换为Map。
:) SELECT CAST(([1, 2, 3], ['Ready', 'Steady', 'Go']), 'Map(UInt8, String)') AS map;
┌─map───────────────────────────┐
│ {1:'Ready',2:'Steady',3:'Go'} │
└───────────────────────────────┘也可以反过来把Map转换为元组Tuple。
:) SELECT tuple(*) AS myTuple
FROM (
SELECT ['key1', 'key2', 'key3'] AS keys, ['value1', 'value2', 'value3'] AS values
);
┌─myTuple───────────────────────────────────────────────┐
│ [["key1","key2","key3"],["value1","value2","value3"]] │
└───────────────────────────────────────────────────────┘10. 嵌套类型
嵌套的本质是在一张数据表里再保存另一张数据表，它是一个多维数组结构，例如产品和产品的SKU信息。
Clickhouse目前只支持一级嵌套。
:) CREATE TABLE t_nested
(
id UInt64,
name String,
sku Nested
(
model String,
price Decimal(16, 2)
)
) ENGINE = MergeTree()
ORDER BY id;
:) INSERT INTO t_nested VALUES
(1, 'iPhone', ['iPhone15', 'iPhone15 Pro', 'iPhone15 Pro Max'], [9000.00, 11000.00, 12000.00]),
(2, 'huawei', ['Mate 60 Pro', 'Mate 60 RS', 'Pura 70 Pro'], [8999.00, 11999.00, 7999.00]);
┌─id─┬─name───┬─sku.model──────────────────────────────────────┬─sku.price──────────┐
│ 1 │ iPhone │ ['iPhone15','iPhone15 Pro','iPhone15 Pro Max'] │ [9000,11000,12000] │
│ 2 │ huawei │ ['Mate 60 Pro','Mate 60 RS','Pura 70 Pro'] │ [8999,11999,7999] │
└────┴────────┴────────────────────────────────────────────────┴────────────────────┘在嵌套类型上使用数组函数。
:) SELECT id, arrayReduce('avg', sku.price)
FROM t_nested;
┌─id─┬─arrayReduce('avg', sku.price)─┐
│ 1 │ 10666.666666666666 │
│ 2 │ 9665.666666666666 │
└────┴───────────────────────────────┘感谢支持
更多内容，请移步《超级个体》。