1. 数据类型介绍
ClickHouse 可以在数据表中存储多种数据类型。
本节描述 ClickHouse 支持的数据类型,以及使用或者实现它们时(如果有的话)的注意事项。
你可以在系统表 system.data_type_families 中检查数据类型名称是否区分大小写。
2. 数值类型
数值类型分为整数、浮点数和定点数三类
2.1 整数
ClickHouse 中使用Int8、Int16、Int32 和 Int64 指代 4 种大小的 Int 类型,其末尾的数字表明占用字节的大小(8位=1字节)
ClickHouse 支持无符号的整数,使用前缀 U 表示
固定长度的整型,包括有符号整型或无符号整型
整型范围:( -2^n-1 到 (2^n-1) -1 )
| 类型 | 字节 | 取值范围 |
|---|---|---|
| Int8 | 1 | [-128 : 127] |
| Int16 | 2 | [-32768 : 32767] |
| Int32 | 4 | [-2147483648 : 2147483647] |
| Int64 | 8 | [-9223372036854775808 : 9223372036854775807] |
无符号整型范围( 0 到 (2^n-1) -1):
| 类型 | 字节 | 取值范围 |
|---|---|---|
| UInt8 | 1 | [0 : 255] |
| UInt16 | 2 | [0 : 65535] |
| UInt32 | 4 | [0 : 4294967295] |
| UInt64 | 8 | [0 : 18446744073709551615] |
2.2 浮点型
- ClickHouse 直接使用 Float32 和 Float64 代表单精度浮点数以及双精度浮点数
- ClickHouse 的浮点类型有两种值:
| 名称 | 字节 | 有效精度(位数) | 普遍概念 |
|---|---|---|---|
| Float32 | 4 | 7 | float |
| Float64 | 8 | 16 | double |
建议尽可能以整数形式存储数据。
例如,将固定精度的数字转换为整数值,或页面加载时间用毫秒为单位表示
2.2.1 使用浮点数
浮点型进行计算时可能引起四舍五入的误差
计算的结果取决于计算方法(计算机系统的处理器类型和体系结构)
浮点计算结果可能是诸如无穷大(
INF)和«非数字»(NaN)。对浮点数计算的时候应该考虑到这点。当一行行阅读浮点数的时候,浮点数的结果可能不是机器最近显示的数值。
1 | select 1 - 0.9; |
**注意:**在使用浮点数的时候,应当要意识到它是有限精度的。
假如,分别对 Float32 和 Float64 写入超过有效精度的20 位小数的数值,下面我们看看会发生什么。
1 | -- 使用Float32 |
可以发现两种类型都产生了数据溢出。,Float32 从小数点后第 8 位起数据溢出, Float64 从小数点后第 17 位起数据溢出。
2.2.2 NaN 和 Inf
与标准 SQL 相比,ClickHouse 支持以下类别的浮点数:
Inf - 正无穷:
1 | select 1 / 0; |
-Inf - 负无穷:
1 |
|
NaN - 非数字:
1 | select 0 / 0; |
2.3 定点数
ClickHouse 提供了Decimal32、Decimal64 和 Decimal128 三种精度的定点数。
定点数适用于高精度的数值运算。
可以通过两种形式声明定点:
简写方式: Decimal32(S)、Decimal64(S)、Decimal128(S)
原生方式:Decimal(P, S)
- P代表精度,决定总位数(整数部分+小数部分),取值范围是1~38;
- S代表规模,决定小数位数,取值范围是0~P。
2.3.1 简写与原生方式对应
| 简写方式 | 原生方式 | 范围 |
|---|---|---|
| Decimal32(S) | Decimal(1~9, S) | -1 * 10^(9-S) 到 1 * 10^(9-S) |
| Decimal64(S) | Decimal(10~18, S) | -1 * 10^(18-S) 到 1 * 10^(18-S) |
| Decimal128(S) | Decimal(19~38, S) | -1 * 10^(38-S) 到 1 * 10^(38-S) |
2.3.2 不同经度混合计算
- 在使用两个不同精度的定点数进行四则运算的时候,它们的小数点位数 S 会发生变化。
- 在进行加法运算时,S 取最大值。例如下面的查询,toDecimal64(2,4) 与 toDecimal32(2,2) 相加后 S=4:
1 | -- Decimal64 类型数据 |
注意 Decimal 进行加减乘除四则运算的时候的精度问题!总结一下是:
| 名称 | 规则 |
|---|---|
| 加法 | S = max(S1, S2) |
| 减法 | S = max(S1, S2) |
| 乘法 | S = S1 + S2(S1范围 >= S2范围) |
| 除法 | S = S1(S1为被除数,S1/S2) |
2.3.3 溢出检查
在对 Decimal 类型执行操作时,数值可能会发生溢出。
检查溢出会导致计算变慢。
分数中的过多数字被丢弃(不是舍入的)。整数中的过多数字将导致异常。
如果已知溢出不可能,则可以通过设置
decimal_check_overflow来禁用溢出检查。禁用溢出检查时,如果出现溢出将导致结果不正确
1 | SELECT toDecimal32(2, 4) AS x, x / 3 as y,toTypeName(y) as dataType; |
2.3.4 内部表示方式
- 数据采用与自身位宽相同的有符号整数存储。
- 这个数在内存中实际范围会高于上述范围,从 String 转换到十进制数的时候会做对应的检查。
- 由于现代CPU不支持128位数字,因此 Decimal128 上的操作由软件模拟,所以 Decimal128 的运算速度明显慢于 Decimal32/Decimal64。
3. 布尔型
ClickHouse中没有单独的类型来存储布尔值
可以使用 UInt8 类型,取值限制为 0 或 1
在 ClickHouse 使用过程中,做比较得到的结果都是 1 或者 0,而不是通常意义上的 True 或者 False
1 | -- 当结果为 true 时返回 1 |
4. 字符串
ClickHouse 中字符串类型可以细分为 String、FixedString 和 UUID 三类。
4.1 String
字符串由 String 定义,长度不限,可以是任意长度,因此在使用 String 的时候无须声明大小
String 可以包含任意的字节集,包含空字节
String 完全代替了传统意义上数据库的Varchar、Text、Clob 和 Blob 等字符类型
字符编码
- ClickHouse 没有编码的概念。
- 字符串可以是任意的字节集,按它们原本的方式进行存储和输出。
- 但是在存储文本时建议使用统一的字符编码集(如 UTF-8 编码),这样读写就不需要进行任何的转换了。
- 对不同的编码文本 ClickHouse 会有不同处理字符串的函数。
- 比如,
length函数可以计算字符串包含的字节数组的长度, - 比如,
lengthUTF8函数是假设字符串以 UTF-8 编码,计算的是字符串包含的 Unicode 字符的长度。
- 比如,
4.2 FixedString
FixedString 类型和传统意义上的 Char 类型有些类似,对于一些字符有明确长度的场合,可以使用固定
长度的字符串。定义 FixedString(N) 中的固定长度 N 的字符串(字节数为 N),且N 必须是严格的自然数。
与 Char 不同的是,FixedString 使用 null字节填充末尾字符,而 Char 通常使用空格填充。
当数据的长度恰好为N个字节时,
FixedString类型是最高效的。当数据长度未达到 N 时效率会降低。
当向FixedString(N) 类型的字段插入数据字节
小于N 时,将对字符串末尾进行空字节填充。当向FixedString(N) 类型的字段插入数据字节
大于N 时,将抛出Too large value for FixedString(N)异常。FixedString(N)的长度是个常量。仅由空字符组成的字符串,函数length返回值为N,而函数empty的返回值为1。当做数据查询时,ClickHouse不会删除字符串末尾的空字节,但是使用 where 过滤数据时不需要手动添加空字节。
- N是最大字节数(Byte),不是字符长度,如果是UTF8字符串,那么就会占用3字节,GBK会占用2字节。
- 当内容少于N,数据库会自动在右填充空字节(null byte),当内容大于N时候,会抛出错误。
- 当写入内容后面后空字节,系统不会自动去裁剪,查询的时候也会被输出。
- FixedString(N) 比 String 支持更少的方法。
2.4.1 存储类型
可以有效存储在FixedString类型的列中的值的示例:
- 二进制表示的IP地址(IPv6使用
FixedString(16)) - 语言代码(ru_RU, en_US … )
- 货币代码(USD, RUB … )
- 二进制表示的哈希值(MD5使用
FixedString(16),SHA256使用FixedString(32))
4.2.2 使用示例
1 | -- 查询数据 |
4.3 UUID
- UUID 即通用唯一标识符是一个16字节的数字,用于标识记录。
- UUID 是一种数据库常见的主键类型,在 ClickHouse 中直接把它作为一种数据类型。
- UUID 共有 32位,它的格式为8-4-4-4-12。
- UUID 默认值是全部零填充,即
00000000-0000-0000-0000-000000000000 - UUID 只支持字符串数据类型支持的函数(比如, min, max, 和 count)。
- UUID 不支持算术运算(例如, abs)或聚合函数,例如 sum和 avg).
- 要生成UUID值,ClickHouse提供了
generateuidv4函数。
1 | -- 生成一个 UUID |
5. 日期时间类型
时间类型分为 DateTime、DateTime64 和 Date 三类。ClickHouse 目前没有时间戳类型。时间类型最
高的精度是秒,也就是说,如果需要处理毫秒、微秒等大于秒分辨率的时间,则只能借助 UInt 类型实
现。
1 | Date: 2020-02-02 |
5.1 Date类型
- Date 日期类型,用两个字节存储,表示从 1970-01-01 (无符号) 到当前的日期值。
- 允许存储从 Unix 纪元开始到编译阶段定义的上限阈值常量(目前上限是2106年,但最终完全支持的年份为2105)。
- Date类型不包含具体的时间信息,只精确到天,它支持字符串形式写入。
- 最小值输出为1970-01-01。
- 日期中没有存储时区信息。
- 默认情况下,客户端连接到服务的时候会使用服务端时区。
- 可以通过启用客户端命令行选项 –use_client_time_zone 来设置使用客户端时间。
1 | -- 建表 |
5.2 DateTime类型
- DateTime 时间戳类型,用四个字节存储 Unix 时间戳
- DateTime 类型包含时、分、秒信息,精确到秒,支持使用字符串形式写入
- 允许存储与日期类型相同的范围内的值。最小值为 1970-01-01 00:00:00
- 因为是Unsigned的整形,因此不能支持1970年1月1日(UTC/GMT的午夜)以前的时间。
- 时区会影响输入和输出。请使–use_client_time_zone 进行切换时区,服务端启动时候最好使用 TZ=X
来保证时区 - 默认情况下,客户端连接到服务的时候会使用服务端时区
1 | -- 建表 |
5.3 DateTime64类型
DateTime64 类型允许以日期(date)加时间(time)的形式来存储一个时刻的时间值,具有定义的亚秒精度。
DateTime64 类型以Int64类型将数据存储为自Linux纪元开始(1970-01-01 00:00:00UTC)的时间刻度数(ticks)。
定义方式:
DateTime64(precision, [timezone])时间精度由 precision 参数确定。此外,
DateTime64类型可以像存储其他数据列一样存储时区信息,时区会影响DateTime64类型的值如何以文本格式显示,以及如何解析以字符串形式指定的时间数据 (‘2020-01-01 05:00:01.000’)。时区不存储在表的行中(也不在resultset中),而是存储在列的元数据中。
当向DateTime64 类型的列中插入 integer类型的数值时,会被视为适当缩放的Unix时间戳(UTC)。
当向DateTime64 类型的列中插入字符串类型的时间时,它会被赋予时区信息。
向 DateTime64 类型列写入数据
1 | -- 建表 |
1546300800000(精度为3)表示'2019-01-01 00:00:00'UTC. 不过,因为timestamp列指定了Asia/Shanghai(UTC+8)的时区,当作为字符串输出时,它将显示为'2019-01-01 08:00:00''2019-01-01 00:00:00'将被认为处于Asia/Shanghai时区并被存储为1546300800000.DateTime64设置精度示例
1 | -- 建表 |
5.4 INTERVAL
- Interval 是间隔的类型,表示时间和日期间隔的数据类型
- Interval 数据类型值不能存储在表中
- Interval 指定时间间隔的值应为无符号整数值
5.4.1 时间间隔类型
| 类型 | 说明 |
|---|---|
| SECOND | 秒 |
| MINUTE | 分 |
| HOUR | 时 |
| DAY | 天 |
| WEEK | 周 |
| MONTH | 月 |
| QUARTER | 刻 |
| YEAR | 年 |
5.4.2 使用示例
- 对于每个间隔类型,都有一个单独的数据类型。 例如,
DAY间隔对应于IntervalDay数据类型 - 您可以使用
Interval-在算术运算类型值 日期 和 日期时间-类型值。 例如,您可以将4天添加到当前时间 - 不同类型的间隔不能合并。可以使用小于等于间隔最小单位指定间隔,例如,一天零一小时,可以用
25 HOUR表示,而不能使用1 DAY 1 HOUR - 时间间隔不能做计算,但是可以使用日期时间类型数据分别与时间间隔类型进行计算
1 | -- 查看间隔类型 |
6. 类型对应关系
ClickHouse 中基础类型只有数值、字符串和时间三种类型,没有 Boolean 类型,但可以使用整型的 0 或 1 替代。
ClickHouse 的数据类型和常见的其他存储系统的数据类型对比:
| MySQL | Hive | CLickHouse(区分大小写) |
|---|---|---|
| byte | tinyint | Int8 |
| short | smallint | Int16 |
| int | int | Int32 |
| long | bigint | Int64 |
| varchar | string | String |
| timestamp | timestamp | DateTime |
| float | float | Float32 |
| double | double | Float64 |
| boolean | boolean | 无 |