📅 2025年2月12日

📦 ck版本 25.1.3.23

🏆 Ck的数据类型

clickhouse 作为一款完备的DBMS(数据库管理系统)，提供了 DDL与 DML的功能,并支持大部分标准的 SQL，但是 ClickHouse所提供的 DDL 与 DML查询,在部分细节上也与其他数据库有所不同(例如 UPDATE 和 DELETE是借助 ALTER变种实现的

🌟 基础数据类型

基础类型只有数值、字符串和时间三种类型,没有 Boolean类型,但可以使用整型的0或1替代

1️⃣ 数值类型

数值类型分为整数、浮点数和定点数三类，和GO语言内的基础数字类型定义差不多

blog地址如下: Go基础类型

int 类型

ClickHouse则直接使用 Int8、Int16、Int32和 Int64指代4种大小的 Int类型,其末尾的数字正好表明了占用字节的大小(8位=1字节)

ck也支持无符号整形具体是直接在 int前面加个 u，和 go语言类似

Float类型

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

在使用浮点数的时候,应当要意识到它是有限精度的。假如,分别对

Float32和 Float64写入超过有效精度的数值，就会损失进度，如下

## 可以发现,Float32从小数点后第8位起及Float64从小数点后第  17位起,都产生了数据溢出
DESKTOP-HLBQNO4. :) SELECT toFloat32('0.12345678901234567890') as a , toTypeName(a);

   ┌──────────a─┬─toTypeName(a)─┐
1. │ 0.12345679 │ Float32       │
   └────────────┴───────────────┘

1 row in set. Elapsed: 0.002 sec.

DESKTOP-HLBQNO4. :) SELECT toFloat64('0.12345678901234567890') as a , toTypeName(a)

   ┌───────────────────a─┬─toTypeName(a)─┐
1. │ 0.12345678901234568 │ Float64       │

正/负无穷

ClickHouse的浮点数支持正无穷、负无穷以及非数字的表达方式，注意是浮点数才支持无穷和非数字的表达方式

## 正无穷
:) select toFloat64(0.8/0)
   ┌─toFloat64(divide(0.8, 0))─┐
1. │                       inf │
   └───────────────────────────┘
## 负无穷
:) select toFloat64(-0.8/0)
   ┌─toFloat64(divide(-0.8, 0))─┐
1. │                       -inf │
   └────────────────────────────┘
## 非数字
:) select toFloat64(0/0)
   ┌─toFloat64(divide(0, 0))─┐
1. │                     nan │
   └─────────────────────────┘

Decimal

如果要求更高精度的数值运算,则需要使用定点数。ClickHouse 提供了 Decimal32、Decimal64和 Decimal128三种精度的定点数。可以通过两种形式声明定点:简写方式有 Decimal32(S)、 Decimal64(S)、Decimal128(S)三种,原生方式为 Decimal(P,S)

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

简写方式和原生方式对应

在使用 decimal计算的时候在不同的运算符中是由不同的规则，规则如下：

在进行加法运算时,S取最大值。例如下面的查询,toDecimal64(4,2)与 toDecimal32(2,2)相加后S=6

select toDecimal64(4,2) + toDecimal32(2,2)
   ┌─plus(toDecim⋯al32(2, 2))─┐
1. │                        6 │
   └──────────────────────────┘

在进行减法运算时,其规则与加法运算相同,S同样会取最大值

SELECT toDecimal64(4, 2) - toDecimal32(2, 2)
   ┌─minus(toDeci⋯al32(2, 2))─┐
1. │                        2 │
   └──────────────────────────┘

在进行乘法运算时,S取两者S之和，注意由于版本问题我所看的书是19.x版本，这个版本会显示 8.0000，而新版本只会显示8，去除后面多余的0

SELECT toDecimal64(4, 2) * toDecimal32(2, 2)

   ┌─multiply(toD⋯al32(2, 2))─┐
1. │                        8 │
   └──────────────────────────┘

在进行除法运算时,S取被除数的值,此时要求被除数S必须大于除数S,否则会报错，同上这里应该是2.000

 SELECT toDecimal64(4, 3) / toDecimal32(2, 2)
   ┌─divide(toDec⋯al32(2, 2))─┐
1. │                        2 │
   └──────────────────────────┘


## 如下就报错了
SELECT toDecimal64(4, 3) / toDecimal32(2, 4)
Elapsed: 0.017 sec.

Received exception from server (version 25.1.3):
Code: 69. DB::Exception: Received from localhost:9000. DB::Exception: Decimal result's scale is less than argument's one: In scope SELECT toDecimal64(4, 3) / toDecimal32(2, 4). (ARGUMENT_OUT_OF_BOUND)

⚠️ 在使用定点数时还有一点值得注意:由于现代计算器系统只支持 32位和 64位CPU,所以 Decimal128是在软件层面模拟实现的,它的速度会明显慢于 Decimal32与 Decimal64

2️⃣ 字符串类型

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

string

字符串由 String定义,长度不限。因此在使用 String的时候无须声明大小，String类型不限定字符集,因为它根本就没有这个概念,所以可以将任意编码的字符串存入其中

FixedString

FixedString类型和传统意义上的Char类型有些类似,对于一些字符有明确长度的场合,可以使用固定长度的字符串。

定长字符串通过 FixedString(N)声明,其中N表示字符串长度。但与 Char不同的是,FixedString使用 null字节填充末尾字符,而Char通常使用空格填充

UUID

UUID共有32位,它的格式为 8-4-4-4-12。如果一个 UUID类型的字段在写入数据时没有被赋值,则会依照格式使用0填充

-- 创建UUID_TEST数据库
CREATE TABLE UUID_TEST (  c1 UUID,  c2 String  ) ENGINE = Memory;
-- 第一行UUID
有值  INSERT INTO UUID_TEST SELECT generateUUIDv4(),'t1'
-- 第二行
UUID没有值 INSERT INTO UUID_TEST(c2) VALUES('t2')
-- 查询
SELECT *
FROM UUID_TEST
   ┌─c1───────────────────────────────────┬─c2─┐
1. │ 00000000-0000-0000-0000-000000000000 │ t2 │
2. │ 88c45606-4a97-46bf-ba25-ebe49fdea555 │ t1 │
   └──────────────────────────────────────┴────┘

3️⃣ 时间类型

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

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

DateTime

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

CREATE TABLE Datetime_TEST (  c1 Datetime  ) ENGINE = Memory  
-- 以字符串形式写入  
INSERT INTO Datetime_TEST VALUES('2019-06-22 00:00:00')  


SELECT
    c1,
    toTypeName(c1) ## 可以看到数据类型为DateTime
FROM Datetime_TEST
   ┌──────────────────c1─┬─toTypeName(c1)─┐
1. │ 2019-06-22 00:00:00 │ DateTime       │
   └─────────────────────┴────────────────┘

DateTime64

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

CREATE TABLE Datetime64_TEST
(
    `c1` Datetime64(2) ## 精度设置
)
ENGINE = Memory

INSERT INTO Datetime64_TEST VALUES('2019-06-22 00:00:00')

SELECT
    c1,
    toTypeName(c1)
FROM Datetime64_TEST
   ┌─────────────────────c1─┬─toTypeName(c1)─┐
1. │ 2019-06-22 00:00:00.00 │ DateTime64(2)  │
   └────────────────────────┴────────────────┘

Date

Date类型不包含具体的时间信息,只精确到天,它同样也支持字符串形式写入

CREATE TABLE Date_TEST
(
    `c1` Date
)
ENGINE = Memory


INSERT INTO Date_TEST VALUES('2019-06-22')


SELECT c1, toTypeName(c1) FROM Date_TEST

   ┌─────────c1─┬─toTypeName(c1)─┐
1. │ 2019-06-22 │ Date           │
   └────────────┴────────────────┘

🌟 复合类型

除了基础数据类型之外,ClickHouse还提供了数组、元组、枚举和嵌套四类复合类型

数组

数组有两种定义形式,常规方式 array(T)

SELECT
    [1, 2] AS a,
    toTypeName(a)


   ┌─a─────┬─toTypeName(a)─┐
1. │ [1,2] │ Array(UInt8)  │
   └───────┴───────────────┘

或者简写方式[T]

SELECT [1, 2]

   ┌─[1, 2]─┐
1. │ [1,2]  │
   └────────

但是有没有发现一个问题，在使用数组时，并没有定义数组内元素的类型，这是因为ck的数组拥有类型推断的能力,推断依据: 以最小存储代价为原则,即使用最小可表达的数据类型，

例如在上面的例子中,array(1,2)会通过自动推断将 UInt8作为数组类型。但是数组元素中如果存在 Null值,则元素类型将变为 Nullable(后面会介绍)

SELECT
    [1, 2] AS a,
    toTypeName(a)

   ┌─a─────┬─toTypeName(a)─┐
1. │ [1,2] │ Array(UInt8)  │
   └───────┴───────────────┘
   
## 如果存在NULL
SELECT
    [1, 2, NULL] AS a,
    toTypeName(a)
   ┌─a──────────┬─toTypeName(a)──────────┐
1. │ [1,2,NULL] │ Array(Nullable(UInt8)) │
   └────────────┴────────────────────────┘

在ck的数组中同一个数组内可以包含多种数据类型,例如数组 [1,2.0]是可行的。但各类型之间必须兼容,例如数组 [1,'2']则会报错

在定义表字段时,数组需要指定明确的元素类型

CREATE TABLE Array_TEST
(
    `c1` Array(String) ## 申明数组内类型
)
ENGINE = Memory

元组

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

blog 地址: Python元组

SELECT
    (1, 2, now()) AS a,
    toTypeName(a)

   ┌─a───────────────────────────┬─toTypeName(a)─────────────────┐
1. │ (1,2,'2025-02-16 18:09:36') │ Tuple(UInt8, UInt8, DateTime) │
   └─────────────────────────────┴───────────────────────────────┘
   
## 简写
SELECT (1, 2, 0, NULL)

   ┌─(1, 2, 0, NULL)─┐
1. │ (1,2,0,NULL)    │
   └─────────────────┘

在定义表字段时,元组也需要指定明确的元素类型

CREATE TABLE Tuple_TEST (  c1 Tuple(String,Int8)  ) ENGINE = Memory;

元素类型和泛型的作用类似,可以进一步保障数据质量。在数据写入的过程中会进行类型检查。例如,如果按照上面表结构: 写入

INSERT INTO Tuple_TEST VALUES(('abc',123))是可行的,而写入 INSERT INTO Tuple_TEST VALUES(('abc','efg'))则会报错

枚举

CK支持枚举类型,这是一种在定义常量时经常会使用的数据类型。ClickHouse提供了 Enum8和 Enum16两种枚举类型,它们除了取值范围不同之外,别无二致。枚举固定使用 (String:Int)Key/Value键值对的形式定义数据,所以 Enum8和 Enum16分别会对应 (String:Int8)和 (String:Int16),前者为 key的类型，后者为 value类型

CREATE TABLE Enum_TEST
(
    `c1` Enum8('ready' = 1, 'start' = 2, 'success' = 3, 'error' = 4)
)
ENGINE = Memory

在定义枚举集合的时候,有几点需要注意:

首先,Key和 Value 是不允许重复的,要保证唯一性。
其次,Key和 Value的值都不能为 Null,但 Key允许是空字符串。在写入枚举数据的时候,只会用到 Key字符串部分

## 连续执行4次
INSERT INTO Enum_TEST VALUES('ready');  
INSERT INTO Enum_TEST VALUES('start');
INSERT INTO Enum_TEST VALUES('ready');  
INSERT INTO Enum_TEST VALUES('start');

## 查询
SELECT c1
FROM Enum_TEST
   ┌─c1────┐
1. │ start │
2. │ ready │
3. │ ready │
4. │ start │
   └───────┘

但是如果插入在表定义枚举其他类型时就会报错

INSERT INTO Enum_TEST FORMAT Values

Ok.

##报错
Error on processing query: Code: 691. DB::Exception: Unknown element 'stop' for enum: while executing 'FUNCTION if(isNull(-dummy-0) : 3, defaultValueOfTypeName('Enum8(\'ready\' = 1, \'start\' = 2, \'success\' = 3, \'error\' = 4)') :: 2, _CAST(-dummy-0, 'Enum8(\'ready\' = 1, \'start\' = 2, \'success\' = 3, \'error\' = 4)') :: 4) -> if(isNull(-dummy-0), defaultValueOfTypeName('Enum8(\'ready\' = 1, \'start\' = 2, \'success\' = 3, \'error\' = 4)'), _CAST(-dummy-0, 'Enum8(\'ready\' = 1, \'start\' = 2, \'success\' = 3, \'error\' = 4)')) Enum8('ready' = 1, 'start' = 2, 'success' = 3, 'error' = 4) : 1': While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (UNKNOWN_ELEMENT_OF_ENUM) (version 25.1.3.23 (official build))

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

嵌套

嵌套类型,顾名思义是一种嵌套表结构。一张数据表,可以定义任意多个嵌套类型字段,但每个字段的嵌套层级只支持一级,即嵌套表内不能继续使用嵌套类型

CREATE TABLE nested_test
(
    `name` String,
    `age` UInt8,
    `dept` Nested(id UInt8, name String)
)
ENGINE = Memory

但是需要注意的是 nested_test表和 dept这个嵌套结构并不是一对一关系,嵌套类型本质是一种多维数组的结构。嵌套表中的每个字段都是一个数组，但是在插入数据的时候，在同一行数据内每个数组字段的长度必须相等

INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001,10002], ['研发部','技  术支持中心','测试部']); 
-- 行与行之间,数组长度无须对齐  
INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001], ['研发部','技术支持中  心']);

在访问嵌套类型的数据时需要使用点符号

SELECT   
    name,
    dept.id,
    dept.name ## 使用点符号
FROM nested_test

   ┌─name──┬─dept.id────┬─dept.name────────────────────────────┐
1. │ bruce │ [16,17,18] │ ['研发部','技  术支持中心','测试部'] │
2. │ bruce │ [16,17]    │ ['研发部','技术支持中  心']          │
   └───────┴────────────┴──────────────────────────────────────┘

🌟 复合类型

ClickHouse还有一类不同寻常的数据类型,我将它们定义为特殊类型

Nullable

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

blog: Java Optional

CREATE TABLE Null_TEST
(
    `c1` String,
    `c2` Nullable(UInt8)
)
ENGINE = TinyLog

## 表示可以为null也可以为unint类型
INSERT INTO Null_TEST VALUES ('nauu',null)  
INSERT INTO Null_TEST VALUES ('bruce',20)

SELECT
    c1,
    c2,
    toTypeName(c2)
FROM Null_TEST

Query id: f7dd443f-c86e-4549-9778-58e2c7a2b311

   ┌─c1────┬───c2─┬─toTypeName(c2)──┐
1. │ nauu  │ ᴺᵁᴸᴸ │ Nullable(UInt8) │
2. │ bruce │   20 │ Nullable(UInt8) │
	————————————————————————————————

此外在ck中如果 input_format_null_as_default设置为1，则非 Nullable列中的 NULL会被转换为默认值（零值）；否则会报错

INSERT INTO Null_TEST FORMAT Values

SELECT
    c1,
    c2,
    toTypeName(c2)
FROM Null_TEST

Query id: f7dd443f-c86e-4549-9778-58e2c7a2b311

   ┌─c1────┬───c2─┬─toTypeName(c2)──┐
1. │ nauu  │ ᴺᵁᴸᴸ │ Nullable(UInt8) │
2. │ bruce │   20 │ Nullable(UInt8) │
3. │       │ ᴺᵁᴸᴸ │ Nullable(UInt8) │
   └───────┴──────┴─────────────────┘ ## 可以看到这里显示的是空字符串

在使用 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 ('blog.tanc.fun:9999','192.0.0.0')

##查询
SELECT *
FROM IP4_TEST
   ┌─url────────────────┬─ip────────┐
1. │ blog.tanc.fun:9999 │ 192.0.0.0 │
   └────────────────────┴───────────┘

直接使用字符串不就行了吗?为何多此一举呢?推测原因如下:

出于便捷性的考量,例如 IPv4类型支持格式检查,格式错误的IP数据是无法被写入的
出于性能的考量,同样以 IPv4为例,IPv4使用 UInt32存储,相比String更加紧凑,占用的空间更小,查询性能更快

IPv6类型是基于 FixedString(16)封装的,它的使用方法与 IPv4别无二致

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

七点半的菜市场

四、ClickHouse中数据类型

🏆 Ck的数据类型

🌟 基础数据类型

1️⃣ 数值类型

int 类型

Float类型

正/负无穷

Decimal

2️⃣ 字符串类型

string

FixedString

UUID

3️⃣ 时间类型

DateTime

DateTime64

Date

🌟 复合类型

数组

元组

枚举

嵌套

🌟 复合类型

Nullable

Domain

BaiXie

🏆 Ck的数据类型

🌟 基础数据类型

1️⃣ 数值类型

int 类型

Float类型

正/负无穷

Decimal

2️⃣ 字符串类型

string

FixedString

UUID

3️⃣ 时间类型

DateTime

DateTime64

Date

🌟 复合类型

数组

元组

枚举

嵌套

🌟 复合类型

Nullable

Domain

五、ClickHouse数据库和表操作

三、ClickHouse的基础使用

BaiXie