Clickhouse的数据类型(基础类型、复合类型、其它类型)

1. 基础类型 1.1 数值类型

1. 整形

名称字节范围类型对应Int81-128 ~ 127byteInt162-32768 ~ 32767shortInt324-2147483648 ~ 2147483647intInt648-9223372036854775808 ~ 9223372036854775807bigintUInt810 ~ 255Unsigned byteUInt1620 ~ 65535Unsigned shortUInt3240 ~ 4294967295Unsigned intUInt6480 ~ 18446744073709551615Unsigned bigint

2. 浮点型

名称字节小数点有效位数类型对应Float3247floatFloat64816double

Float32四舍五入保留8位小数，Float64四舍五入保留17位小数

clickhouse1 :)
clickhouse1 :) select toFloat32(0.12345678901234567890123456789) as data, toTypeName(data) as data_type;
┌───────data─┬─data_type─┐
│ 0.12345679 │ Float32   │
└────────────┴───────────┘
clickhouse1 :) 
clickhouse1 :) select toFloat64(0.12345678901234567890123456789) as data, toTypeName(data) as data_type;
┌────────────────data─┬─data_type─┐
│ 0.12345678901234568 │ Float64   │
└─────────────────────┴───────────┘
clickhouse1 :) 

正无穷大inf、负无穷大-inf、非数字nan都属于Float64

clickhouse1 :) 
clickhouse1 :) select (1/0) as data, toTypeName(data) data_type;
┌─data─┬─data_type─┐
│  inf │ Float64   │
└──────┴───────────┘
clickhouse1 :)
clickhouse1 :) select (-1/0) as data, toTypeName(data) data_type;
┌─data─┬─data_type─┐
│ -inf │ Float64   │
└──────┴───────────┘
clickhouse1 :) 
clickhouse1 :) select (0/0) as data, toTypeName(data) data_type;
┌─data─┬─data_type─┐
│  nan │ Float64   │
└──────┴───────────┘
clickhouse1 :)

3. 定点数

简写方式原生方式参数含义数值范围Decimal32(S)Decimal(9, S)整数和小数总位数P = 9, 小数位数 = S(0 ~ 9)-1 * 10 ^ (9 - S) 到 1 * 10 ^ (9 - S)Decimal64(S)Decimal(18, S)整数和小数总位数P = 18, 小数位数 = S(0 ~ 18)-1 * 10 ^ (18 - S) 到 1 * 10 ^ (18 - S)Decimal128(S)Decimal(38, S)整数和小数总位数P = 38, 小数位数 = S(0 ~ 38)-1 * 10 ^ (38 - S) 到 1 * 10 ^ (38 - S)

计算机本身不支持Decimal128(S)，是由软件层面实现的，所以性能低些

进行加法和减法运算，P取最大值，S取最大值

clickhouse1 :) 
clickhouse1 :) select (toDecimal32(2, 4) + toDecimal64(2, 2)) as data, toTypeName(data) data_type;
┌───data─┬─data_type──────┐
│ 4.0000 │ Decimal(18, 4) │
└────────┴────────────────┘
clickhouse1 :) 
clickhouse1 :) select (toDecimal32(4, 4) - toDecimal64(2, 2)) as data, toTypeName(data) data_type;
┌───data─┬─data_type──────┐
│ 2.0000 │ Decimal(18, 4) │
└────────┴────────────────┘
clickhouse1 :) 

进行乘法运算，P取最大值，S取两者之和

clickhouse1 :)
clickhouse1 :) select (toDecimal32(4, 4) * toDecimal64(2, 2)) as data, toTypeName(data) data_type;
┌─────data─┬─data_type──────┐
│ 8.000000 │ Decimal(18, 6) │
└──────────┴────────────────┘
clickhouse1 :) 

进行除法运算，P取最大值，S取除号前面的，且除号前面的S必须大于除号后面的S

clickhouse1 :)
clickhouse1 :) select (toDecimal32(4, 4) / toDecimal64(2, 2)) as data, toTypeName(data) data_type;
┌───data─┬─data_type──────┐
│ 2.0000 │ Decimal(18, 4) │
└────────┴────────────────┘
clickhouse1 :)

1.2 字符串类型

1. String

• 不限制长度
• 不限定字符集，推荐使用UTF-8

2. FixedString(N)

• 长度超过N，会报错；长度不足N, 用null字节填充末尾

clickhouse1 :)
clickhouse1 :) select toFixedString('abc', 5) as a, length(a) as len;
┌─a───┬─len─┐
│ abc │   5 │
└─────┴─────┘
clickhouse1 :)

3. UUID

唯一值，32位，格式位8-4-4-4-12，默认都是0填充

clickhouse1 :) 
clickhouse1 :) create table uuid_table(
:-] col1 UUID,
:-] col2 String
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into uuid_table(col1, col2) select generateUUIDv4(), 'value1';
clickhouse1 :) 
clickhouse1 :) insert into uuid_table(col2) select 'value2';
clickhouse1 :) 
clickhouse1 :) select * from uuid_table;
┌─col1─────────────────────────────────┬─col2───┐
│ e50157b5-c809-411f-bb8d-3870ffa883bb │ value1 │
└──────────────────────────────────────┴────────┘
┌─col1─────────────────────────────────┬─col2───┐
│ 00000000-0000-0000-0000-000000000000 │ value2 │
└──────────────────────────────────────┴────────┘
clickhouse1 :) 

1.3 时间类型

最高精度为亚秒，可使用UInt类型实现毫秒

1. DateTime

clickhouse1 :) 
clickhouse1 :) create table datetime_test(
:-] col1 DateTime
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into datetime_test values('2021-08-02 18:00:00');
clickhouse1 :) 
clickhouse1 :) select col1, toTypeName(col1) as date_type from datetime_test;
┌────────────────col1─┬─date_type─┐
│ 2021-08-02 18:00:00 │ DateTime  │
└─────────────────────┴───────────┘
clickhouse1 :) 

2. DateTime64(S)

S取值为0 ~ 2

clickhouse1 :) 
clickhouse1 :) create table datetime64_test0(
:-] col1 DateTime64(0)
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into datetime64_test0 values('2021-08-02 18:00:00');
clickhouse1 :) 
clickhouse1 :) select col1, toTypeName(col1) as date_type from datetime64_test0;
┌────────────────col1─┬─date_type─────┐
│ 2021-08-02 18:00:00 │ DateTime64(0) │
└─────────────────────┴───────────────┘
clickhouse1 :) 
clickhouse1 :) insert into datetime64_test2 values('2021-08-02 18:00:00');
clickhouse1 :) 
clickhouse1 :) select col1, toTypeName(col1) as date_type from date_test;
┌───────col1─┬─date_type─┐
│ 2021-08-02 │ Date      │
└────────────┴───────────┘
clickhouse1 :) 

3. Date

clickhouse1 :) 
clickhouse1 :) create table date_test(
:-] col1 Date
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into date_test values('2021-08-02');
clickhouse1 :) 
clickhouse1 :) select * from date_test;
┌───────col1─┐
│ 2021-08-02 │
└────────────┘
clickhouse1 :) 

1.4 布尔类型

使用整形0和1实现

2. 复杂类型 2.1 Array(data_type)

clickhouse1 :) 
clickhouse1 :) create table array_test(
:-] col1 Array(UInt32),
:-] col2 Array(Nullable(String))
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into array_test(col1, col2) select array(1, 2.6, 3.4), ['s1', Null];
clickhouse1 :) 
clickhouse1 :) select * from array_test;
┌─col1────┬─col2────────┐
│ [1,2,3] │ ['s1',NULL] │
└─────────┴─────────────┘
clickhouse1 :) 

2.2 Tuple(data_type1, …)

clickhouse1 :) 
clickhouse1 :) create table tuple_test(
:-] col1 Tuple(UInt32, String, Nullable(Nothing)),
:-] col2 Tuple(Float64, DateTime)
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into tuple_test(col1, col2) select tuple(1, '1', Null), (2.2, now());
clickhouse1 :) 
clickhouse1 :) select * from tuple_test;
┌─col1─────────┬─col2────────────────────────┐
│ (1,'1',NULL) │ (2.2,'2021-08-02 19:33:28') │
└──────────────┴─────────────────────────────┘
clickhouse1 :) 

2.3 Enum(key = value, …)

clickhouse1 :) 
clickhouse1 :) create table enum_test(
:-] col1 Enum8('ready' = 1, 'start' = 2, 'success' = 3, 'error' = 4),
:-] col2 Enum16('men' = 1, 'women' = 2, '' = 3)
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into enum_test(col1, col2) select 'success', 'women';
clickhouse1 :) 
clickhouse1 :) insert into enum_test(col1, col2) select 'error', 'men';
clickhouse1 :) 
clickhouse1 :) select * from enum_test where col2 = 'women';
┌─col1────┬─col2──┐
│ success │ women │
└─────────┴───────┘
clickhouse1 :)
clickhouse1 :) select * from enum_test where col2 = 2;
┌─col1────┬─col2──┐
│ success │ women │
└─────────┴───────┘
clickhouse1 :) 

• Enum8的value类型为Int8, Enum16的value类型为Int16
• key唯一，不为Null; value也唯一，不为Null
• 可以不使用Enum类型，而使用String类型；查询的时候也可以只使用key进行操作，但使用value(Int)类型的性能更好

2.4 Nested嵌套类型

clickhouse1 :) 
clickhouse1 :) create table nest_test(
:-] name String,
:-] age UInt8,
:-] dept Nested(
:-] id UInt16,
:-] dept_name String
:-] )
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into nest_test(name, age, dept.id, dept.dept_name) select 'zhang_san', 30, [1001, 1002, 1003], ['商务部', '研发部', '行政部'];
clickhouse1 :) 
clickhouse1 :) insert into nest_test(name, age, dept.id, dept.dept_name) select 'li_si', 40, [1008, 1009], ['营销部', '财务部'];
clickhouse1 :) 
clickhouse1 :) select name, age, dept.dept_name from nest_test;
┌─name──────┬─age─┬─dept.dept_name───────────────┐
│ zhang_san │  30 │ ['商务部','研发部','行政部'] │
└───────────┴─────┴──────────────────────────────┘
┌─name──┬─age─┬─dept.dept_name──────┐
│ li_si │  40 │ ['营销部','财务部'] │
└───────┴─────┴─────────────────────┘
clickhouse1 :) 

• 嵌套字段里面不能含嵌套字段
• 嵌套字段里面的每一个字段都是数组，一条数据的一个嵌套字段所有数组长度必须相同

3. 特殊类型 3.1 Nullable

clickhouse1 :) 
clickhouse1 :) create table null_test(
:-] name String,
:-] age Nullable(UInt8)
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into null_test(name, age) values('name1', 18);
clickhouse1 :) insert into null_test(name, age) values('name2', null);
clickhouse1 :) 
clickhouse1 :) select name, age, toTypeName(age) as age_col_type from null_test;
┌─name──┬─age─┬─age_col_type────┐
│ name1 │  18 │ Nullable(UInt8) │
└───────┴─────┴─────────────────┘
┌─name──┬──age─┬─age_col_type────┐
│ name2 │ ᴺᵁᴸᴸ │ Nullable(UInt8) │
└───────┴──────┴─────────────────┘
clickhouse1 :) 

• 必须和第一部分的基础类型搭配使用，表示这个字段可以为null
• 该字段不能做为索引字段
• 该列保存时，会额外有一个[Column].null.bin文件保存null值，所以该列读写性能变低

3.2 Domain

todo