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）。对浮点数计算的时候应该考虑到这点。当一行行阅读浮点数的时候，浮点数的结果可能不是机器最近显示的数值。
select 1 - 0.9;
┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘注意：在使用浮点数的时候，应当要意识到它是有限精度的。
假如，分别对 Float32 和 Float64 写入超过有效精度的20 位小数的数值，下面我们看看会发生什么。
-- 使用Float32
select toFloat32('0.1234567901234567890123456789') as value,toTypeName(value) as dataType;
┌──────value─┬─dataType─┐
│ 0.12345679 │ Float32 │
└────────────┴──────────┘
-- 使用Float64
select toFloat64('0.1234567901234567890123456789') as value,toTypeName(value) as dataType;
┌───────────────value─┬─dataType─┐
│ 0.12345679012345678 │ Float64 │
└─────────────────────┴──────────┘
可以发现两种类型都产生了数据溢出。，Float32 从小数点后第 8 位起数据溢出， Float64 从小数点后第 17 位起数据溢出。
2.2.2 NaN 和 Inf
与标准 SQL 相比，ClickHouse 支持以下类别的浮点数：
Inf - 正无穷：
select 1 / 0;
┌─divide(1, 0)─┐
│ inf │
└──────────────┘
-Inf - 负无穷：
select -1 / 0;
┌─divide(-1, 0)─┐
│ -inf │
└───────────────┘
NaN - 非数字：
select 0 / 0;
┌─divide(0, 0)─┐
│ nan │
└──────────────┘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：
-- Decimal64 类型数据
select toDecimal64(2, 4) as value ,toTypeName(value) as dataType;
┌──value─┬─dataType───────┐
│ 2.0000 │ Decimal(18, 4) │
└────────┴────────────────┘
-- Decimal32 类型数据
select toDecimal32(2, 4) as value,toTypeName(value) as dataType;
┌──value─┬─dataType──────┐
│ 2.0000 │ Decimal(9, 4) │
└────────┴───────────────┘
-- Decimal64 + Decimal32
select toDecimal64(2, 4) + toDecimal32(2, 2) as value , toTypeName(value) as dataType;
┌──value─┬─dataType───────┐
│ 4.0000 │ Decimal(18, 4) │
└────────┴────────────────┘注意 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来禁用溢出检查。禁用溢出检查时，如果出现溢出将导致结果不正确
SELECT toDecimal32(2, 4) AS x, x / 3 as y,toTypeName(y) as dataType;
┌──────x─┬──────y─┬─dataType──────┐
│ 2.0000 │ 0.6666 │ Decimal(9, 4) │
└────────┴────────┴───────────────┘
-- 制造溢出 1
SELECT toDecimal32(4.2, 8) AS x, 6 * x
Received exception from server (version 21.4.3):
Code: 407. DB::Exception: Received from localhost:9977. DB::Exception: Decimal math overflow: While processing toDecimal32(4.2, 8) AS x, 6 * x.
-- 关闭溢出检查
SET decimal_check_overflow = 0;
-- 关闭溢出检查后，如果出现溢出将导致结果不正确
SELECT toDecimal32(4.2, 8) AS x, 6 * x;
┌──────────x─┬─multiply(6, toDecimal32(4.2, 8))─┐
│ 4.20000000 │ -17.74967296 │
└────────────┴──────────────────────────────────┘2.3.4 内部表示方式
- 数据采用与自身位宽相同的有符号整数存储。
- 这个数在内存中实际范围会高于上述范围，从 String 转换到十进制数的时候会做对应的检查。
- 由于现代CPU不支持128位数字，因此 Decimal128 上的操作由软件模拟，所以 Decimal128 的运算速度明显慢于 Decimal32/Decimal64。
3. 布尔型
ClickHouse中没有单独的类型来存储布尔值
可以使用 UInt8 类型，取值限制为 0 或 1
在 ClickHouse 使用过程中，做比较得到的结果都是 1 或者 0，而不是通常意义上的 True 或者 False
-- 当结果为 true 时返回 1
select 1 == 1 as value,toTypeName(value) as dataType;
┌─value─┬─dataType─┐
│ 1 │ UInt8 │
└───────┴──────────┘
-- 当结果为 false 时返回 0
select 1 == 2 as value,toTypeName(value) as dataType;
┌─value─┬─dataType─┐
│ 0 │ UInt8 │
└───────┴──────────┘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 使用示例
-- 查询数据
SELECT toFixedString('abc', 5) value, LENGTH(value)AS LENGTH;
-- ‘abc’ 虽然只有 3 位，但长度却是5，因为末尾有2位空字符填充
┌─value─┬─LENGTH─┐
│ abc │ 5 │
└───────┴────────┘
-- 创建表
create table FixedStringTest(col FixedString(2)) engine = TinyLog;
-- 查看表是否创建成功
show tables;
┌─name────────────┐
│ FixedStringTest │
└─────────────────┘
-- 插入数据
insert into FixedStringTest values('a'),('b'),('c');
-- 查询数据
select * from FixedStringTest;
┌─col─┐
│ a │
│ b │
│ c │
└─────┘
-- 使用 where 过滤数据
select col,LENGTH(col) AS LENGTH from FixedStringTest where col = 'b';
┌─col─┬─LENGTH─┐
│ b │ 2 │
└─────┴────────┘
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函数。
-- 生成一个 UUID
select generateUUIDv4() as uuid;
┌─────────────────────────────────uuid─┐
│ f4ebd8f8-9990-4a12-9e1b-80aa6b823327 │
└──────────────────────────────────────┘
-- 测试 UUID 默认值
create table uuid_test(
c1 UUID,
c2 String
) engine = Memory;
-- 插入第一行数据
insert into uuid_test select generateUUIDv4(), 'v1';
-- 插入第二行数据，这里没有插入 UUID 值，使用默认值
insert into uuid_test (c2) select 'v2';
-- 查询数据
select * from uuid_test;
┌───────────────────────────────────c1─┬─c2─┐
│ b7c432ba-c9aa-486f-bee6-6362499a3fca │ v1 │
└──────────────────────────────────────┴────┘
-- 可以看到，这行没有被插入值的 UUID 被 0 填充了。
┌───────────────────────────────────c1─┬─c2─┐
│ 00000000-0000-0000-0000-000000000000 │ v2 │
└──────────────────────────────────────┴────┘
5. 日期时间类型
时间类型分为 DateTime、DateTime64 和 Date 三类。ClickHouse 目前没有时间戳类型。时间类型最
高的精度是秒，也就是说，如果需要处理毫秒、微秒等大于秒分辨率的时间，则只能借助 UInt 类型实
现。
Date: 2020-02-02
DateTime: 2020-02-02 20:20:20
DateTime64: 2020-02-02 20:20:20.335
5.1 Date类型
- Date 日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。
- 允许存储从 Unix 纪元开始到编译阶段定义的上限阈值常量（目前上限是2106年，但最终完全支持的年份为2105）。
- Date类型不包含具体的时间信息，只精确到天，它支持字符串形式写入。
- 最小值输出为1970-01-01。
- 日期中没有存储时区信息。
- 默认情况下，客户端连接到服务的时候会使用服务端时区。
- 可以通过启用客户端命令行选项 –use_client_time_zone 来设置使用客户端时间。
-- 建表
create table date_test(
c1 Date
) engine = Memory;
-- 测试写入数据，以字符串形式写入
insert into date_test values('2021-04-16');
-- 测试写入数据，以时间戳形式写入 ，（单位秒）
insert into date_test values(1546300800);
-- 查询数据
select c1, toTypeName(c1) from date_test;
┌─────────c1─┬─toTypeName(c1)─┐
│ 2021-04-16 │ Date │
│ 2019-01-01 │ Date │
└────────────┴────────────────┘
5.2 DateTime类型
- DateTime 时间戳类型，用四个字节存储 Unix 时间戳
- DateTime 类型包含时、分、秒信息，精确到秒，支持使用字符串形式写入
- 允许存储与日期类型相同的范围内的值。最小值为 1970-01-01 00:00:00
- 因为是Unsigned的整形，因此不能支持1970年1月1日（UTC/GMT的午夜）以前的时间。
- 时区会影响输入和输出。请使–use_client_time_zone 进行切换时区，服务端启动时候最好使用 TZ=X
来保证时区 - 默认情况下，客户端连接到服务的时候会使用服务端时区
-- 建表
CREATE TABLE datetime_test
(
`c1` DateTime
)
ENGINE = Memory
-- 测试写入数据，以字符串形式写入
insert into datetime_test values('2021-04-16 17:07:50');
-- 测试写入数据，以时间戳形式写入 ，（单位秒）
insert into datetime_test values(1546300800);
-- 查询数据
select c1, toTypeName(c1) from datetime_test;
┌──────────────────c1─┬─toTypeName(c1)─┐
│ 2021-04-16 17:07:50 │ DateTime │
└─────────────────────┴────────────────┘
┌──────────────────c1─┬─toTypeName(c1)─┐
│ 2019-01-01 08:00:00 │ DateTime │
└─────────────────────┴────────────────┘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 类型列写入数据
-- 建表
create table datetime64_test2(
c1 DateTime64(3,'Asia/Shanghai'),
k1 String
) engine = Memory;
-- 测试写入数据，以字符串形式写入
insert into datetime64_test2 values ('2019-01-01 00:00:00', 2);
-- 测试写入数据，以时间戳形式写入 ，（单位秒）
insert into datetime64_test2 values (1546300800000, 1);
-- 查询数据
select * from datetime64_test2;
┌──────────────────────c1─┬─k1─┐
│ 2019-01-01 08:00:00.000 │ 1 │
│ 2019-01-01 00:00:00.000 │ 2 │
└─────────────────────────┴────┘
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设置精度示例
-- 建表
create table datetime64_test(
c1 DateTime64(1,'Asia/Shanghai'),
c2 DateTime64(2,'Asia/Shanghai'),
c3 DateTime64(3,'Asia/Shanghai'),
c4 DateTime64(4,'Asia/Shanghai'),
c5 DateTime64(5,'Asia/Shanghai')
) engine = Memory;
-- 插入数据
insert into datetime64_test (c1, c2,c3,c4,c5) values('2021-04-16 17:34:57.11111','2021-04-16 17:34:57.11111','2021-04-16 17:34:57.11111','2021-04-16 17:34:57.11111','2021-04-16 17:34:57.11111');
-- 查询数据
select * from datetime64_test;
┌────────────────────c1─┬─────────────────────c2─┬──────────────────────c3─┬───────────────────────c4─┬────────────────────────c5─┐
│ 2021-04-16 17:34:57.1 │ 2021-04-16 17:34:57.11 │ 2021-04-16 17:34:57.111 │ 2021-04-16 17:34:57.1111 │ 2021-04-16 17:34:57.11111 │
└───────────────────────┴────────────────────────┴─────────────────────────┴──────────────────────────┴───────────────────────────┘
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 - 时间间隔不能做计算，但是可以使用日期时间类型数据分别与时间间隔类型进行计算
-- 查看间隔类型
SELECT toTypeName(INTERVAL 4 DAY) as type;
┌─type────────┐
│ IntervalDay │
└─────────────┘
-- 使用时间间隔做时间计算
SELECT now() as current_date_time, current_date_time + INTERVAL 4 DAY;
┌───current_date_time─┬─plus(now(), toIntervalDay(4))─┐
│ 2021-04-20 09:56:35 │ 2021-04-24 09:56:35 │
└─────────────────────┴───────────────────────────────┘
-- 使用 INTERVAL 1 DAY 1 HOUR 进行计算
SELECT now() as current_date_time, current_date_time + INTERVAL 1 DAY 1 HOUR;
Syntax error: failed at position 71 ('1'):
Expected one of: UNION, LIMIT, WHERE, WINDOW, LIKE, GLOBAL NOT IN, end of query, HAVING, AS, IS, GROUP BY, INTO OUTFILE, OR, identifier, QuestionMark, BETWEEN, OFFSET, NOT LIKE, PREWHERE, AND, Comma, alias, ORDER BY, SETTINGS, IN, ILIKE, FROM, FORMAT, NOT ILIKE, WITH, NOT, token, NOT IN, GLOBAL IN
-- 使用 25 HOUR 进行计算
SELECT now() as current_date_time, current_date_time + INTERVAL 25 HOUR;
┌───current_date_time─┬─plus(now(), toIntervalHour(25))─┐
│ 2021-04-20 10:01:32 │ 2021-04-21 11:01:32 │
└─────────────────────┴─────────────────────────────────┘
-- 时间与不同时间间隔类型分别进行计算
SELECT now() AS current_date_time, current_date_time + INTERVAL 1 DAY + INTERVAL 1 HOUR ;
┌───current_date_time─┬─plus(plus(now(), toIntervalDay(1)), toIntervalHour(1))─┐
│ 2021-04-20 10:04:13 │ 2021-04-21 11:04:13 │
└─────────────────────┴────────────────────────────────────────────────────────┘
-- 不同时间间隔类型进行计算
select now() AS current_date_time, current_date_time + (INTERVAL 1 DAY + INTERVAL 1 HOUR);
Received exception from server (version 21.4.3):
Code: 43. DB::Exception: Received from localhost:9977. DB::Exception: Illegal types IntervalDay and IntervalHour of arguments of function plus: While processing now() AS current_date_time, current_date_time + (toIntervalDay(1) + toIntervalHour(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 | 无 |
