This article is the fifth of the clickhouse column. For more content, please pay attention to this historical article!
First, the data type table
Clickhouse has a lot of built-in column data types, you can get all the supported data types of the version by querying system.data_type_families this table. In the following, the first column is the field type, the second column indicates whether the field type of this type is case-sensitive (1 means case-insensitive, Date and date are both valid data type names), and the third type is the field type. alias.
peer1 :) SELECT * FROM system.data_type_families;
┌─name────────────────────────────┬─case_insensitive─┬─alias_to────┐
│ Polygon │ 0 │ │
│ Ring │ 0 │ │
│ Point │ 0 │ │
│ MultiPolygon │ 0 │ │
│ IPv6 │ 0 │ │
│ IntervalSecond │ 0 │ │
│ IPv4 │ 0 │ │
│ UInt32 │ 0 │ │
│ IntervalYear │ 0 │ │
│ IntervalQuarter │ 0 │ │
│ IntervalMonth │ 0 │ │
│ Int64 │ 0 │ │
│ IntervalDay │ 0 │ │
│ IntervalHour │ 0 │ │
│ UInt256 │ 0 │ │
│ Int16 │ 0 │ │
│ LowCardinality │ 0 │ │
│ AggregateFunction │ 0 │ │
│ Nothing │ 0 │ │
│ Decimal256 │ 1 │ │
│ Tuple │ 0 │ │
│ Array │ 0 │ │
│ Enum16 │ 0 │ │
│ IntervalMinute │ 0 │ │
│ FixedString │ 0 │ │
│ String │ 0 │ │
│ DateTime │ 1 │ │
│ Map │ 0 │ │
│ UUID │ 0 │ │
│ Decimal64 │ 1 │ │
│ Nullable │ 0 │ │
│ Enum │ 0 │ │
│ Int32 │ 0 │ │
│ UInt8 │ 0 │ │
│ Date │ 1 │ │
│ Decimal32 │ 1 │ │
│ UInt128 │ 0 │ │
│ Float64 │ 0 │ │
│ SimpleAggregateFunction │ 0 │ │
│ Nested │ 0 │ │
│ DateTime64 │ 1 │ │
│ Int128 │ 0 │ │
│ Decimal128 │ 1 │ │
│ Int8 │ 0 │ │
│ Decimal │ 1 │ │
│ Int256 │ 0 │ │
│ IntervalWeek │ 0 │ │
│ UInt64 │ 0 │ │
│ Enum8 │ 0 │ │
│ DateTime32 │ 1 │ │
│ UInt16 │ 0 │ │
│ Float32 │ 0 │ │
│ INET6 │ 1 │ IPv6 │
│ INET4 │ 1 │ IPv4 │
│ BINARY │ 1 │ FixedString │
│ NATIONAL CHAR VARYING │ 1 │ String │
│ BINARY VARYING │ 1 │ String │
│ NCHAR LARGE OBJECT │ 1 │ String │
│ NATIONAL CHARACTER VARYING │ 1 │ String │
│ NATIONAL CHARACTER LARGE OBJECT │ 1 │ String │
│ NATIONAL CHARACTER │ 1 │ String │
│ NATIONAL CHAR │ 1 │ String │
│ CHARACTER VARYING │ 1 │ String │
│ LONGBLOB │ 1 │ String │
│ MEDIUMTEXT │ 1 │ String │
│ TEXT │ 1 │ String │
│ TINYBLOB │ 1 │ String │
│ VARCHAR2 │ 1 │ String │
│ CHARACTER LARGE OBJECT │ 1 │ String │
│ DOUBLE PRECISION │ 1 │ Float64 │
│ LONGTEXT │ 1 │ String │
│ NVARCHAR │ 1 │ String │
│ INT1 UNSIGNED │ 1 │ UInt8 │
│ VARCHAR │ 1 │ String │
│ CHAR VARYING │ 1 │ String │
│ MEDIUMBLOB │ 1 │ String │
│ NCHAR │ 1 │ String │
│ CHAR │ 1 │ String │
│ SMALLINT UNSIGNED │ 1 │ UInt16 │
│ TIMESTAMP │ 1 │ DateTime │
│ FIXED │ 1 │ Decimal │
│ TINYTEXT │ 1 │ String │
│ NUMERIC │ 1 │ Decimal │
│ DEC │ 1 │ Decimal │
│ TINYINT UNSIGNED │ 1 │ UInt8 │
│ INTEGER UNSIGNED │ 1 │ UInt32 │
│ INT UNSIGNED │ 1 │ UInt32 │
│ CLOB │ 1 │ String │
│ MEDIUMINT UNSIGNED │ 1 │ UInt32 │
│ BOOL │ 1 │ Int8 │
│ SMALLINT │ 1 │ Int16 │
│ INTEGER SIGNED │ 1 │ Int32 │
│ NCHAR VARYING │ 1 │ String │
│ INT SIGNED │ 1 │ Int32 │
│ TINYINT SIGNED │ 1 │ Int8 │
│ BIGINT SIGNED │ 1 │ Int64 │
│ BINARY LARGE OBJECT │ 1 │ String │
│ SMALLINT SIGNED │ 1 │ Int16 │
│ MEDIUMINT │ 1 │ Int32 │
│ INTEGER │ 1 │ Int32 │
│ INT1 SIGNED │ 1 │ Int8 │
│ BIGINT UNSIGNED │ 1 │ UInt64 │
│ BYTEA │ 1 │ String │
│ INT │ 1 │ Int32 │
│ SINGLE │ 1 │ Float32 │
│ FLOAT │ 1 │ Float32 │
│ MEDIUMINT SIGNED │ 1 │ Int32 │
│ BOOLEAN │ 1 │ Int8 │
│ DOUBLE │ 1 │ Float64 │
│ INT1 │ 1 │ Int8 │
│ CHAR LARGE OBJECT │ 1 │ String │
│ TINYINT │ 1 │ Int8 │
│ BIGINT │ 1 │ Int64 │
│ CHARACTER │ 1 │ String │
│ BYTE │ 1 │ Int8 │
│ BLOB │ 1 │ String │
│ REAL │ 1 │ Float32 │
└─────────────────────────────────┴──────────────────┴─────────────┘2. Basic data types
Like traditional databases, clickhouse provides basic data types. This part will be briefly introduced. I believe that students who have learned programming languages are familiar with this part.
Integer type
Integer type, IntN As N increases, the range of integer values expands. U means unsigned unsigned, unsigned means that there is no negative sign, and only trees greater than or equal to 0 can be stored.
- Signed Integer (IntN): Int8, Int16, Int32, Int64, Int128, Int256. The value range is [-2^(N-1) ~ 2^(N-1)-1], for example: Int8 (-128 to 127)
- Unsigned integer (UIntN): UInt8, UInt16, UInt32, UInt64, UInt128, UInt256. The value range is [0 ~ 2^N-1], for example: UInt8 (0 to 255)
floating point type
- Single-precision floating-point number Float32, friends who have studied C, java, and mysql can use it as a float data type. Loss of precision occurs from the 8th decimal place .
- Double-precision floating-point number Float64, friends who have studied C, java, and mysql can use it as a double data type. Loss of precision occurs from the 17th decimal place .
Decimal type
Sometimes Float32 and Float64 cannot meet the requirements of calculation accuracy, so we need to use the Decimal data type. ClickHouse provides Decimal with Decimal32, Decimal64 and Decimal128 precision.
When defining the type of a table field, it can be declared in two forms: Decimal32(S), Decimal64(S), Decimal128(S) in short form, and Decimal(P, S) in native form, which means the fixed-point number The total length of integer bits plus decimal places is at most P, and the length of decimal places is at most S.
Refer to the following SQL and output results to understand and learn
SELECT
toDecimal64(2, 3) AS x, --数值为2，精度为3，所以2.000
toTypeName(x) AS xtype,
toDecimal32(2, 2) AS y, --数值为2，精度为2，所以2.00
toTypeName(y) as ytype,
x + y AS a, --2.000 + 2.00,加减法取最大精度。所以4.000
toTypeName(a) AS atype,
x * y AS b, --2.000*2.00,乘法的精度值相加（2+3=5）。所以4.00000
toTypeName(b) AS btype,
x / y AS c, --2.000/2.00，被除数2.000精度是3，结果精度等于被除数精度。所以1.000
toTypeName(c) AS ctype; The following figure shows the execution result of the above SQL
Ordinary String type
The String type in clickhouse has no length limit, so it can replace all data types that exist in the form of characters in traditional relational databases, such as: CHAR, VARCHAR, CLOB and so on.
FixedString type
FixedString(N) represents a data type with a fixed length of N. You can use the toFixedString function to convert String to FixedString. The following SQL, although the length of zimug is 5, but the length of FixedString(6) is 6, and the actual storage content is zimug\0 .
peer1 :) select toFixedString('zimug', 6), length(toFixedString('zimug', 6));
┌─toFixedString('zimug', 6)─┬─length(toFixedString('zimug', 6))─┐
│ zimug │ 6 │
└───────────────────────────┴───────────────────────────────────┘UUID
UUID is a relatively common data unique value, and ClickHouse uses it as a data type. The UUID data format is: 6fb875ae-75b9-4643-a146-5a1de7c717b4 , the usage is as follows:
The usage example is as follows:
-- 建表时创建UUID数据类型字段
CREATE TABLE test_uuid (id UUID, content String) ENGINE=TinyLog;
-- 向表中插入数据
INSERT INTO test_uuid SELECT generateUUIDv4(), 'test uuid 1';
-- 插入的时候不为UUID赋值，UUID字段使用0填充
INSERT INTO test_uuid (content) VALUES ('test uuid 2');date type
There are three types of date data: DateTime, DateTime64 and Date, and supports the use of strings to insert the date format. There is also a date type TIMESTAMP, which is used in exactly the same way as the DateTime type, except that it has an alias for the DateTime data type.
- DateTime is accurate to seconds, such as: '2022-06-12 06:06:06'
- DateTime64 is accurate to subseconds, such as: '2022-06-12 06:06:06.000'
- Date does not contain time information, accurate to the day, such as: '2022-06-12'
boolean type
ClickHouse Boolean data type BOOLEAN. Its actual storage is to use the UInt8 type, and the value is limited to 0 or 1.
java
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。