ClickHouse（六）：Clickhouse数据类型-1

进入正文前，感谢宝子们订阅专题、点赞、评论、收藏！关注IT贫道，获取高质量博客内容！

🏡个人主页：含各种IT体系技术，IT贫道_Apache Doris,Kerberos安全认证,大数据OLAP体系技术栈-CSDN博客

📌订阅：拥抱独家专题，你的订阅将点燃我的创作热情！

👍点赞：赞同优秀创作，你的点赞是对我创作最大的认可！

⭐️ 收藏：收藏原创博文，让我们一起打造IT界的荣耀与辉煌！

✏️评论：留下心声墨迹，你的评论将是我努力改进的方向！

---

目录

1. Int

​​​​​​​2. Float

​​​​​​​3. Decimal

4. String

5. FixedString

​​​​​​​​​​​​​​6. UUID

​​​​​​​​​​​​​​7. Date

​​​​​​​8. DateTime

​​​​​​​​​​​​​​9. DateTime64

​​​​​​​10. 布尔类型

---

ClickHouse提供了许多数据类型，它们可以划分为基础类型、复合类型和特殊类型。我们可以在system.data_type_families表中检查数据类型名称以及是否区分大小写。这个表中存储了ClickHouse支持的所有数据类型。

 

select * from system.data_type_families limit 10;SELECT *FROM system.data_type_familiesLIMIT 10┌─name────────────┬─case_insensitive─┬─alias_to─┐│ Polygon          │                    0 │            ││ Ring              │                    0 │            ││ MultiPolygon    │                    0 │            ││ IPv6              │                    0 │            ││ IntervalSecond  │                    0 │            ││ IPv4              │                    0 │            ││ UInt32            │                   0 │             ││ IntervalYear     │                   0 │             ││ IntervalQuarter │                   0 │             ││ IntervalMonth    │                   0 │             │└─────────────────┴──────────────────┴──────────┘10 rows in set. Elapsed: 0.004 sec.

下面介绍下常用的数据类型，ClickHouse与Mysql、Hive中常用数据类型的对比图如下：

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	无

1. Int

ClickHouse中整形分为Int8、Int16、Int32、Int64来表示整数不同的取值范围，其末尾数字正好代表占用字节的大小（8位=1字节），整形又包含有符号整形和无符号整形，他们写法上的区别为无符号整形前面加“U”表示。

• 有符号整型范围

类型	字节	范围
Int8	1	[-128:127]
Int16	2	[-32768:32767]
Int32	4	[-2147483648:2147483647]
Int64	8	[-9223372036854775808:9223372036854775807]

• 无符号整形范围：

类型	字节	范围
UInt8	1	[0:255]
UInt16	2	[0:65535]
UInt32	4	[0:4294967295]
UInt64	8	[0:18446744073709551615]

​​​​​​​​​​​​​​2. Float

我们建议使用整数方式来存储数据，因为浮点类型数据计算可能导致四舍五入的误差。浮点类型包含单精度浮点数和双精度浮点数。

• 单精度浮点数

类型	字节	有效精度位数
Float32	4	7

Float32从小数点后第8位起会发生数据溢出。

• 双精度浮点数

类型	字节	有效精度位数
Float64	8	16

Float64从小数点后第17位起会发生数据溢出。

• 示例
  • toFloat32(...) 用来将字符串转换成Float32类型的函数
  • toFloat64(...) 用来将字符串转换成Float64类型的函数

#浮点数有可能导致数据误差node1 :) select 1-0.9SELECT 1 - 0.9┌───────minus(1, 0.9)─┐│ 0.09999999999999998 │└─────────────────────┘1 rows in set. Elapsed: 0.021 sec.#Float32类型，从第8位开始产生溢出，会四舍五入。node1 :) select toFloat32(0.123456789);SELECT toFloat32(0.123456789)┌─toFloat32(0.123456789)─┐│             0.12345679 │└────────────────────────┘1 rows in set. Elapsed: 0.004 sec. # Float64类型，从第17为开始产生数据溢出，会四舍五入node1 :) select toFloat64(0.12345678901234567890);SELECT toFloat64(0.12345678901234568)┌─toFloat64(0.12345678901234568)─┐│            0.12345678901234568 │└────────────────────────────────┘1 rows in set. Elapsed: 0.006 sec.

​​​​​​​3. Decimal

有符号的定点数，可在加、减和乘法运算过程中保持精度。ClickHouse提供了Decimal32、Decimal64、Decimal128、Decimal256几种精度的定点数，支持几种写法：

• Decimal(P,S)
• Decimal32(S),数据范围：(-1*10^(9-S),1*10^(9-S))
• Decimal64(S),数据范围：(-1*10^(18-S),1*10^(18-S))
• Decimal128(S),数据范围：(-1*10^(38-S),1*10^(38-S))
• Decimal256(S),数据范围：(-1*10^(76-S),1*10^(76-S))

其中，P代表精度，决定总位数（整数部分+小数部分），取值范围是1~76。S代表规模，决定小数位数，取值范围是0~P。

根据P值的范围可以有如下对等写法，这里以小数点后2位举例：

P取值	原生写法示例	等同于
[1:9]	Decimal(9,2)	Decimal32(2)
[10:18]	Decimal(18,2)	Decimal64(2)
[19:38]	Decimal(38,2)	Decimal128(2)
[39:76]	Decimal(76,2)	Decimal256(2)

另外，Decimal数据在进行四则运算时，精度（总位数）和规模（小数点位数）会发生变化，具体规则如下：

• 精度（总位数）对应规则：

1. Decimal64(S1) 运算符 Decimal32(S2) -> Decimal64(S)
2. Decimal128(S1) 运算符 Decimal32(S2) -> Decimal128(S）
3. Decimal128(S1) 运算符 Decimal64(S2) -> Decimal128(S)
4. Decimal256(S1) 运算符Decimal<32|64|128>(S2) -> Decimal256(S)

两个不同精度的数据进行四则运算时，结果数据的精度以最大精度为准。

• 规模（小数点位数）对应规则：

1. 加法|减法：S=max(S1,S2)，即以两个数据中小数点位数最多的为准。
2. 乘法：S=S1+S2(注意：S1精度>=S2精度)，即以两个数据的小数位相加为准。
3. 除法：规模以被除数的小数位为准。两数相除，被除数的小数位数不能小于除数的小数位数，也就是触发的规模可以理解为与两个数据中小数点位数大的为准。举例：a/b ，a是被除数，与a的规模保持一致。

• 示例：

1. toDecimal32(value,S):将字符串value转换为Decimal32类型,小数点后有S位。
2. toTypeName(字段)：获取字段的数据类型函数。

#测试加法,S取两者最大的，P取两者最大的
node1 :) selecttoDecimal64(2,3) as x,toTypeName(x) as xtype,toDecimal32(2,2) as y,toTypeName(y) as ytype,x+y as z,toTypeName(z) as ztype;

#测试减法，S取两者最大的，P取两者最大的。
node1 :) selecttoDecimal64(2,3) as x,toTypeName(x) as xtype,toDecimal32(2,2) as y,toTypeName(y) as ytype,x-y as z,toTypeName(z) as ztype;

结果如下：

 

#测试乘法，S取两者最大的，P取两者小数位之和。
node1 :) selecttoDecimal64(2,3) as x,toTypeName(x) as xtype,toDecimal32(2,2) as y,toTypeName(y) as ytype,x*y as z,toTypeName(z) as ztype;

结果如下：

#测试除法，S取两者最大的，P取被除数的小数位数。
node1 :) selecttoDecimal64(2,3) as x,toTypeName(x) as xtype,toDecimal32(2,2) as y,toTypeName(y) as ytype,x/y as z,toTypeName(z) as ztype;

 结果如下：

 

node1 :) select 1-toDecimal64(0.9,1);
SELECT 1 - toDecimal64(0.9, 1)┌─minus(1, toDecimal64(0.9, 1))─┐
│                           0.1       │
└───────────────────────────────┘

注意：在Clickhouse后续的新版本中，整数不再有2.000 小数位，即使保留了对应了小数位也不会保存。小数依然根据创建保留的小数位来保存设置。​​​​​​​

4. String

字符串可以是任意长度的。它可以包含任意的字节集，包含空字节。因此，字符串类型可以代替其他 DBMSs 中的VARCHAR、BLOB、CLOB 等类型。​​​​​​​

5. FixedString

固定长度N的字符串（N必须是严格的正自然数）,一般在明确字符串长度的场景下使用，可以使用下面的语法对列声明为FixedString类型：

# N表示字符串的长度。<column_name>  FixedString(N)

当向ClickHouse中插入数据时,如果字符串包含的字节数少于 N ,将对字符串末尾进行空字节填充。如果字符串包含的字节数大于N,将抛出Too large value for FixedString(N)异常。

当做数据查询时，ClickHouse不会删除字符串末尾的空字节。 如果使用WHERE子句，则须要手动添加空字节以匹配FixedString的值，新版本后期不需要手动添加。

• 示例：
  • toFixedString(value,N)：将字符串转换为N位长度，N不能小于value字符串实际长度。

#查看字符号串长度node1 :) select toFixedString('hello',6) as a,length(a) as alength;SELECTtoFixedString('hello', 6) AS a,length(a) AS alength┌─a─────┬─alength─┐│ hello │       6   │└───────┴─────────┘node1 :) select toFixedString('hello world',6) as a,length(a) as alength;SELECTtoFixedString('hello world', 6) AS a,length(a) AS alengthReceived exception from server (version 20.8.3):Code: 131. DB::Exception: Received from localhost:9000. DB::Exception: String too long for type FixedString(6).

​​​​​​​​​​​​​​6. UUID

UUID是一种数据库常见的主键类型，在ClickHouse中直接把它作为一种数据类型。UUID共有32位，它的格式为8-4-4-4-12，如果在插入新记录时未指定UUID列值，则UUID值将用0来填充（00000000-0000-0000-0000-000000000000）。

UUID类型不支持算术运算、聚合函数sum和avg。

• 示例：
  • generateUUIDv4()随机生成一个32位的UUID。

# 使用mydb库node1 :) use mydb;#创建表t_uuid,指定x列为UUID类型，表引擎为TinyLognode1 :) CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLog#向表 t_uuid中插入一条数据node1 :) INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1';#向表t_uuid中插入一条数据，这里不指定UUID的值，默认会生成0来填充node1 :) INSERT INTO t_uuid (y) VALUES ('Example 2')#查询结果node1 :) select * from t_uuid;SELECT *FROM t_uuid┌────────────────────────────────────x─┬─y─────────┐│ 9c9f82dc-48a0-4749-b46a-cf6a1159c1fe │ Example 1 ││ 00000000-0000-0000-0000-000000000000 │ Example 2 │└──────────────────────────────────────┴───────────┘2 rows in set. Elapsed: 0.003 sec.

​​​​​​​​​​​​​​7. Date

Date只能精确到天，用两个字节存储，表示从1970-01-01（无符号）到当前的日期值。日期中没有存储时区信息，不能指定时区。

• 示例：
  • now() : 获取当前天日期，返回格式：yyyy-MM-dd HH:mm:ss
  • toDate(value) : 将字符串转成Date，只支持yyyy-MM-dd格式。

# 创建表t_datenode1 :) CREATE TABLE t_date (x date) ENGINE=TinyLog;# 向表中插入两条数据node1 :) INSERT INTO t_date VALUES('2021-06-01'),('2021-07-01');# 查询结果node1 :) SELECT x,toTypeName(x) FROM t_date;SELECTx,toTypeName(x)FROM t_date┌──────────x─┬─toTypeName(x)─┐│ 2021-06-01 │ Date            ││ 2021-07-01 │ Date            │└────────────┴───────────────┘2 rows in set. Elapsed: 0.003 sec.# 获取当前天日期时间及日期转换node1 :) select now(),toDate(now()) as d,toTypeName(d) ;SELECTnow(),toDate(now()) AS d,toTypeName(d)┌───────────────now()─┬──────────d─┬─toTypeName(toDate(now()))─┐│ 2021-01-25 18:03:00 │ 2021-01-25 │ Date                          │└─────────────────────┴────────────┴───────────────────────────┘1 rows in set. Elapsed: 0.004 sec.

​​​​​​​8. DateTime

DateTime精确到秒，可以指定时区。用四个字节（无符号的）存储Unix时间戳。允许存储与日期类型相同的范围内的值。最小值为0000-00-00 00:00:00，时间戳类型值精确到秒。

时区使用启动客户端或服务器时的系统时区。默认情况下，客户端连接到服务的时候会使用服务端时区。您可以通过启用客户端命令行选项 --use_client_time_zone 来设置使用客户端时区。

• 示例：
  • toDateTime(DateTimeValue) ：将字符串转成DateTime，只支持yyyy-MM-dd HH:MI:SS。
  • toDateTime(DateTimeValue,时区) ：同上，支持将数据转换为对应时区时间。

# 创建表 t_datetimenode1 :) CREATE TABLE t_datetime(`timestamp` DateTime) ENGINE = TinyLog;# 向表中插入一条数据node1 :) INSERT INTO t_datetime Values('2021-06-01 08:00:00');# 查询数据node1 :) SELECT timestamp,toTypeName(timestamp) as t FROM t_datetime;SELECTtimestamp,toTypeName(timestamp) AS tFROM t_datetime┌───────────timestamp─┬─t────────┐│ 2021-06-01 08:00:00 │ DateTime │└─────────────────────┴──────────┘1 rows in set. Elapsed: 0.003 sec# 转换时区查询node1 :) SELECT  toDateTime(timestamp, 'Asia/Shanghai') AS column, toTypeName(column) AS x  FROM t_datetime;SELECTtoDateTime(timestamp, 'Asia/Shanghai') AS column,toTypeName(column) AS xFROM t_datetime┌──────────────column─┬─x─────────────────────────┐│ 2021-06-01 08:00:00 │ DateTime('Asia/Shanghai') │└─────────────────────┴───────────────────────────┘1 rows in set. Elapsed: 0.003 sec.

​​​​​​​​​​​​​​9. DateTime64

DateTime64精确到毫秒和微秒，可以指定时区。在内部，此类型以Int64类型将数据存储。时间刻度的分辨率由precision参数确定。此外，DateTime64 类型可以像存储其他数据列一样存储时区信息，时区会影响 DateTime64 类型的值如何以文本格式显示，以及如何解析以字符串形式指定的时间数据 (‘2020-01-01 05:00:01.000’)。时区信息不存储在表的行中，而是存储在列的元数据中。

语法如下：

# precision 精度，timezone：时区DateTime64(precision, [timezone])

• 示例：
  • toDateTime64(timeStr,precision):将字符串转成DateTime64，精度为precision。支持yyyy-MM-dd HH:MI:SS.SSS时间格式。
  • toDateTime64(timeStr,precision，timezone):同上，只是可以将时间转换为对应时区时间。

#创建表node1 :) CREATE TABLE dt(`timestamp` DateTime64(3, 'Europe/Moscow'),`event_id` UInt8) ENGINE = TinyLog#插入数据node1 :) INSERT INTO dt Values (1546300800000, 1), ('2019-01-01 00:00:00', 2)，(1546300812345, 3)#查询数据node1 :) select * from dt;SELECT *FROM dt┌───────────────timestamp─┬─event_id─┐│ 2019-01-01 03:00:00.000 │        1   ││ 2019-01-01 00:00:00.000 │        2   ││ 2019-01-01 03:00:12.345 │        3   │└─────────────────────────┴──────────┘3 rows in set. Elapsed: 0.003 sec.#使用toDateTime64转换时间node1 :) select toDateTime64(timestamp,4) as t1,toDateTime64(timestamp,4,'Europe/London') as t2,event_id from dt;SELECTtoDateTime64(timestamp, 4) AS t1,toDateTime64(timestamp, 4, 'Europe/London') AS t2,event_idFROM dt┌───────────────────────t1─┬───────────────────────t2─┬─event_id─┐│ 2019-01-01 03:00:00.0000 │ 2019-01-01 00:00:00.0000 │        1 ││ 2019-01-01 00:00:00.0000 │ 2018-12-31 21:00:00.0000 │        2 ││ 2019-01-01 03:00:12.3450 │ 2019-01-01 00:00:12.3450 │        3 │└──────────────────────────┴──────────────────────────┴──────────┘3 rows in set. Elapsed: 0.003 sec.

​​​​​​​10. 布尔类型

ClickHouse中没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。具体参照枚举类型。

---

👨‍💻如需博文中的资料请私信博主。

---