ClickHouse 的数据类型都有哪些,和关系型数据库之间又有什么区别呢?
作者:@万明珠
喜欢这篇文章的话,就点个关注吧,会持续分享高质量Python文章,以及其它相关内容。
楔子
作为一款分析型数据库,ClickHouse 提供了许多数据类型,它们可以划分为基础类型、复合类型和特殊类型。其中基础类型使 ClickHouse 具备了描述数据的基本能力,而另外两种类型则使 ClickHouse 的数据表达能力更加丰富和立体。
下面就来分门别类的介绍一下。
基础类型
基础类型只有数值、字符串和时间三种类型,注:准确来说还有布尔类型(Bool),但一般都用整型(UInt8)表示布尔类型,1 为真 0 为假。
数值类型
数值类型分为整数、浮点数和 Decimal 三类。
Int
在普遍观念中,常用 Tinyint、Smallint、Int 和 Bigint 指代整数的不同取值范围,而 ClickHouse 则直接使用 Int8、Int16、Int32、Int64 来指代 4 种大小的 Int 类型,其末尾的数字表示该类型的整数占多少位。可以认为:Int8 等价于 Tinyint、Int16 等价于 Smallint、Int32 等价于 Int、Int64 等价于 Bigint。
ClickHouse 也支持无符号整数,使用前缀 U 表示,比如:UInt8、UInt16、UInt32、UInt64。
Float
与整数类似,ClickHouse 直接使用 Float32 和 Float64 代表单精度浮点数和双精度浮点数,可以看成是 FLOAT 和 DOUBLE。另外 ClickHouse 的浮点数也支持正无穷、负无穷以及非数字的表达方式。
satori :) select 1 / 0, -1 / 0, 0 / 0
SELECT
1 / 0,
-1 / 0,
0 / 0
Query id: e3b3712c-0506-4b3b-b2d8-7f936c548740
┌─divide(1, 0)─┬─divide(-1, 0)─┬─divide(0, 0)─┐
│ inf │ -inf │ nan │
└──────────────┴───────────────┴──────────────┘
1 rows in set. Elapsed: 0.002 sec.
satori :)
Decimal
如果要求高精度的数值运算,则需要使用 Decimal、即定点数(类似于浮点数),ClickHouse 提供了 Decimal32、Decimal64 和 Decimal128 三种精度的Decimal。在定义表字段的类型时,可以通过两种形式声明。
- 简写方式:Decimal32(S)、Decimal64(S)、Decimal128(S);
- 原生方式:Decimal(P, S),表示该定点数的整数位加上小数位的总长度最大为 P,其中小数位长度最多为 S;
Decimal32 的 P 为 10、Decimal64 的 P 为 19、Decimal128 的 P 为 39。比如某个字段类型是 Decimal32(3),那么表示该字段存储的定点数,其整数位加上小数位的总长度不超过 10,其中小数部分如果超过 3 位则只保留 3 位。
而在 SQL 中我们可以通过 toDecimal32 或 toDecimal64 将一个整数或浮点数转成定点数,比如:toDecimal32(2, 5) 得到的结果就是 2.00000。另外使用两个不同精度的 Decimal 进行四则运算的时候,它们的小数点位数 S 会发生变化,规则如下:
在进行加法和减法运算时,S 取最大值。
satori :) select toDecimal32(22, 3) + toDecimal32(33, 2)
SELECT toDecimal32(22, 3) + toDecimal32(33, 2)
Query id: a223565d-e6ba-4db9-aa7d-1cae37424128
┌─plus(toDecimal32(22, 3), toDecimal32(33, 2))─┐
│ 55.000 │
└──────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
satori :)
在进行乘法运算时,S 取两者之和:
satori :) select toDecimal32(22, 3) * toDecimal32(33, 2)
SELECT toDecimal32(22, 3) * toDecimal32(33, 2)
Query id: 0f1bd695-9f37-43b4-a6d1-b2a62a1dd6c3
┌─multiply(toDecimal32(22, 3), toDecimal32(33, 2))─┐
│ 726.00000 │
└──────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
satori :)
在进行除法运算时,S 取被除数的值,此时要求被除数的 S 必须大于除数 S,否则报错。
satori :) select toDecimal64(6, 3) / toDecimal64(3, 2)
SELECT toDecimal64(6, 3) / toDecimal64(3, 2)
Query id: 55b4a58f-b722-4487-8391-2c08e6c764ca
┌─divide(toDecimal64(6, 3), toDecimal64(3, 2))─┐
│ 2.000 │
└──────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
satori :) select toDecimal64(6, 3) / toDecimal64(3, 4) -- 这里会报错,因为被除数的 S 小于除数的 S
SELECT toDecimal64(6, 3) / toDecimal64(3, 4)
Query id: 6f782411-0d15-4ab8-adab-be19c9f6b0e2
0 rows in set. Elapsed: 0.003 sec.
Received exception from server (version 21.7.3):
Code: 69. DB::Exception: Received from localhost:9000. DB::Exception:
Decimal result's scale is less than argument's one: While processing toDecimal64(6, 3) / toDecimal64(3, 4).
satori :)
另外还有一点需要注意:由于现代计算机系统只支持 32 位或 64 位,所以 Decimal128 是在软件层面模拟出来的,它的速度会比 Decimal32、Decimal64 要慢。
字符串类型
字符串类型可以细分为 String、FixedString 和 UUID 三类,从命名来看仿佛不像是一款数据库提供的类型,反倒像一门编程语言的设计。
String
字符串由 String 定义,长度不限,因为在使用 String 的时候无需声明大小。它完全代替了传统意义上的 Varchar、Text、Clob 和 Blob 等字符类型。String 类型不限定字符集,因为它根本没有这个概念,所以可以将任意编码的字符串存入其中。但为了程序的规范性和可维护性,在同一套程序中应使用统一的编码,比如 UTF-8。
FixedString
FixedString 类型和传统意义上的 Char 类型有些类似,对于一些有着明确长度的场合,可以使用 FixedString(N) 来声明固定长度的字符串。但与 char 不同的是,FixedString 使用 NULL 字节来填充末尾字符,而 Char 通常使用空格填充。
可以使用 toFixedString 生成 FixedString。
satori :) select toFixedString('satori', 7), length(toFixedString('satori', 7))
SELECT
toFixedString('satori', 7),
length(toFixedString('satori', 7))
Query id: 45fe6e26-0540-40de-9eaf-aeefd12a3a1b
┌─toFixedString('satori', 7)─┬─length(toFixedString('satori', 7))─┐
│ satori │ 7 │
└────────────────────────────┴────────────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
satori :)
UUID
UUID 是一种数据库常见的主键类型,而在 ClickHouse 中直接把它作为一种数据类型。UUID 共有 32 位,它的格式为 8-4-4-4-12。如果一个 UUID 类型的字段在写入数据的时候没有被赋值,那么它会按照相应格式用 0 填充。
时间类型
时间类型分为 DateTime、DateTime64 和 Date三类。
DateTime
DateTime 类型包含年、月、日、时、分、秒信息,精确到秒,支持使用字符串的方式写入;
DateTime64
DateTime64 可以记录亚秒,它在 DateTime 之上增加了精度的设置。举个栗子:DateTime64 类型的时间可以是 2018-01-01 12:12:32.22;但如果是 DateTime 的话,则是 2018-01-01 12:12:32,也就是说最后的 .22 没了。
Date
Date 类型不包含具体的时间信息,只精确到天,并且和 DateTime、DateTime64 一样,支持字符串写入。
复合类型
除了基础数据类型之外,ClickHouse 还提供了数组、元组、枚举和嵌套,总共四种复合类型。这些类型通常都是其它数据库原生不具备的特性,拥有了复合类型之后,ClickHouse 的数据模型表达能力就更强了。
Array
数组有两种定义形式,常规方式 Array(T),比如某个字段是包含 UInt8 的数组,那么就可以声明为 Array(UInt8);需要说明的是,ClickHouse 中的类型是区分大小写的,比如这里的 Array 就不可以写成 array,UInt8 不可以写成 uint8。
当然在查询的时候,我们可以通过 array 函数创建一个数组。注意:ClickHouse 中的绝大部分函数也是区分大小写的,只要是你在其它关系型数据库中没有见过的函数,基本上都区分大小写。
satori :) select array(1, 2) as a, toTypeName(a) -- toTypeName 表示获取字段的类型
SELECT
[1, 2] AS a,
toTypeName(a)
Query id: b22e371f-d5fc-4245-b299-a79a344fc7ea
┌─a─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8) │
└───────┴─────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
satori :)
这里只是演示,关于具体的语法后面说。
在查询的时候简写成 [v1, v2, v3, ...] 也是可以的。
satori :) select [1, 2] as a, toTypeName(a)
SELECT
[1, 2] AS a,
toTypeName(a)
Query id: 50d4e367-cd1b-4826-bca8-eb913e6fbaeb
┌─a─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8) │
└───────┴────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
satori :)
从上述的例子中可以发现,在查询时并不需要主动声明数组的元素类型,因为 ClickHouse 的数组拥有类型推断的能力,推断的依据是:以最小存储代价为原则,即使用最小可表达的数据类型。比如 array(1, 2) 会使用 UInt8 作为数组类型,但如果数组中存在 NULL 值,元素类型将变为 Nullable(T)。
satori :) select [1, 2, NULL] as a, toTypeName(a)
SELECT
[1, 2, NULL] AS a,
toTypeName(a)
Query id: 95eb5bfa-5008-4bd0-ac94-bd2e39de6485
┌─a──────────┬─toTypeName([1, 2, NULL])─┐
│ [1,2,NULL] │ Array(Nullable(UInt8)) │
└────────────┴──────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
satori :)
数组里面的元素可以有多种,但前提是它们必须能够兼容,比如:[1, 3.14] 可以,但 [1, 'ABC'] 则不行。而在定义表字段的时候,如果使用 Array 类型,则需要指定明确的元素类型,比如:
CREATE TABLE table_name (
arr Array(String) -- 指定明确类型
) ENGINE = Memory
Tuple
元组由 1 ~ n 个元素组成,每个元素之间允许设置不同的数据类型,且彼此之间不要求兼容。元组同样支持类型推断,其推断依据仍然是以最小存储代价为原则。与数组类似,在 SQL 中我们可以通过 Tuple(T) 来定义元组,可以使用 tuple 函数在查询时创建元组。
satori :) SELECT tuple(1, 'a', now()) as a, (1, 3, '666') as b
SELECT
(1, 'a', now()) AS a,
(1, 3, '666') AS b
Query id: ab97ea39-accd-4eaa-85b0-c1728fde57e4
┌─a─────────────────────────────┬─b───────────┐
│ (1,'a','2021-08-05 01:10:07') │ (1,3,'666') │
└───────────────────────────────┴─────────────┘
1 rows in set. Elapsed: 0.002 sec.
satori :)
关于数组和元组的区别,熟悉 Python 的话应该很清楚,答案是元组不可变。在定义表字段时,元组也需要指定明确的元素类型。
CREATE TABLE table_name (
-- 可以指定多个类型
-- 但是注意:Tuple(String, Int8) 表示 tpl 字段的值只能是含有两个元素的元组
-- 并且第一个元素为 String,第二个元素为 Int8
tpl Tuple(String, Int8)
) ENGINE = Memory
而在数据写入的过程中会进行类型检查,比如写入 ('abc', 123) 是可行的,但写入 ('abc', 'def') 则报错。
Enum
ClickHouse 支持枚举类型,这是一种在定义常量时经常使用的数据类型。ClickHouse 提供了 Enum8 和 Enum16 两种枚举类型,它们之间除了取值范围不同之外,别无二致。枚举固定使用 (String:Int) 键值对的形式定义数据,所以 Enum8 和 Enum16 分别会对应 (String:Int8) 和 (String:Int16),例如:
CREATE TABLE table_name(
e Enum('ready'=1, 'start'=2, 'success'=3, 'error'=4)
) ENGINE = Memory
在定义枚举集合的时候,有几点需要注意。首先 Key 和 Value 是不允许重复的,要保证唯一性。其次 Key 和 Value 的值都不能为 Null,但 Key 允许为空字符串。在写入枚举数据的时候,只会用到 Key 字符串部分,例如:
INSERT INTO table_name VALUES('ready')
另外在数据写入的时候,会对照枚举集合项的内容进行逐一检查,如果 Key 字符串不在集合范围内则抛出异常,比如执行下面的语句就会报错:
INSERT INTO table_name VALUES('abc') -- 会报错
可能有人觉得,完全可以使用 String 代替枚举,为什么还需要专门实现枚举类型呢?答案是出于对性能的考虑。因为虽然枚举中定义的 Key 属于 String 类型,但是在后续对枚举的所有操作中(包括排序、分组、去重、过滤等),会使用 Int 类型的 Value 值。
Nested
嵌套类型,顾名思义是一种嵌套表结构。一张数据表,可以定义任意多个嵌套类型字段,但每个字段的嵌套层级只支持一级,即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系,使用嵌套类型也是一种不错的选择。例如我们下面创建一张表 nested_test,具体的建表逻辑后面会说,当然本身也不是特别难的东西。
CREATE TABLE nested_test (
name String,
age UInt8,
dept Nested(
id UInt32,
name String
)
) ENGINE = Memory;
ClickHouse 的嵌套类型和传统的嵌套类型不同,导致在初次接触它的时候会让人十分困惑。以上面这张表为例,如果按照它的字面意思来理解,会很容易理解成 nested_test 与 dept 是一对一的包含关系,其实这是错误的。不信可以执行下面的语句,看看会是什么结果:
我们看到报错了,现在大家应该明白了,嵌套类型本质是一种多维数组的结构。嵌套表中的每个字段都是一个数组,并且行与行之间数组的长度无须对齐,所以需要把刚才的 INSERT 语句调整成下面的形式:
INSERT INTO nested_test VALUES('nana', 20, [10000, 10001, 10002], ['唐辛子', 'ななかぐら', 'ゴウマ']);
-- 行与行之间,数组长度无需对齐。
INSERT INTO nested_test VALUES('nana', 20, [10000, 10001], ['唐辛子', 'ななかぐら']);
需要注意的是,在同一行中每个数组字段的长度必须相等。例如在下面的示例中,由于行内数组字段的长度没有对齐,所以会抛出异常:
提示我们长度不一样。
然后在访问嵌套类型的数据时需要使用点符号,例如:
特殊类型
ClickHouse 还有一类不同寻常的数据类型,这里将它们定义为特殊类型。
Nullable
准确来说,Nullable 并不能算是一种独立的数据类型,它更像是一种辅助的修饰符,需要与基础数据类型一起搭配使用。Nullable 类型与 Python 类型注解里面的 Optional 有些相似,它表示某个基础数据类型可以是 NULL 值。其具体用法如下所示:
CREATE TABLE null_test (
col1 String,
col2 Nullable(UInt8)
) ENGINE = Memory
通过 Nullable 修饰后 col2 字段可以被写入 NULL 值:
INSERT INTO null_test VALUES ('nana', NULL);
在使用 Nullable 类型的时候还有两点值得注意:首先,它只能和基础类型搭配使用,不能用于数组和元组这些复合类型,也不能作为索引字段;其次,应该慎用 Nullable 类型,包括 Nullable 的数据表,不然会使查询和写入性能变慢。因为在正常情况下,每个列字段的数据会被存储在对应的 [Column].bin 文件中,如果一个列字段被 Nullable 类型修饰后,会额外生成一个 [Column].null.bin 文件专门保存它的 NULL 值。这意味着在读取和写入数据时,需要一倍的额外文件操作。
Domain
域名类型分为 IPv4 和 IPv6 两类,本质上它们是对整型和字符串的进一步封装。IPv4 类型是基于 UInt32 封装的,它的具体用法如下所示:
CREATE TABLE ip4_test (
url String,
ip IPv4
) ENGINE = Memory;
INSERT INTO ip4_test VALUES ('www.nana.com', '127.0.0.1');
细心的人可能会问,直接使用字符串不就行了吗?为何多此一举呢?至少有如下两个原因:
1)出于便捷性的考量,例如 IPv4 类型支持格式检查,格式错误的 IP 数据是无法被写入的,例如:
INSERT INTO ip4_test VALUES('www,nana.com', '192.0.0')
Exception on client:
Code: 441. DB::Exception: Invalid IPv4 value.: data for INSERT was parsed from query
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 21.7.3 revision 54449.
2)出于性能的考量,同样以 IPv4 为例,IPv4 使用 UInt32 存储(每个部分占 8 位),相比 String 更加紧凑,占用的空间更小,查询性能更快。IPv6 类型是基于 FixedString(16) 封装的,它的使用方法与 IPv4 别无二致,此处不再赘述。
在使用 Domain 类型的时候还有一点需要注意,虽然它从表面上看起来与 String 一样,但 Domain 类型并不是字符串,所以它不支持隐式的自动类型转换。如果需要返回 IP 的字符串形式,则需要显式调用 IPv4NumToString 或 IPv6NumToString 函数进行转换。
如果觉得文章对您有所帮助,可以请囊中羞涩的作者喝杯柠檬水,万分感谢,愿每一个来到这里的人都生活愉快,幸福美满。
微信赞赏
支付宝赞赏
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器