ClickHouse——数据类型

前言

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

咱们能够在system.data_type_families表中检查数据类型名称以及是否区分大小写。

SELECT * FROM system.data_type_families

上面的系统表，存储了ClickHouse所支持的数据类型，注意不一样版本的ClickHouse可能数据类型会有所不一样，具体以下表所示：数据库

┌─name────────────────────┬─case_insensitive─┬─alias_to────┐
│ IPv6                    │                0 │             │
│ IPv4                    │                0 │             │
│ LowCardinality          │                0 │             │
│ Decimal                 │                1 │             │
│ String                  │                0 │             │
│ Decimal64               │                1 │             │
│ Decimal32               │                1 │             │
│ Decimal128              │                1 │             │
│ Float64                 │                0 │             │
│ Float32                 │                0 │             │
│ Int64                   │                0 │             │
│ SimpleAggregateFunction │                0 │             │
│ Array                   │                0 │             │
│ Nothing                 │                0 │             │
│ UInt16                  │                0 │             │
│ Enum16                  │                0 │             │
│ UInt32                  │                0 │             │
│ Date                    │                1 │             │
│ Int8                    │                0 │             │
│ Int32                   │                0 │             │
│ Enum8                   │                0 │             │
│ UInt64                  │                0 │             │
│ IntervalSecond          │                0 │             │
│ Int16                   │                0 │             │
│ FixedString             │                0 │             │
│ Nullable                │                0 │             │
│ AggregateFunction       │                0 │             │
│ DateTime                │                1 │             │
│ Enum                    │                0 │             │
│ Tuple                   │                0 │             │
│ IntervalMonth           │                0 │             │
│ Nested                  │                0 │             │
│ IntervalMinute          │                0 │             │
│ IntervalHour            │                0 │             │
│ IntervalWeek            │                0 │             │
│ IntervalDay             │                0 │             │
│ UInt8                   │                0 │             │
│ IntervalQuarter         │                0 │             │
│ UUID                    │                0 │             │
│ IntervalYear            │                0 │             │
│ LONGBLOB                │                1 │ String      │
│ MEDIUMBLOB              │                1 │ String      │
│ TINYBLOB                │                1 │ String      │
│ BIGINT                  │                1 │ Int64       │
│ SMALLINT                │                1 │ Int16       │
│ TIMESTAMP               │                1 │ DateTime    │
│ INTEGER                 │                1 │ Int32       │
│ INT                     │                1 │ Int32       │
│ DOUBLE                  │                1 │ Float64     │
│ MEDIUMTEXT              │                1 │ String      │
│ TINYINT                 │                1 │ Int8        │
│ DEC                     │                1 │ Decimal     │
│ BINARY                  │                1 │ FixedString │
│ FLOAT                   │                1 │ Float32     │
│ CHAR                    │                1 │ String      │
│ VARCHAR                 │                1 │ String      │
│ TEXT                    │                1 │ String      │
│ TINYTEXT                │                1 │ String      │
│ LONGTEXT                │                1 │ String      │
│ BLOB                    │                1 │ String      │
└─────────────────────────┴──────────────────┴─────────────┘

一、基础类型

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

1.1 数值类型

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

Int类型

固定长度的整数类型又包括有符号和无符号的整数类型。

有符号整数类型
| 类型 | 字节 | 范围 |
| ------ | ------ | ------ |
| Int8 | 1 | [-2^7 ~2^7-1] |
| Int16 | 2 | [-2^15 ~ 2^15-1] |
| Int32 | 4 | [-2^31 ~ 2^31-1] |
| Int64 | 8 | [-2^63 ~ 2^63-1] |
| Int128 | 16 | [-2^127 ~ 2^127-1] |
| Int256 | 32 | [-2^255 ~ 2^255-1] |
无符号类型
| 类型 | 字节 | 范围 |
| ------ | ------ | ------ |
| UInt8 | 1 | [0 ~2^8-1] |
| UInt16 | 2 | [0 ~ 2^16-1] |
| UInt32 | 4 | [0 ~ 2^32-1] |
| UInt64 | 8 | [0 ~ 2^64-1] |
| UInt256 | 32 | [0 ~ 2^256-1] |

浮点类型

单精度浮点数
Float32从小数点后第8位起会发生数据溢出数组

类型	字节	精度
Float32	4	7

双精度浮点数
Float32从小数点后第17位起会发生数据溢出

类型	字节	精度
Float64	8	16

示例

-- Float32类型，从第8为开始产生数据溢出
kms-1.apache.com :) select toFloat32(0.123456789);

SELECT toFloat32(0.123456789)

┌─toFloat32(0.123456789)─┐
│             0.12345679 │
└────────────────────────┘
-- Float64类型，从第17为开始产生数据溢出
kms-1.apache.com :) select toFloat64(0.12345678901234567890);

SELECT toFloat64(0.12345678901234568)

┌─toFloat64(0.12345678901234568)─┐
│            0.12345678901234568 │
└────────────────────────────────┘

Decimal类型

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

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) )ui
Decimal256(S)
数据范围：( -1 10^(76 - S), 1 10^(76 - S) )url

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

根据P的范围，能够有以下的等同写法：

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)

注意点：不一样精度的数据进行四则运算时，精度(总位数)和规模(小数点位数)会发生变化，具体规则以下：

精度对应的规则
- Decimal64(S1) 运算符 Decimal32(S2) -> Decimal64(S)
- Decimal128(S1) 运算符 Decimal32(S2) -> Decimal128(S）
- Decimal128(S1) 运算符 Decimal64(S2) -> Decimal128(S)
- Decimal256(S1) 运算符 Decimal<32|64|128>(S2) -> Decimal256(S)
  能够看出：两个不一样精度的数据进行四则运算时，结果数据已最大精度为准
规模(小数点位数)对应的规则
- 加法|减法：S = max(S1, S2)，即以两个数据中小数点位数最多的为准
- 乘法： S = S1 + S2(注意：S1精度 >= S2精度)，即以两个数据的小数位相加为准
- 除法： S = S1，即被除数的小数位为准

-- toDecimal32(value, S)
  -- 加法,S取二者最大的，P取二者最大的
  SELECT
      toDecimal64(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;
  -- 结果输出
  ┌─────x─┬─xtype──────────┬────y─┬─ytype─────────┬─────z─┬─ztype──────────┐
  │ 2.000 │ Decimal(18, 3) │ 2.00 │ Decimal(9, 2) │ 4.000 │ Decimal(18, 3) │
  └───────┴────────────────┴──────┴───────────────┴───────┴────────────────┘
  -- 乘法，比较特殊，与这两个数的顺序有关
  -- 以下：x类型是Decimal64，y类型是Decimal32，顺序是x*y,小数位S=S1+S2
  SELECT
      toDecimal64(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;
  -- 结果输出   
  ┌─────x─┬─xtype──────────┬────y─┬─ytype─────────┬───────z─┬─ztype──────────┐
  │ 2.000 │ Decimal(18, 3) │ 2.00 │ Decimal(9, 2) │ 4.00000 │ Decimal(18, 5) │
  └───────┴────────────────┴──────┴───────────────┴─────────┴────────────────┘
  -- 交换相乘的顺序，y*x,小数位S=S1*S2
  SELECT
      toDecimal64(2, 3) AS x,
      toTypeName(x) AS xtype,
      toDecimal32(2, 2) AS y,
      toTypeName(y) as ytype,
      y * x AS z,
      toTypeName(z) AS ztype;
  -- 结果输出
  ┌─────x─┬─xtype──────────┬────y─┬─ytype─────────┬────────z─┬─ztype──────────┐
  │ 2.000 │ Decimal(18, 3) │ 2.00 │ Decimal(9, 2) │ 0.400000 │ Decimal(18, 6) │
  └───────┴────────────────┴──────┴───────────────┴──────────┴────────────────┘
  -- 除法，小数位与被除数保持一致
  SELECT
      toDecimal64(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;
  -- 结果输出
  ┌─────x─┬─xtype──────────┬────y─┬─ytype─────────┬─────z─┬─ztype──────────┐
  │ 2.000 │ Decimal(18, 3) │ 2.00 │ Decimal(9, 2) │ 1.000 │ Decimal(18, 3) │
  └───────┴────────────────┴──────┴───────────────┴───────┴────────────────┘

1.2 字符串类型

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

String

字符串由String定义，长度不限。因此在使用String的时候无须声明大小。它完全代替了传统意义上数据库的Varchar、Text、CLOB 和BLOB等字符类型。String类型不限定字符集，因为它根本就没有这个概念，所以可以将任意编码的字符串存入其中。

FixedString

固定长度的N字节字符串，通常在在一些明确字符串长度的场景下使用，声明方式以下：

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

值得注意的是：FixedString使用null字节填充末尾字符。

-- 虽然hello只有5位，但最终字符串是6位
select toFixedString('hello',6) as a,length(a) as alength;
-- 结果输出

┌─a─────┬─alength─┐
│ hello │       6 │
└───────┴─────────┘
-- 注意对于固定长度的字符串进行比较时，会出现不同的结果
-- 以下：因为a是6位字符，而hello是5位，因此二者不相等
select toFixedString('hello',6) as a,a = 'hello' ,length(a) as alength;    
-- 结果输出
┌─a─────┬─equals(toFixedString('hello', 6), 'hello')─┬─alength─┐
│ hello │                                          0 │       6 │
└───────┴────────────────────────────────────────────┴─────────┘

-- 须要使用下面的方式
select toFixedString('hello',6) as a,a = 'hello\0' ,length(a) as alength;
-- 结果输出

┌─a─────┬─equals(toFixedString('hello', 6), 'hello\0')─┬─alength─┐
│ hello │                                            1 │       6 │
└───────┴──────────────────────────────────────────────┴─────────┘

UUID

UUID是一种数据库常见的主键类型，在ClickHouse中直接把它做为一种数据类型。UUID共有32位，它的格式为8-4-4-4-12，好比：

61f0c404-5cb3-11e7-907b-a6006ad3dba0
-- 当不指定uuid列的值时，填充为0
00000000-0000-0000-0000-000000000000

使用示例以下：

-- 建表
CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLog;
-- insert数据
INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1';
INSERT INTO t_uuid (y) VALUES ('Example 2');
SELECT * FROM t_uuid;
-- 结果输出，默认被填充为0
┌────────────────────────────────────x─┬─y─────────┐
│ b6b019b5-ee5c-4967-9c4d-8ff95d332230 │ Example 1 │
│ 00000000-0000-0000-0000-000000000000 │ Example 2 │
└──────────────────────────────────────┴───────────┘

1.3 时间类型

时间类型分为DateTime、DateTime64和Date三类。须要注意的是ClickHouse目前没有时间戳类型，也就是说，时间类型最高的精度是秒，因此若是须要处理毫秒、微秒精度的时间，则只能借助UInt类型实现。

Date类型

用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。日期中没有存储时区信息。

CREATE TABLE t_date (x date) ENGINE=TinyLog;
INSERT INTO t_date VALUES('2020-10-01');
SELECT x,toTypeName(x) FROM t_date;
┌──────────x─┬─toTypeName(x)─┐
│ 2020-10-01 │ Date          │
└────────────┴───────────────┘

DateTime类型

用四个字节（无符号的）存储 Unix 时间戳。容许存储与日期类型相同的范围内的值。最小值为 0000-00-00 00:00:00。时间戳类型值精确到秒（没有闰秒）。时区使用启动客户端或服务器时的系统时区。

CREATE TABLE t_datetime(`timestamp` DateTime) ENGINE = TinyLog;
INSERT INTO t_datetime Values('2020-10-01 00:00:00');
SELECT * FROM t_datetime;
-- 结果输出
┌───────────timestamp─┐
│ 2020-10-01 00:00:00 │
└─────────────────────┘
-- 注意，DateTime类型是区分时区的
SELECT 
    toDateTime(timestamp, 'Asia/Shanghai') AS column,
    toTypeName(column) AS x 
FROM t_datetime;
-- 结果输出
┌──────────────column─┬─x─────────────────────────┐
│ 2020-10-01 00:00:00 │ DateTime('Asia/Shanghai') │
└─────────────────────┴───────────────────────────┘
SELECT
     toDateTime(timestamp, 'Europe/Moscow') AS column,
     toTypeName(column) AS x 
FROM t_datetime; 
-- 结果输出
┌──────────────column─┬─x─────────────────────────┐
│ 2020-09-30 19:00:00 │ DateTime('Europe/Moscow') │
└─────────────────────┴───────────────────────────┘

DateTime64

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

Datetime64 接受年-月-日时:分:秒.亚秒的字符串比如‘2019-12-16 20:50:10.66’

布尔类型

ClickHouse没有单独的类型来存储布尔值。可使用UInt8 类型，取值限制为0或 1。

二、复合类型

ClickHouse还提供了数组、元组、枚举和嵌套四类复合类型。

2.1 数组类型

Array(T)，由 T 类型元素组成的数组。T 能够是任意类型，包含数组类型。但不推荐使用多维数组，ClickHouse对多维数组的支持有限。例如，不能在MergeTree表中存储多维数组。

SELECT array(1, 2) AS x, toTypeName(x);
-- 结果输出
┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8)            │
└───────┴─────────────────────────┘
SELECT [1, 2] AS x, toTypeName(x);
-- 结果输出
┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8)       │
└───────┴────────────────────┘

须要注意的是，数组元素中若是存在Null值，则元素类型将变为Nullable。

SELECT array(1, 2, NULL) AS x, toTypeName(x);
-- 结果输出
┌─x──────────┬─toTypeName(array(1, 2, NULL))─┐
│ [1,2,NULL] │ Array(Nullable(UInt8))        │
└────────────┴───────────────────────────────┘

另外，数组类型里面的元素必须具备相同的数据类型，不然会报异常

SELECT array(1, 'a')
-- 报异常
DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString and some of them are not

2.2 枚举类型

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

-- 建表
CREATE TABLE t_enum
(
    x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog;
-- INSERT数据
INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello');
-- 若是定义了枚举类型值以后，不能写入其余值的数据
INSERT INTO t_enum values('a')
-- 报异常：Unknown element 'a' for type Enum8('hello' = 1, 'world' = 2)

2.3 Tuple类型

Tuple(T1, T2, ...)，元组，与Array不一样的是，Tuple中每一个元素都有单独的类型，不能在表中存储元组（除了内存表）。它们能够用于临时列分组。在查询中，IN表达式和带特定参数的 lambda 函数能够来对临时列进行分组。

SELECT tuple(1,'a') AS x, toTypeName(x);
--结果输出
┌─x───────┬─toTypeName(tuple(1, 'a'))─┐
│ (1,'a') │ Tuple(UInt8, String)      │
└─────────┴───────────────────────────┘
-- 建表
CREATE TABLE t_tuple(
   c1 Tuple(String,Int8)
) engine=TinyLog;
-- INSERT数据
INSERT INTO t_tuple VALUES(('jack',20));
--查询数据
SELECT * FROM t_tuple;
┌─c1──────────┐
│ ('jack',20) │
└─────────────┘
-- 若是插入数据类型不匹配，会报异常
INSERT INTO t_tuple VALUES(('tom','20'));
-- Type mismatch in IN or VALUES section. Expected: Int8. Got: String

2.4 嵌套Nested

嵌套类型，顾名思义是一种嵌套表结构。一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系，使用嵌套类型也是一种不错的选择。

--创建Nested语句
CREATE TABLE nested_test (
    name String,
    age  UInt8 ,
    dept Nested(
        id UInt8,
        name String
    )
) ENGINE = Memory;

ClickHouse的嵌套类型和传统的嵌套类型不相同，导致在初次接触它的时候会让人十分困惑。以上面这张表为例，如果按照它的字面意思来理解，会很容易理解成nested_test与dept是一对一的包含关系，其实这是错误的。

嵌套类型本质是一种多维数组的结构。嵌套表中的每个字段都是一个数组，并且行与行之间数组的长度无须对齐，在同一行数据内每个数组字段的长度必须相等。

插入数据时候每一个nestd字段要需要一个数组。

--插入数据
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─────────────┐
│ bruce │ [16,17,18] │ ['研发部','技术支持中心','测试部'] │
└────┴───────┴────────────────────┘

三、特殊数据类型

3.1 Nullable

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

CREATE TABLE Null_TEST (
    c1 String,
    c2 Nullable(UInt8)
) ENGINE = TinyLog;
--通过Nullable修饰后c2字段可以被写入Null值：
INSERT INTO Null_TEST VALUES ('nauu',null)
INSERT INTO Null_TEST VALUES ('bruce',20)
SELECT c1 , c2 ,toTypeName(c2) FROM Null_TEST
┌─c1───┬───c2─┬─toTypeName(c2)─┐
│ nauu   │ NULL    │ Nullable(UInt8) │
│ bruce  │ 20      │ Nullable(UInt8) │
└─────┴──────┴───────────┘

3.2 Domain

Domain类型是特定实现的类型：

IPv4是与UInt32类型保持二进制兼容的Domain类型，用于存储IPv4地址的值。它提供了更为紧凑的二进制存储的同时支持识别可读性更加友好的输入输出格式。

IPv6是与FixedString(16)类型保持二进制兼容的Domain类型，用于存储IPv6地址的值。它提供了更为紧凑的二进制存储的同时支持识别可读性更加友好的输入输出格式。

注意低版本的ClickHouse不支持此类型。

-- 建表
CREATE TABLE hits
(url String, 
 from IPv4
) ENGINE = MergeTree()
ORDER BY from;
-- 写入数据
INSERT INTO hits (url, from) VALUES
('https://wikipedia.org', '116.253.40.133')
('https://clickhouse.tech', '183.247.232.58');

-- 查询
SELECT * FROM hits;
┌─url─────────────────────┬───────────from─┐
│ https://wikipedia.org   │ 116.253.40.133 │
│ https://clickhouse.tech │ 183.247.232.58 │
└─────────────────────────┴────────────────┘

参考：
https://clickhouse.com/docs/zh/sql-reference/data-types/

https://www.cnblogs.com/wdh01/p/16859758.html

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

http://www.javashuo.com/article/p-rxqsytpe-ve.html