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 最近玩《王國守衛戰-複仇》鴿了很久）