data type

integer

Fixed length integers , Including signed or unsigned integers .

Integer range (-2 Of n-1 Second party to 2 Of n-1 Power -1):

Int8 - [-128 : 127] , Corresponding java Of byte

Int16 - [-32768 : 32767], Corresponding java Of short

Int32 - [-2147483648 : 2147483647], Corresponding java Of int

Int64 - [-9223372036854775808 : 9223372036854775807], Corresponding java Of long

Unsigned integer range (0~2 Of n Power -1):

UInt8 - [0 : 255]

UInt16 - [0 : 65535]

UInt32 - [0 : 4294967295]

UInt64 - [0 : 18446744073709551615]

Use scenarios : Number 、 Number 、 It can also be storage type id.

floating-point

Float32 - float

Float64 – double

It is recommended to store data in integer form as much as possible . for example , Convert fixed precision numbers to integer values , For example, time is expressed in milliseconds , Because floating-point calculation may cause rounding error .

Use scenarios : Generally, the data value is relatively small , It does not involve a large number of statistical calculations , When the accuracy requirement is not high . For example, keep the weight of goods .

Boolean type

There is no separate type for storing Boolean values . have access to UInt8 type , The value is limited to 0 or 1.

Decimal type

Signed floating point numbers , You can add 、 To maintain precision in subtraction and multiplication . For division , The least significant number will be discarded ( No rounding ). Floating point numbers are saved as Decimal type , Like money .

There are three kinds of statements :

• Decimal32(s), amount to Decimal(9-s,s), The number of significant digits is 1~9, for example Decimal32(5) Indicates that the integer part has 4 position , The fractional part has 5 position , And whether the truncation is rounded , Direct truncation
• Decimal64(s), amount to Decimal(18-s,s), The number of significant digits is 1~18
• Decimal128(s), amount to Decimal(38-s,s), The number of significant digits is 1~38

s Identify decimal places

Use scenarios : General amount field 、 exchange rate 、 Interest rate and other fields to ensure decimal point accuracy , All use Decimal For storage .

character string

1)String

Strings can be of any length . It can contain any set of bytes , Contains empty bytes .

2)FixedString(N)

Fixed length N String ,N It must be strictly positive natural numbers . When the server read length is less than N When the string of , By at the end of the string Add empty bytes In order to achieve N bytes . When the server read length is greater than N When the string of , Will return an error message .

And String comparison , Rarely used FixedString, Because it's not very convenient to use .

Use scenarios : name 、 A word description 、 Character encoding . Fixed length can save some fixed length content , For example, some coding , Gender, etc., but considering a certain risk of change , The benefits are not obvious enough , Therefore, the use of fixed length strings is of limited significance .

Enumeration type

Include Enum8 and Enum16 type .Enum preservation ‘string’= integer Correspondence of .

Enum8 use ‘String’= Int8 Description of .

Enum16 use ‘String’= Int16 Description of .

Usage demonstration

Create one with an enumeration Enum8(‘hello’ = 1, ‘world’ = 2) Column of type

CREATE TABLE t_enum
(
	x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog;

This x Columns can only store values listed in the type definition :‘hello’ or ’world’

hadoop113 :) INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello');

And then query

hadoop113 :) select * from t_enum;

SELECT *
FROM t_enum

Query id: 99231044-3186-4734-9b11-26c81083e054

┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘

3 rows in set. Elapsed: 0.006 sec. 

If you try to save any other values ,ClickHouse Throw an exception

hadoop113 :) insert into t_enum values('a')
...
Code: 36. DB::Exception: Unknown element 'a' for enum: data for INSERT was parsed from query
...

If you need to see the value of the corresponding line , You have to Enum Convert value to integer type

hadoop113 :) SELECT CAST(x, 'Int8') FROM t_enum;
┌─CAST(x, 'Int8')─┐
│               1 │
│               2 │
│               1 │
└─────────────────┘

Use scenarios : For some states 、 Type of field is a kind of spatial optimization , It's also a kind of data constraint . However, in actual use, the maintenance cost is often increased due to the change of some data content , Even data loss . So use... With caution .

Time type

at present ClickHouse There are three types of time

* Date Accept years - month - Japan String, such as ‘2019-12-16’

* Datetime Accept years - month - Japan when : branch : second String, such as ‘2019-12-16 20:50:10’

* Datetime64 Accept years - month - Japan when : branch : second . Subsecond String, such as ‘2019-12-16 20:50:10.66’

The date type , Store in two bytes , From 1970-01-01 ( Unsigned ) To the current date value .

Array

Array(T): from T An array of type elements .

T It can be any type , Contains array types . but Multidimensional arrays are not recommended ,ClickHouse Limited support for multidimensional arrays . for example , Can't be in MergeTree Tables store multidimensional arrays .

/*  How to create an array  1, Use  array  function array(T), among toTypeName Is to get the type of column to  */
hadoop102 :) SELECT array(1, 2) AS x, toTypeName(x);
┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8)            │
└───────┴─────────────────────────┘

/*  How to create an array  2: Use square brackets [] */
hadoop102 :) SELECT [1, 2] AS x, toTypeName(x);
┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8)            │
└───────┴─────────────────────────┘

Null value Nullable

It is not recommended to use Nullable, Because it almost always has a negative impact on performance , Therefore, if you have to save a null value when designing the database , It is recommended to convert to a special value of the corresponding type , for example Int Type of -1 etc. , Can not use Nulltable You don't have to .

There are also many data structures , Please refer to the official documents :https://clickhouse.yandex/docs/zh/data_types/