clickhouse数据类型

https://clickhouse.com/docs/zh/sql-reference/data-types/int-uint

https://www.cnblogs.com/traditional/p/15218628.html

https://zhuanlan.zhihu.com/p/559077600

 

https://cloud.tencent.com/developer/article/1964429

 

https://help.aliyun.com/document_detail/146000.html?spm=a2c4g.146003.0.0.400c717fTDAjHH

https://cookcode.blog.csdn.net/article/details/108827707

 

 

 

ClickHouse 的数据类型

 

 

楔子

作为一款分析型数据库，ClickHouse 提供了许多数据类型，它们可以划分为基础类型、复合类型和特殊类型。其中基础类型使 ClickHouse 具备了描述数据的基本能力，而另外两种类型则使 ClickHouse 的数据表达能力更加的丰富立体。

下面就来分门别类的介绍一下。

基础类型

基础类型只有数值、字符串和时间三种类型，注：准确来说，还有布尔类型（Bool），但由于没有 true、false，所以一般都用整型（UInt8）表示布尔类型，1 为真 0 为假。

数值类型

数值类型分为整数、浮点数和 Decimal 三类，接下来分别进行说明。

1）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。

2）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 :)

3）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 三类，从命名来看仿佛不像是一款数据库提供的类型，反倒像一门编程语言的设计。

1）String

字符串由 String 定义，长度不限，因为在使用 String 的时候无需声明大小。它完全代替了传统意义上的 Varchar、Text、Clob 和 Blob 等字符类型。String 类型不限定字符集，因为它根本没有这个概念，所以可以将任意编码的字符串存入其中。但是为了程序的规范性和可维护性，在同一套程序中使用统一的编码，比如 utf-8，就是一种很好的约定。

2）FiexedString

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 :)

3）UUID

UUID 是一种数据库常见的主键类型，在 ClickHouse 中直接把它作为一种数据类型。UUID 共有 32 位，它的格式为 8-4-4-4-12。如果一个 UUID 类型的字段在写入数据的时候没有被赋值，那么它会按照相应格式用 0 填充。

时间类型

时间类型分为 DateTime、DateTime64 和 Date三类。

1）DateTime

DateTime 类型包含年、月、日、时、分、秒信息，精确到秒，支持使用字符串的方式写入；

2）DateTime64

DateTime64 可以记录亚秒，它在 DateTime 之上增加了精度的设置。举个栗子：DateTime64 类型的时间可以是 2018-01-01 12:12:32.22；但如果是 DateTime 的话，则是2018-01-01 12:12:32，也就是说最后的 .22 没了。

3）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。

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, 2.13] 可以，但是 [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 存储，相比 String 更加紧凑，占用的空间更小，查询性能更快。IPv6 类型是基于 FixedString(16) 封装的，它的使用方法与 IPv4 别无二致，此处不再赘述。

在使用 Domain 类型的时候还有一点需要注意，虽然它从表象上看起来与 String 一样，但 Domain 类型并不是字符串，所以它不支持隐式的自动类型转换。如果需要返回 IP 的字符串形式，则需要显式调用 IPv4NumToString 或 IPv6NumToString 函数进行转换。

 

如果觉得文章对您有所帮助，可以请囊中羞涩的作者喝杯柠檬水，万分感谢，愿每一个来到这里的人都生活愉快，幸福美满。

ClickHouse 数据类型全解析及实际应用

发布于 2022-03-28 08:10:22
1.5K0
举报

一、数据类型

基础类型只有数值、字符串和时间三种类型，没有 Boolean 类型，但可以使用整型的 0 或 1 替代。ClickHouse 的数据类型和常见的其他存储系统的数据类型对比:

官网：https://clickhouse.tech/docs/zh/sql-reference/data-types/

1.1、数值类型

数值类型分为整数、浮点数和定点数三类

1.1.1、整数

在普遍观念中，常用Tinyint、Smallint、Int 和 Bigint 指代整数的不同取值范围。而 ClickHouse 则直接 使用Int8、Int16、Int32 和 Int64 指代 4 种大小的 Int 类型，其末尾的数字正好表明了占用字节的大小 (8位=1字节)。ClickHouse 支持无符号的整数，使用前缀 U 表示。固定长度的整型，包括有符号整 型或无符号整型。

整型范围:( -2^{n-1}到 2^{n-1}-1):

Int8 - [-128 : 127]
Int16 - [-32768 : 32767]
Int32 - [-2147483648 : 2147483647]
Int64 - [-9223372036854775808 : 9223372036854775807]

无符号整型范围( 0到 2^{n-1}-1):

UInt8 - [0 : 255]
UInt16 - [0 : 65535]
UInt32 - [0 : 4294967295]
UInt64 - [0 : 18446744073709551615]

1.1.2、浮点型

与整数类似，ClickHouse 直接使用 Float32 和 Float64 代表单精度浮点数以及双精度浮点数

ClickHouse 的浮点类型有两种值:

Float32 - float
Float64 - double

建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示，因为浮点型进行计算时可能引起四舍五入的误差。

SELECT 1 - 0.9
┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘
1 rows in set. Elapsed: 0.002 sec.

与标准 SQL 相比，ClickHouse 支持以下类别的浮点数:

Inf - 正无穷:

bigdata02 :) select 1 / 0;
SELECT 1 / 0
┌─divide(1, 0)─┐
│          inf │
└──────────────┘
1 rows in set. Elapsed: 0.001 sec.

-Inf - 负无穷:

bigdata02 :) select -1 / 0;
SELECT -1 / 0
┌─divide(-1, 0)─┐
│          -inf │
└───────────────┘
1 rows in set. Elapsed: 0.001 sec.

NaN - 非数字:

bigdata02 :) select 0 / 0;
SELECT 0 / 0
┌─divide(0, 0)─┐
│          nan │
└──────────────┘
1 rows in set. Elapsed: 0.001 sec.

在使用浮点数的时候，应当要意识到它是有限精度的。假如，分别对 Float32 和 Float64 写入超过有效 精度的数值，下面我们看看会发生什么。例如，将拥有 20 位小数的数值分别写入 Float32 和 Float64， 此时结果就会出现数据误差:

bigdata02 :) select toFloat32('0.1234567901234567890123456789') as a,
toTypeName(a);
┌──────────a─┬─toTypeName(toFloat32('0.1234567901234567890123456789'))─┐
│ 0.12345679 │ Float32                                                 │
└────────────┴─────────────────────────────────────────────────────────┘
bigdata02 :) select toFloat64('0.1234567901234567890123456789') as a,
toTypeName(a);
┌───────────────────a─┬─toTypeName(toFloat64('0.1234567901234567890123456789'))─┐
│ 0.12345679012345678 │ Float64│
└─────────────────────┴─────────────────────────────────────────────────────────┘

可以发现，Float32 从小数点后第 8 位起及 Float64 从小数点后第 17 位起，都产生了数据溢出。

1.1.3、Decimal

如果要求更高精度的数值运算，则需要使用定点数。ClickHouse 提供了Decimal32、Decimal64 和 Decimal128 三种精度的定点数。

可以通过两种形式声明定点:简写方式有 Decimal32(S)、 Decimal64(S)、Decimal128(S)三种，原生方式为Decimal(P, S)，其中:

P代表精度，决定总位数(整数部分+小数部分)，取值范围是1~38; S代表规模，决定小数位数，取值范围是0~P。

简写方式与原生方式的对应关系

在使用两个不同精度的定点数进行四则运算的时候，它们的小数点位数 S 会发生变化。在进行加法运算 时，S 取最大值。例如下面的查询，toDecimal64(2,4) 与 toDecimal32(2,2) 相加后 S=4:

select toDecimal64(2, 4) + toDecimal32(2, 2);

结果:

┌─plus(toDecimal64(2, 4), toDecimal32(2, 2))─┐
│                                     4.0000 │
└────────────────────────────────────────────┘

注意 Decimal 进行加减乘除四则运算的时候的精度问题!总结一下是:

1.1.4、布尔型

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。而且在 ClickHouse 使用过 程中，你也会发现，做比较得到的结果都是 1 或者 0，而不是通常意义上的 True 或者 False

select 1 == 1;
select 1 == 2;

1.2、字符串

字符串类型可以细分为 String、FixedString 和 UUID 三类

1.2.1、String类型

字符串可以任意长度的。它可以包含任意的字节集，包含空字节，可以用来替换 VARCHAR ,BLOB,CLOB 等数据类型。字符串由String定义，长度不限。因此在使用 String 的时候无须声明大小。它完全代替了传统意义上数据库的Varchar、Text、Clob 和 Blob 等字符类型。String 类型不限定字符 集，因为它根本就没有这个概念，所以可以将任意编码的字符串存入其中。但是为了程序的规范性和可 维护性，在同一套程序中应该遵循使用统一的编码，例如“统一保持UTF-8编码”就是一种很好的约定。

create table dylan_test02(
    id UInt8,
    name String
) engine = Memory;

1.2.2、FixedString(N)

FixedString 类型和传统意义上的 Char 类型有些类似，对于一些字符有明确长度的场合，可以使用固定长度的字符串。定长字符串通过 FixedString(N) 声明，其中 N 表示字符串长度。但与 Char 不同的是， FixedString 使用 null字节填充末尾字符，而 Char 通常使用空格填充。比如在下面的例子中，字符串 ‘abc’ 虽然只有 3 位，但长度却是5，因为末尾有2位空字符填充:

SELECT toFixedString('abc', 5), LENGTH(toFixedString ('abc',5))AS LENGTH;

固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字 符串末尾添加空字节来达到 N 字节长度。当服务端读取长度大于 N 的字符串时候，将返回错误消息。与 String 相比，极少会使用 FixedString，因为使用起来不是很方便。

总结:

A、N是最大字节数(Byte),不是字符长度,如果是UTF8字符串,那么就会占用3字节,GBK会占用2字节. B、当内容少于N,数据库会自动在右填充空字节(null byte)(跟PGsql不一样,PGsql填充的是空格),当内 容大于N时候,会抛出错误. C、当写入内容后面后空字节,系统不会自动去裁剪,查询的时候也会被输出(mysql不会输出) D、FixedString(N) 比 String 支持更少的方法

1.2.3、UUID

UUID 是一种数据库常见的主键类型，在 ClickHouse 中直接把它作为一种数据类型。UUID 共有 32 位，它的格式为00000000-0000-0000-0000-000000000000。如果一个 UUID 类型的字段在写入数据时没有被赋值，则会依照格式使用 0 填充，例如:

-- 建表
create table uuid_test(
c1 UUID,
    c2 String
) engine = Memory;
-- 插入数据
insert into uuid_test select generateUUIDv4(), 't1'; insert into uuid_test (c2) select 't2';
-- 查询数据
select * from uuid_test;

自行执行结果可以看到，第二行没有被赋值的 UUID 被 0 填充了。

1.3. 日期时间类型

时间类型分为 DateTime、DateTime64 和 Date 三类。ClickHouse 目前没有时间戳类型。时间类型最 高的精度是秒，也就是说，如果需要处理毫秒、微秒等大于秒分辨率的时间，则只能借助 UInt 类型实 现。

Date:  2020-02-02
DateTime: 2020-02-02 20:20:20
DateTime64: 2020-02-02 20:20:20.335

1.3.1. Date类型

日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。允许存储从 Unix 纪元开始 到编译阶段定义的上限阈值常量(目前上限是2106年，但最终完全支持的年份为2105)。最小值输出 为1970-01-01。

Date类型不包含具体的时间信息，只精确到天，它支持字符串形式写入。需要注意的是:日期中没有存储时区信息。默认情况下，客户端连接到服务的时候会使用服务端时区。

可以通过启用客户端命令行选项 --use_client_time_zone 来设置使用客户端时间。

-- 建表
drop table if exists date_test; create table date_test(
    c1 Date
) engine = Memory;
-- 插入数据
insert into date_test values('2021-04-25');
--查询
select c1, toTypeName(c1) from date_test;

1.3.2. DateTime类型

时间戳类型。用四个字节(无符号的整数类型，Uint32)存储 Unix 时间戳)。允许存储与 Date 类型相同的范围内的值。最小值为 1970-01-01 00:00:00。时间戳类型值精确到秒(没有闰秒)。

DateTime 类型包含时、分、秒信息，精确到秒，支持使用字符串形式写入。

总结:

A、因为是Unsigned的整形，因此不能支持1970年1月1日(UTC/GMT的午夜)以前的时间。 B、时区会影响输入和输出。请使–use_client_time_zone 进行切换时区，服务端启动时候最好使用TZ=X 来保证时区

-- 建表
create table datetime_test(
    c1 DateTime
) engine = Memory;
-- 插入数据
insert into datetime_test values('2021-04-25 10:20:30');
--查询
select c1, toTypeName(c1) from datetime_test;

1.3.3. DateTime64类型

DateTime64 可以记录亚秒，它在 DateTime 之上增加了精度的设置，例如:

-- 建表
drop table if exists datetime64_test; create table datetime64_test(
    c1 DateTime64(2),
    c2 DateTime64(4)
) engine = Memory;
-- 插入数据
insert into datetime64_test (c1, c2) values('2021-04-25 10:20:30', '2021-04-25 10:20:30.333');
--查询
select c1, toTypeName(c1), c2, toTypeName(c2) from datetime64_test;

1.4. 复合类型

除了基础数据类型之外，ClickHouse 还提供了数组、元组、枚举和嵌套四类复合类型。这些类型通常是其他数据库原生不具备的特性。拥有了复合类型之后，ClickHouse 的数据模型表达能力更强了。

1.4.1. 枚举类型

ClickHouse 支持枚举类型，这是一种在定义常量时经常会使用的数据类型。ClickHouse 提供了 Enum8 和Enum16 两种枚举类型，它们除了取值范围不同之外，别无二致。枚举固定使用(String:Int) Key/Value键值对的形式定义数据，所以Enum8和Enum16分别会对应(String:Int8) 和 (String:Int16)包括 Enum8 和 Enum16 类型。Enum 保存 'string'= integer 的对应关系。建表的时候 是 不允许重复的。其次， Key/Value 不能同时为 Null ，但是Key允许空字符串。在定义枚举集合的时候，有几点需要注意。首先，Key和Value是不允许重复的，要保证唯一性。其次， Key和Value的值都不能为Null，但Key允许是空字符串。在写入枚举数据的时候，只会用到Key字符串部分

Enum8 用 'String'= Int8 对描述 
Enum16 用 'String'= Int16 对描述

用法演示:创建一个带有一个枚举 Enum8('a' = 1, 'b' = 2, 'c'=3) 类型的列:

CREATE TABLE dylan_enum_test (x Enum8('a' = 1, 'b' = 2, 'c'=3)) ENGINE = TinyLog;

这个 res 列只能存储类型定义中列出的值: 'a' 或 'b' 或者 'c' 。如果尝试保存任何其他值， ClickHouse 抛出异常。

插入正常数据:

INSERT INTO dylan_enum_test VALUES ('a'), ('b'), ('c');

查询结果:

select * from dylan_enum_test;

插入异常数据:

insert into dylan_enum_test values('test');

如果需要看到对应行的数值，则必须将 Enum 值转换为整数类型。

SELECT CAST(res, 'Int8') FROM dylan_enum_test;

可能有人会觉得，完全可以使用String代替枚举，为什么还需要专门的枚举类型呢?这是出于性能的考虑。因为虽然枚举定义中的Key属于String类型，但是在后续对枚举的所有操作中(包括排序、分组、 去重、过滤等)，会使用Int类型的Value值。

1.4.2. 数组

T 可以是任意类型，包含数组类型。但不推荐使用多维数组，ClickHouse 对多维数组的支持有限。例如，不能存储在 MergeTree 表中存储多维数组。

可以使用array函数来创建数组:

array(T)

也可以使用方括号:

[]

创建数组:

SELECT array(1, 2) AS x, toTypeName(x);
SELECT [1, 2] AS x, toTypeName(x);

ClickHouse 会自动检测数组元素，并根据元素计算出存储这些元素最小的数据类型。如果在元素中存 在 Null 或存在 Nullable 类型元素，那么数组的元素类型将会变成 Nullable 。

如果 ClickHouse 无法确定数据类型，它将产生异常。当尝试同时创建一个包含字符串和数字的数组时 会发生这种情况 ( SELECT array(1, 'a') )。

SELECT array(1, 2, NULL) AS x, toTypeName(x);

如果尝试创建不兼容的数据类型数组，ClickHouse 将引发异常:

SELECT array(1, 'a');

1.4.3. 元组

元组类型由1~n个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间不要求兼容。元组同 样支持类型推断，其推断依据仍然以最小存储代价为原则。与数组类似，元组也可以使用两种方式定 义，常规方式tuple(T)。

元素类型和泛型的作用类似，可以进一步保障数据质量。在数据写入的过程中会进行类型检查。Tuple(T1, T2, ...):元组，其中每个元素都有单独的类型。

创建元组的示例:

SELECT tuple(1,'a') AS x, toTypeName(x);
SELECT tuple(1,'a') AS x, toTypeName(x), x.1, x.2;

创建一张带tuple字段的表:

drop table dylan_tuple_table;
CREATE TABLE dylan_tuple_table (t Tuple(Int8, String, Array(String), Array(Int8)))
ENGINE = TinyLog;

插入数据:

INSERT INTO dylan_tuple_table VALUES((1, 'a', ['a', 'b', 'c'], [1, 2, 3])),(tuple(11, 'A', ['A', 'B', 'C'], [11, 22, 33]));

查看数据:

SELECT t, t.2, t.3, t.4 FROM dylan_tuple_table;

1.4.4. 嵌套类型

嵌套类型，顾名思义是一种嵌套表结构。一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系，使用嵌套类型也是一种不错的选择。嵌套Nested(Name1 Type1，Name2 Type2，...) 嵌套的数据结构就像一个嵌套的表。嵌套数据结构的参数 - 列名和类型 - 与在CREATE查询中的指定方式相同。每个表的行可以对应于嵌套数据结构中的任意数量的行。

如下：

drop table dylan_test_table;
CREATE TABLE dylan_nest_table (
    name String,
    age Int8,
    dept Nested(
id UInt8,
name String )
)
ENGINE = TinyLog;

插入数据:

insert into dylan_test_table values ('tt', 1, 1, '哎'); 
insert into dylan_test_table values ('tt1', 2, (1, '哟'));

插入报错，核心异常信息为:

DB::Exception: Type mismatch in IN or VALUES section. Expected: Array(UInt8).Got: UInt64

通过此信息显示，异常显示需要 Array ，而不是单纯的 Int 。所以这里也就明白:嵌套类型本质是一 个多维数组的结构。嵌套类型的一个字段对应一个数组。字段对应的数组内的数量没有限制，但是字段 之间需要数组内的数量对齐。

正确的插入语句应该为:

insert into dylan_test_table values ('tt', 1, [1], ['哎']); 
insert into dylan_test_table values ('tt1', 1, [1,2,3], ['python','spark','flink']);

其实，在Hive中，有一种复杂类型叫做 Struct，跟当前这种情况很类似，但是根据经验，推荐尽量少使用 Nested 类型

查询:

select * from dylan_test_table;

结果:

┌─name────┬─age─┬─dept.id─┬─dept.name────────────┐ 
│ tt │ 1 │ [1] │ ['哎'] │ 
│ tt1 │ 1 │ [1,2,3] │ ['python','spark','flink'] │
└─────────┴─────┴─────────┴──────────────────────┘

1.5. 其他类型

1.5.1. Nullable(TypeName)

准确来说，Nullable 并不能算是一种独立的数据类型，它更像是一种辅助的修饰符，需要与基础数据类 型一起搭配使用。Nullable 类型与 Java8 的 Optional 对象有些相似，它表示某个基础数据类型可以是 Null 值。

可以用特殊标记 (NULL) 表示 "缺失值"，可以与 TypeName 的正常值存放一起。例如，Nullable(Int8) 类 型的列可以存储 Int8 类型值，而没有值的行将存储 NULL。

官网解释:

允许用特殊标记 (NULL) 表示«缺失值»，可以与 TypeName 的正常值存放一起。例如， Nullable(Int8) 类型的列可以存储 Int8 类型值，而没有值的行将存储 NULL。 对于 TypeName，不能使用复合数据类型 阵列 和 元组。复合数据类型可以包含 Nullable 类型值，例如 Array(Nullable(Int8))。 Nullable 类型字段不能包含在表索引中。 除非在 ClickHouse 服务器配置中另有说明，否则 NULL 是任何 Nullable 类型的默认值。

特点:

  1. Nullable 只能和基本类型搭配使用;
  2. 不能使用在 Array/Tuple 这种复合类型上;
  3. 不能作为索引字段【Order by()】;
  4. 慎用 Nullable ，写入写出性能不好。因为它会生成单独的文件。

用法:

--建表
create table dylan_null_test(
c1 String,
    c2 Nullable(UInt8)
) engine = TinyLog;
--插入数据
insert into dylan_null_test values ('aa', null); 
insert into dylan_null_test values ('aa', 1);
--查询数据
select c1, c2 from dylan_null_test;

在使用 Nullable 类型的时候还有两点值得注意:

首先，它只能和基础类型搭配使用，不能用于数组和 元组这些复合类型，也不能作为索引字段;

其次，应该慎用 Nullable 类型，包括 Nullable 的数据表， 不然会使查询和写入性能变慢。因为在正常情况下，每个列字段的数据会被存储在对应的 [Column].bin 文件中。如果一个列字段被Nullable 类型修饰后，会额外生成一个 [Column].null.bin 文件专门保存它 的 Null 值。这意味着在读取和写入数据时，需要一倍的额外文件操作。

1.5.2. Domain

域名类型分为 IPv4 和 IPv6 两类，本质上它们是对整型和字符串的进一步封装。IPv4 类型是基于 UInt32 封装的，它的具体用法如下所示:

分为 IPv4 IPv6 。其实本质都是对整型，字符串进行的封装。

IPv4 使用 UInt32 存储。如 110.253.30.113
IPv6 使用 FixedString(16) 存储。如 2e02:ea08:e010:4100::2

用法:

-- 建表
drop table if exists dylan_ip_test; 
create table dylan_ip_test(
url String,
    ip IPv4
) engine = Memory;
-- 插入数据
insert into dylan_ip_test values ('www.baidu.com', '110.253.30.113');
-- 查询数据
select url, ip, toTypeName(ip) from dylan_ip_test;

疑问:为什么不直接使用字符串来代替 IPv4 类型呢?

1、出于便捷性的考量，例如IPv4类型支持格式检查，格式错误的IP数据是无法被写入的 2、出于性能的考量，同样以IPv4为例，IPv4使用UInt32存储，相比String更加紧凑，占用的空间更小， 查询性能更快。IPv6类型是基于FixedString(16)封装的，它的使用方法与IPv4别无二致

在使用Domain类型的时候还有一点需要注意，虽然它从表象上看起来与String一样，但Domain类型并不是字符串，所以它不支持隐式的自动类型转换。如果需要返回IP的字符串形式，则需要显式调用 IPv4NumToString或IPv6NumToString函数进行转换。

 

posted @ 2023-06-05 11:42  He_LiangLiang  阅读(316)  评论(0)  编辑  收藏  举报