ClickHouse数据库数据定义手记之数据类型

前边一篇文章详细分析了如何在<code>Windows10</code>系统下搭建<code>ClickHouse</code>的开发环境，接着需要详细学习一下此数据库的数据定义，包括数据类型、<code>DDL</code>和<code>DML</code>。<code>ClickHouse</code>作为一款完备的<code>DBMS</code>，提供了类似于<code>MySQL</code>（其实有部分语法差别还是比较大的）的<code>DDL</code>与<code>DML</code>功能，并且实现了大部分标准<code>SQL</code>规范中的内容。系统学习<code>ClickHouse</code>的数据定义能够帮助开发者更深刻地理解和使用<code>ClickHouse</code>。本文大纲（右侧分支）👇👇

本文会详细分析<code>ClickHouse</code>目前最新版本（<code>20.10.3.30</code>）支持的所有数据类型。

<code>ClickHouse</code>的数据类型从大体的来看主要包括：

数值类型

字符串类型

日期时间类型

复合类型

特殊类型

这里做一份汇总的表格👇

大类

类型

类型名称

一般概念

JavaType

备注

<code>8bit</code>整型

<code>TINYINT</code>

`Byte

Integer`

<code>16bit</code>整型

<code>SMALLINT</code>

`Short

<code>32bit</code>整型

<code>Integer</code>

<code>64bit</code>整型

<code>BIGINT</code>

<code>128bit</code>整型

<code>256bit</code>整型

无符号<code>8bit</code>整型

<code>TINYINT UNSIGNED</code>

<code>Java</code>中不存在无符号整数类型，选择类型时只要不溢出就行

无符号<code>16bit</code>整型

<code>SMALLINT UNSIGNED</code>

无符号<code>32bit</code>整型

<code>INT UNSIGNED</code>

无符号<code>64bit</code>整型

<code>BIGINT UNSIGNED</code>

<code>Float32</code>

<code>32bit</code>单精度浮点数

<code>FLOAT</code>

<code>Float</code>

<code>Float64</code>

<code>64bit</code>双精度浮点数

<code>DOUBLE</code>

<code>Double</code>

<code>Decimal(P,S)</code>

高精度数值，<code>P</code>为总位长，<code>S</code>为小数位长

<code>DECIMAL</code>

<code>BigDecimal</code>

<code>Decimal32(S)</code>

高精度数值，<code>P</code>总位长属于<code>[1,9]</code>，<code>S</code>为小数位长

<code>Decimal(P,S)</code>特化类型

<code>Decimal64(S)</code>

高精度数值，<code>P</code>总位长属于<code>[10,18]</code>，<code>S</code>为小数位长

<code>Decimal128(S)</code>

高精度数值，<code>P</code>总位长属于<code>[19,38]</code>，<code>S</code>为小数位长

<code>String</code>

不定长字符串，长度随意不限

广义上类似<code>LONGTEXT</code>

替代了传统<code>DBMS</code>中的<code>VARCHAR</code>、<code>BLOB</code>、<code>CLOB</code>、<code>TEXT</code>等类型

<code>FixedString(N)</code>

定长字符串，使用<code>null</code>字节填充末尾字符

有点类似<code>VARCHAR</code>

特殊字符串，<code>32</code>位长度，格式为：<code>8-4-4-4-4-12</code>

一般使用内置函数生成

日期

<code>LocalDate</code>

<code>DateTime</code>

日期时间

类似<code>DATE_TIME</code>

`LocalDateTime

OffsetDateTime`

<code>DateTime64</code>

<code>Array(T)</code>

数组

类似<code>T[]</code>

<code>Tuple(S,T...R)</code>

元组

枚举

<code>Nested</code>

嵌套

<code>Nullable</code>

<code>NULL</code>修饰类型，不是独立的数据类型

<code>Domain</code>

域名

存储<code>IPV4</code>和<code>IPV6</code>格式的域名

ClickHouse中类型严格区分大小写，一般为驼峰表示，例如DateTime不能写成DATETIME或者DATE_TIME，同理，UUID不能写成uuid

下面就每种类型再详细分析其用法。

数值类型主要包括整型数值、浮点数值、高精度数值和特殊的布尔值。

整型数值指固定长度（<code>bit</code>数）的整数，可以使用带符号和无符号的表示方式。先看整型数值的表示范围👇👇

带符号整型数值：

字节(byte)数

范围

Int128和Int256能表示的整数范围十分巨大，占用的字节大小也随之增大，一般很少使用。

无符号整型数值：

值得注意的是，UInt128类型并不支持，因此不存在UInt128。UInt256能表示的整数范围十分巨大，占用的字节大小也随之增大，一般很少使用。

一般在使用<code>MySQL</code>的时候会定义一个<code>BIGINT UNSIGNED</code>类型的自增趋势的主键，在<code>ClickHouse</code>中对标<code>UInt64</code>类型。做一个小测试，在<code>ClickHouse</code>命令行客户端中执行：

输出结果：

尴尬，上面的<code>shell</code>执行结果有点长，变形了。

浮点数包括单精度浮点数<code>Float32</code>和双精度浮点数<code>Float64</code>👇👇

字节(byte)大小

有效精度(排除最左边的零小数位数)

小数点后除去左边的零后第<code>8</code>位起会产生数据溢出

小数点后除去左边的零后第<code>17</code>位起会产生数据溢出

可以做一个小测试：

特别地，与标准的<code>SQL</code>相比，<code>ClickHouse</code>支持如下特殊的浮点数类别：

<code>Inf</code> - 表示正无穷

<code>-Inf</code> - 表示负无穷

<code>NaN</code> - 表示不是数字

验证一下：

高精度数值类型<code>Decimal</code>一般又称为为定点数，可以指定总位数和固定位数小数点，表示一定范围内的精确数值。<code>Decimal</code>的原生表示形式为<code>Decimal(P,S)</code>，两个参数的意义是：

<code>P</code>：代表精度，决定总位数（也就是决定整数部分加上小数部分一共有多少位数字），取值范围是<code>[1,76]</code>

<code>S</code>：代表规模（<code>scale</code>），决定小数位数，取值范围是<code>[0,P]</code>

<code>Decimal(P,S)</code>衍生出的简单表示形式有：<code>Decimal32(S)</code>、<code>Decimal64(S)</code>、<code>Decimal128(S)</code>和<code>Decimal256(S)</code>。见下表：

P的取值范围

S的取值范围

数值范围

<code>Decimal256(S)</code>

如果觉得衍生类型不好理解，还是直接使用<code>Decimal(P,S)</code>就行。它的定义格式如下：

对于四则运算，使用两个不同精度的<code>Decimal</code>数值进行（内置函数）运算，运算结果小数位的规则如下（假设<code>S1</code>为左值的小数位，<code>S2</code>为右值的小数位，<code>S</code>为结果小数位）：

对于加法和减法，<code>S = max(S1,S2)</code>

对于乘法，<code>S = S1 + S2</code>

对于除法，<code>S = S1</code>（结果小数位和被除数小数位一致）

重点注意：如果从事的是金融领域等追求准确精度的数值存储，不能使用浮点数，而应该考虑使用整型或者定点数，舍入尽可能交由程序规则处理，毕竟数据库是存储数据的工具，不应该承担太多处理数据计算的职能。

<code>ClickHouse</code>中不存在布尔值类型，官方建议使用<code>UInt8</code>类型，通过值<code>0</code>或<code>1</code>表示<code>false</code>或<code>true</code>。

字符串类型主要包括：

不定长（动态长度）字符串<code>String</code>

固定长度字符串<code>FixedString(N)</code>，这里的<code>N</code>是最大字节数，而不是长度，例如<code>UTF-8</code>字符占用<code>3</code>个字节，<code>GBK</code>字符占用<code>2</code>个字节

特殊字符串<code>UUID</code>（存储的是数值，只是形式是字符串）

<code>ClickHouse</code>中没有编码的概念，字符串可以包含一组任意字节，这些字节按原样存储和输出。这个编码和解码操作推测完全移交给客户端完成。一般情况下，推荐使用<code>UTF-8</code>编码存储文本类型内容，这样就能在不进行转换的前提下读取和写入数据。

<code>String</code>类型不限制字符串的长度，可以直接替代其他<code>DBMS</code>的<code>VARCHAR</code>、<code>BLOB</code>、<code>CLOB</code>等字符串类型，相比<code>VARCHAR</code>这类要考虑预测数据最大长度，显然<code>String</code>无比便捷。使用<code>Java</code>语言开发，直接使用<code>String</code>类型承接即可。<code>String</code>类型的数据列的定义如下：

<code>FixedString</code>类型的数据列的定义如下：

<code>FixedString</code>表示固定长度<code>N</code>的字符串，这里的<code>N</code>代表<code>N</code>个字节（<code>N</code> bytes），而不是<code>N</code>个字符或者<code>N</code>个码点（<code>code point</code>）。一些使用<code>FixedString</code>类型的典型场景：

二进制表示存储<code>IP</code>地址，如使用<code>FixedString(16)</code>存储<code>IPV6</code>地址

哈希值的二进制表示形式，如<code>FixedString(16)</code>存储<code>MD5</code>的二进制值，<code>FixedString(32)</code>存储<code>SHA256</code>的二进制值

当写入<code>FixedString</code>类型数据的时候：

如果数据字节数大于<code>N</code>，则会返回一个<code>Too large value for FixedString(N)</code>的异常

如果数据字节数小于<code>N</code>，则会使用<code>null</code>字节填补剩下的部分

官方文档提示查询条件WHERE中如果需要匹配FixedString类型的列，传入的查询参数要自行补尾部的<code>\0</code>，否则有可能导致查询条件失效。也就是更加建议写入数据和查询条件都是固定字节数的参数。

内置函数<code>length()</code>会直接返回<code>N</code>，而内置函数<code>empty()</code>在全为<code>null</code>字节的前提下会返回<code>1</code>，其他情况返回<code>0</code>。

<code>UUID</code>这个概念很常见，<code>Java</code>中也有静态方法<code>java.util.UUID#randomUUID()</code>直接生成<code>UUID</code>，因为其独特的唯一性有时候可以选择生成<code>UUID</code>作为数据库的主键类型。<code>ClickHouse</code>直接定义了一种<code>UUID</code>类型，严格来说这种类型不是字符串，但是因为在文档上它的位置顺序排在字符串类型之下，日期时间类型之上，形式上看起来也像字符串，并且它仅仅支持字符串类型的内置函数，所以笔者也把它归类为字符串类型。<code>ClickHouse</code>中的<code>UUID</code>实际上是一个<code>16</code>字节的数字，它的通用格式如下：

<code>UUID</code>类型列定义格式如下：

可以通过内置函数<code>generateUUIDv4()</code>直接生成<code>UUID</code>数据，测试一下：

日期时间类型包括<code>Date</code>（表示年月日）、<code>DateTime</code>（表示年月日时分秒）和<code>DateTime64</code>（表示年月日时分秒亚秒）。

<code>Date</code>表示年月日，但是这种类型在<code>ClickHouse</code>中使用<code>2</code>字节（<code>2 byte -> 16 bit</code>）无符号整数去存储距离<code>Unix</code>纪元（<code>1970-01-01</code>）的天数，不支持时区，能够表示的最大年份为<code>2105</code>年。基于这个特性，在插入<code>Date</code>类型数据的时候可以采用<code>yyyy-MM-dd</code>格式或者无符号整数。见下面的测试：

Date类型中的0或者'0000-00-00'代表1970-01-01

<code>DateTime</code>是通常概念中的年月日时分秒，支持时区，但是不支持毫秒表示，也就是此类型精确到秒。它的定义格式为：

可以表示的范围：<code>[1970-01-01 00:00:00, 2105-12-31 23:59:59]</code>。使用<code>DateTime</code>的时候需要注意几点：

<code>DateTime</code>时间点实际上保存为<code>Unix</code>时间戳（笔者探究过这里的单位应该是秒），与时区或者夏时制无关

<code>DateTime</code>的时区并不存储在列数据或者结果集中，而是存储在列元数据中

创建表定义<code>DateTime</code>类型的列的时候如果不指定时区，则使用服务器或者操作系统中设置的默认时区

创建表定义<code>DateTime</code>类型的列的时候如果不指定时区，<code>ClickHouse</code>客户端会使用<code>ClickHouse</code>服务端的时区，也可以通过参数<code>--use_client_time_zone</code>指定

可以通过配置值<code>date_time_input_format</code>或<code>date_time_output_format</code>分别指定<code>DateTime</code>类型数据的输入和输出格式

<code>DateTime</code>类型数据插入的时候，整数会被视为<code>Unix</code>时间戳，并且会使用<code>UTC</code>作为时区（零时区），字符串会被视为使用了时区的日期时间（取决于服务或者系统），再基于时区转化为对应的<code>Unix</code>时间戳进行存储

可以测试一下：

<code>DateTime64</code>其实和<code>DateTime</code>类型差不多，不过可以额外表示亚秒，所谓亚秒，精度就是<code>10 ^ (-n)</code>（<code>10</code>的负<code>n</code>次方）秒，例如<code>0.1</code>秒、<code>0.01</code>秒等等。它的定义格式为：

测试一下：

复合类型主要包括数组<code>Array(T)</code>、元组<code>Tuple(T,S....R)</code>、枚举<code>Enum</code>和嵌套<code>Nested</code>，这里的复合指的是同类型多元素复合或者多类型多元素复合。

数组类型<code>Array(T)</code>中的<code>T</code>可以是任意的数据类型（但是同一个数组的元素类型必须唯一），类似于泛型数组<code>T[]</code>。它的定义如下：

编写测试例子：

需要注意的是：

可以使用<code>array()</code>函数或者<code>[]</code>快速创建数组

快速创建数组时，<code>ClickHouse</code>会自动将参数类型定义为可以存储所有列出的参数的"最窄"的数据类型，可以理解为最小代价原则

<code>ClickHouse</code>无法确定数组的数据类型（常见的是快速创建数组使用了多类型元素），将会返回一个异常（例如<code>SELECT array(1, 'a')</code>是非法的）

如果数组中的元素存在<code>NULL</code>，元素类型将会变为<code>Nullable(T)</code>

元组（<code>Tuple(S,T...R)</code>）类型的数据由<code>1-n</code>个元素组成，每个元素都可以使用单独（可以不相同）的数据类型。它的定义如下：

类似于数组类型<code>Array</code>，元组<code>Tuple</code>对于每个元素的类型推断也是基于最小代价原则

创建表的时候明确元组<code>Tuple</code>中元素的类型定义后，数据写入的时候元素的类型会进行检查，必须一一对应，否则会抛出异常（如<code>x_col Tuple(UInt64, String)</code>只能写入<code>(1,'a')</code>而不能写入<code>('a','b')</code>）

这里可以看出<code>ClickHouse</code>在处理<code>Tuple</code>类型数据写入发现类型不匹配的时候，会尝试进行类型转换，也就是按照写入的数据对应位置的元素类型和列定义<code>Tuple</code>中对应位置的类型做转换（如果类型一致则不需要转换），类型转换异常就会抛出异常。类型为<code>Tuple(UInt64,String)</code>实际上可以写入<code>('111','222')</code>或者<code>(111,'222')</code>，但是不能写入<code>('a','b')</code>。转换过程会调用内置函数，如无意外会消耗额外的性能和时间，因此更推荐在写入数据的时候确保每个位置元素和列定义时候的元素类型一致。

枚举类型<code>Enum</code>算是<code>ClickHouse</code>中独创的复合类型，它使用有限键值对<code>K-V(String:Int)</code>的形式定义数据，有点像<code>Java</code>中的<code>HashMap</code>结构，而<code>KEY</code>和<code>VALUE</code>都不允许<code>NULL</code>值，但是<code>KEY</code>允许设置为空字符串。<code>Enum</code>的数据查询一般返回是<code>KEY</code>的集合，写入可以是<code>KEY</code>也可以是<code>VALUE</code>。它的定义如下：

<code>Enum</code>可以表示的值范围是<code>16</code>位，也就是<code>VALUE</code>只能从<code>[-32768,32767]</code>中取值。它衍生出两种简便的类型<code>Enum8</code>（本质是<code>(String:Int18)</code>，代表值范围是<code>8</code>位，也就是<code>[-128,127]</code>）和<code>Enum16</code>（本质是<code>(String:Int16)</code>，代表值范围是<code>16</code>位，也就是<code>[-32768,32767]</code>），如果直接使用原生类型<code>Enum</code>则会根据实际定义的<code>K-V</code>对数量最终决定具体选用<code>Enum8</code>或是<code>Enum16</code>存储数据。测试一下：

<code>ClickHouse</code>中的<code>Enum</code>本质就是<code>String:Int</code>，特化一个这样的类型，方便定义有限集合的键值对，枚举的<code>VALUE</code>是整型数值，会直接参与<code>ORDER BY</code>、<code>GROUP BY</code>、<code>IN</code>、<code>DISTINCT</code>等操作。按照常规思维来说，排序、聚合、去重等操作使用整型对比使用字符串在性能上应该有不错的提升，所以在使用有限状态集合的场景使用<code>Enum</code>类型比使用<code>String</code>定义枚举集合理论上有天然优势。

嵌套类型<code>Nested</code>算是一种比较奇特的类型。如果使用过<code>GO</code>语言，<code>Nested</code>类型数据列定义的时候有点像<code>GO</code>语言的结构体：

<code>ClickHouse</code>的嵌套类型和固有思维中传统的嵌套类型大有不同，它的本质是一种多维数组结构，可以这样理解：

嵌套类型行与行之间的数组长度无须固定，但是同一行中嵌套表内每个数组的长度必须对齐，例如：

行号

<code>major.id</code>

<code>major.name</code>

[1,2]

['M','N']

[1,2,3]

['M','N','O']

3（异常）

[1,2,3,4]

可以通过<code>ARRAY JOIN</code>子句实现嵌套类型的子表数据平铺，类似于<code>MySQL</code>中的行转列：

特殊类型主要包括<code>Nullable</code>、域名<code>Domain</code>和<code>Nothing</code>。

<code>Nullable</code>不算一种独立的类型，它是一种其他类型的类似辅助修饰符的修饰类型，与其他基本类型搭配使用。如果熟悉<code>Java</code>中的<code>java.lang.Optional</code>，<code>Nullable</code>的功能就是与<code>Optional</code>相似，表示某个基本数据类型可以为<code>Null</code>值（写入时候不传值）。它的定义如下：

需要注意几点：

<code>NULL</code>是<code>Nullable</code>的默认值，也就是<code>INSERT</code>时候可以使用<code>NULL</code>指定空值或者不传值

不能使用<code>Nullable</code>修饰复合数据类型，但是复合数据类型中的元素可以使用<code>Nullable</code>修饰

<code>Nullable</code>修饰的列不能添加索引

官网文档有一段提醒：<code>Nullable</code>几乎总是造成负面的性能影响，在设计数据库的时候必须牢记这一点，这是因为<code>Nullable</code>中的列的<code>NULL</code>值和列的非<code>NULL</code>值会存放在两个不同的文件，所以不能添加索引，查询和写入还会涉及到非单个文件的操作

<code>Domain</code>类型也是<code>ClickHouse</code>独有的类型，是基于其他类型进行封装的一种特殊类型，包括<code>IPv4</code>（本质上是基于<code>UInt32</code>封装，以紧凑的二进制形式存储）和<code>IPv6</code>（本质上是基于<code>FixedString(16)</code>封装）两种类型。它们的定义如下：

<code>Domain</code>类型的局限性：

不能通过<code>ALTER TABLE</code>改变当前<code>Domain</code>类型列的类型

不能通过字符串隐式转换从其他列或者其他表插入<code>Domain</code>类型的列数据，例如<code>A</code>表有<code>String</code>类型存储的<code>IP</code>地址格式的列，无法导入<code>B</code>表中<code>Domain</code>类型的列

<code>Domain</code>类型对存储的值不做限制，但是写入数据的时候会校验是否满足<code>IPv4</code>或者<code>IPv6</code>的格式

此外，<code>Domain</code>类型数据的<code>INSERT</code>或者<code>SELECT</code>都做了人性化格式化操作，所以在使用<code>INSERT</code>语句的时候可以直接使用字符串形式写入，查询的结果虽然在客户端命令行展示的是可读的"字符串"，但是如果想查询到字符串格式的结果需要使用内置函数<code>IPv4NumToString()</code>和<code>IPv6NumToString()</code>（这里也就说明了不支持隐式类型转换，文档中也提到<code>CAST()</code>内置函数可以把<code>IPv4</code>转化为<code>UInt32</code>，把<code>IPv6</code>转化为<code>FixedString(16)</code>）。测试一下：

<code>Nothing</code>不是一种显式的数据类型，它存在的唯一目的就是表示不希望存在值的情况，使用者也无法创建<code>Nothing</code>类型。例如字面量<code>NULL</code>其实是<code>Nullable(Nothing)</code>类型，空的数组<code>array()</code>（内置函数）是<code>Nothing</code>类型。

<code>ClickHouse</code>中所有列定义完毕之后如果没有定义默认值（这个比较复杂，在以后介绍<code>DDL</code>相关的文章的时候再说），如果不使用<code>Nullable</code>，那么写入数据的时候空的列会被填充对应类型的零值。各类型零值归类如下：

数值类型的零值为数字<code>0</code>

字符串类型的零值为空字符串<code>''</code>，<code>UUID</code>的零值为<code>00000000-0000-0000-0000-000000000000</code>

日期时间类型的零值为其存储的时间偏移量的零值

<code>Enum</code>类型是定义的<code>VALUE</code>值最小的为零值

<code>Array</code>类型的零值为<code>[]</code>

<code>Tuple</code>类型的零值为<code>[类型1的零值,类型2的零值......]</code>

<code>Nested</code>类型的零值为多维数组并且每个数组都是<code>[]</code>

特殊地，可以认为<code>Nullable</code>修饰的类型的零值为<code>NULL</code>

这里模拟一个场景，基本上使用所有的<code>ClickHouse</code>中常用的类型。定义一张订单表：

创建完成后，调用<code>DESC ShoppingOrder</code>：

引入<code>clickhouse-jdbc</code>依赖：

编写测试案例：

客户端查询：

实践表明：

<code>ClickHouseDataType</code>中可以查看<code>ClickHouse</code>各种数据类型和<code>Java</code>数据类型以及<code>SQLType</code>之间的对应关系，如<code>UInt64 => BigInteger</code>

<code>ClickHouse</code>的<code>Array</code>类型写入数据的时候可以使用<code>[元素x,元素y]</code>的格式，也可以使用<code>java.sql.Array</code>进行传递，具体是<code>ClickHouseArray</code>，读取数据也可以类似地操作

枚举<code>Enum</code>会直接转换为<code>Java</code>中的<code>String</code>类型

本文已经十分详细分析了<code>ClickHouse</code>的各种数据类型的功能和基本使用例子，下一篇文章将会分析<code>DDL</code>部分。<code>ClickHouse</code>中的很多<code>DDL</code>的用法比较独特，和传统关系型数据库的<code>DDL</code>区别比较大。

Throwable's Blog

（本文完 c-7-d e-a-20201118 最近玩《王国守卫战-复仇》鸽了很久）