ClickHouse数据库数据定义手记之数据类型(上)
前提
前边一篇文章详细分析了如何在Windows10系统下搭建ClickHouse的开发环境 接着需要详细学习一下此数据库的数据定义 包括数据类型、DDL和DML。ClickHouse作为一款完备的DBMS 提供了类似于MySQL 其实有部分语法差别还是比较大的 的DDL与DML功能 并且实现了大部分标准SQL规范中的内容。系统学习ClickHouse的数据定义能够帮助开发者更深刻地理解和使用ClickHouse。本文大纲 右侧分支
本文会详细分析ClickHouse目前最新版本 20.10.3.30 支持的所有数据类型。
ClickHouse的数据类型从大体的来看主要包括
数值类型字符串类型日期时间类型复合类型特殊类型这里做一份汇总的表格
ClickHouse中类型严格区分大小写 一般为驼峰表示 例如DateTime不能写成DATETIME或者DATE_TIME 同理 UUID不能写成uuid
下面就每种类型再详细分析其用法。
数值类型主要包括整型数值、浮点数值、高精度数值和特殊的布尔值。
整型数值指固定长度 bit数 的整数 可以使用带符号和无符号的表示方式。先看整型数值的表示范围
带符号整型数值
Int128和Int256能表示的整数范围十分巨大 占用的字节大小也随之增大 一般很少使用。
无符号整型数值
值得注意的是 UInt128类型并不支持 因此不存在UInt128。UInt256能表示的整数范围十分巨大 占用的字节大小也随之增大 一般很少使用。
一般在使用MySQL的时候会定义一个BIGINT UNSIGNED类型的自增趋势的主键 在ClickHouse中对标UInt64类型。做一个小测试 在ClickHouse命令行客户端中执行
SELECT \ toInt8(127) AS a,toTypeName(a) AS aType, \ toInt16(32767) AS b,toTypeName(b) AS bType, \ toInt32(2147483647) AS c,toTypeName(c) AS cType, \ toInt64(9223372036854775807) AS d,toTypeName(d) AS dType, \ toInt128(170141183460469231731687303715884105727) AS e,toTypeName(e) AS eType, \ toInt256(57896044618658097711785492504343953926634992332820282019728792003956564819967) AS f,toTypeName(f) AS fType, \ toUInt8(255) AS g,toTypeName(g) AS gType, \ toUInt16(65535) AS h,toTypeName(h) AS hType, \ toUInt32(4294967295) AS i,toTypeName(i) AS iType, \ toUInt64(18446744073709551615) AS j,toTypeName(j) AS jType, \ toUInt256(115792089237316195423570985008687907853269984665640564039457584007913129639935) AS k,toTypeName(k) AS kType; 复制代码
输出结果
SELECT toInt8(127) AS a, toTypeName(a) AS aType, toInt16(32767) AS b, toTypeName(b) AS bType, toInt32(2147483647) AS c, toTypeName(c) AS cType, toInt64(9223372036854775807) AS d, toTypeName(d) AS dType, toInt128(1.7014118346046923e38) AS e, toTypeName(e) AS eType, toInt256(5.78960446186581e76) AS f, toTypeName(f) AS fType, toUInt8(255) AS g, toTypeName(g) AS gType, toUInt16(65535) AS h, toTypeName(h) AS hType, toUInt32(4294967295) AS i, toTypeName(i) AS iType, toUInt64(18446744073709551615) AS j, toTypeName(j) AS jType, toUInt256(1.157920892373162e77) AS k, toTypeName(k) AS kType ┌───a─┬─aType─┬─────b─┬─bType─┬──────────c─┬─cType─┬───────────────────d─┬─dType─┬────────────────────────────────────────e─┬─eType──┬────────────────────f─┬─fType──┬───g─┬─gType─┬─────h─┬─hType──┬──────────i─┬─iType──┬────────────────────j─┬─jType──┬──────────────────────────────────────────────────────────────────────────────k─┬─kType───┐ │ 127 │ Int8 │ 32767 │ Int16 │ 2147483647 │ Int32 │ 9223372036854775807 │ Int64 │ -170141183460469231731687303715884105728 │ Int128 │ -9223372036854775808 │ Int256 │ 255 │ UInt8 │ 65535 │ UInt16 │ 4294967295 │ UInt32 │ 18446744073709551615 │ UInt64 │ 115792089237316195423570985008687907853269984665640564039448360635876274864128 │ UInt256 │ └─────┴───────┴───────┴───────┴────────────┴───────┴─────────────────────┴───────┴──────────────────────────────────────────┴────────┴──────────────────────┴────────┴─────┴───────┴───────┴────────┴────────────┴────────┴──────────────────────┴────────┴────────────────────────────────────────────────────────────────────────────────┴─────────┘ 1 rows in set. Elapsed: 0.009 sec. 复制代码
尴尬 上面的shell执行结果有点长 变形了。
浮点数包括单精度浮点数Float32和双精度浮点数Float64
可以做一个小测试
f5abc88ff7e4 :) SELECT toFloat32( 0.1234567890 ) AS a,toTypeName(a); SELECT toFloat32( 0.1234567890 ) AS a, toTypeName(a) ┌──────────a─┬─toTypeName(toFloat32( 0.1234567890 ))─┐ │ 0.12345679 │ Float32 │ └────────────┴───────────────────────────────────────┘ 1 rows in set. Elapsed: 0.005 sec. f5abc88ff7e4 :) SELECT toFloat32( 0.0123456789 ) AS a,toTypeName(a); SELECT toFloat32( 0.0123456789 ) AS a, toTypeName(a) ┌───────────a─┬─toTypeName(toFloat32( 0.0123456789 ))─┐ │ 0.012345679 │ Float32 │ └─────────────┴───────────────────────────────────────┘ 1 rows in set. Elapsed: 0.036 sec. f5abc88ff7e4 :) SELECT toFloat64( 0.12345678901234567890 ) AS a,toTypeName(a); SELECT toFloat64( 0.12345678901234567890 ) AS a, toTypeName(a) ┌───────────────────a─┬─toTypeName(toFloat64( 0.12345678901234567890 ))─┐ │ 0.12345678901234568 │ Float64 │ └─────────────────────┴─────────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.005 sec. f5abc88ff7e4 :) SELECT toFloat64( 0.01234567890123456789 ) AS a,toTypeName(a); SELECT toFloat64( 0.01234567890123456789 ) AS a, toTypeName(a) ┌────────────────────a─┬─toTypeName(toFloat64( 0.01234567890123456789 ))─┐ │ 0.012345678901234568 │ Float64 │ └──────────────────────┴─────────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.005 sec. 复制代码
特别地 与标准的SQL相比 ClickHouse支持如下特殊的浮点数类别
Inf - 表示正无穷-Inf - 表示负无穷NaN - 表示不是数字验证一下
f5abc88ff7e4 :) SELECT divide(0.5,0); SELECT 0.5 / 0 ┌─divide(0.5, 0)─┐ │ inf │ └────────────────┘ 1 rows in set. Elapsed: 0.007 sec. f5abc88ff7e4 :) SELECT divide(-0.5,0); SELECT -0.5 / 0 ┌─divide(-0.5, 0)─┐ │ -inf │ └─────────────────┘ 1 rows in set. Elapsed: 0.004 sec. f5abc88ff7e4 :) SELECT divide(0.0,0.0); SELECT 0. / 0. ┌─divide(0., 0.)─┐ │ nan │ └────────────────┘ 1 rows in set. Elapsed: 0.005 sec. 复制代码
高精度数值类型Decimal一般又称为为定点数 可以指定总位数和固定位数小数点 表示一定范围内的精确数值。Decimal的原生表示形式为Decimal(P,S) 两个参数的意义是
Decimal(P,S)衍生出的简单表示形式有 Decimal32(S)、Decimal64(S)、Decimal128(S)和Decimal256(S)。见下表
如果觉得衍生类型不好理解 还是直接使用Decimal(P,S)就行。它的定义格式如下
column_name Decimal(P,S) amount Decimal(10,2) 复制代码
对于四则运算 使用两个不同精度的Decimal数值进行 内置函数 运算 运算结果小数位的规则如下 假设S1为左值的小数位 S2为右值的小数位 S为结果小数位
对于加法和减法 S max(S1,S2)对于乘法 S S1 S2对于除法 S S1 结果小数位和被除数小数位一致f5abc88ff7e4 :) SELECT toDecimal32(2,4) AS x, toDecimal32(2,2) AS y,x SELECT toDecimal32(2, 4) AS x, toDecimal32(2, 2) AS y, ┌──────x─┬────y─┬─plus(toDecimal32(2, 4), toDecimal32(2, 2))─┐ │ 2.0000 │ 2.00 │ 4.0000 │ └────────┴──────┴────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.019 sec. f5abc88ff7e4 :) SELECT toDecimal32(2,4) AS x, toDecimal32(2,5) AS y,y/x SELECT toDecimal32(2, 4) AS x, toDecimal32(2, 5) AS y, y / x ┌──────x─┬───────y─┬─divide(toDecimal32(2, 5), toDecimal32(2, 4))─┐ │ 2.0000 │ 2.00000 │ 1.00000 │ └────────┴─────────┴──────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.004 sec. f5abc88ff7e4 :) SELECT toDecimal32(2,4) AS x, toDecimal32(2,4) AS y,y*x SELECT toDecimal32(2, 4) AS x, toDecimal32(2, 4) AS y, y * x ┌──────x─┬──────y─┬─multiply(toDecimal32(2, 4), toDecimal32(2, 4))─┐ │ 2.0000 │ 2.0000 │ 4.00000000 │ └────────┴────────┴────────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.004 sec. 复制代码
重点注意 如果从事的是金融领域等追求准确精度的数值存储 不能使用浮点数 而应该考虑使用整型或者定点数 舍入尽可能交由程序规则处理 毕竟数据库是存储数据的工具 不应该承担太多处理数据计算的职能。
ClickHouse中不存在布尔值类型 官方建议使用UInt8类型 通过值0或1表示false或true。
字符串类型主要包括
ClickHouse中没有编码的概念 字符串可以包含一组任意字节 这些字节按原样存储和输出。这个编码和解码操作推测完全移交给客户端完成。一般情况下 推荐使用UTF-8编码存储文本类型内容 这样就能在不进行转换的前提下读取和写入数据。
String类型不限制字符串的长度 可以直接替代其他DBMS的VARCHAR、BLOB、CLOB等字符串类型 相比VARCHAR这类要考虑预测数据最大长度 显然String无比便捷。使用Java语言开发 直接使用String类型承接即可。String类型的数据列的定义如下
column_name String 复制代码
FixedString类型的数据列的定义如下
column_name FixedString(N) 复制代码
FixedString表示固定长度N的字符串 这里的N代表N个字节 N bytes 而不是N个字符或者N个码点 code point 。一些使用FixedString类型的典型场景
当写入FixedString类型数据的时候
官方文档提示查询条件WHERE中如果需要匹配FixedString类型的列 传入的查询参数要自行补尾部的\0 否则有可能导致查询条件失效。也就是更加建议写入数据和查询条件都是固定字节数的参数。
内置函数length()会直接返回N 而内置函数empty()在全为null字节的前提下会返回1 其他情况返回0。
UUID这个概念很常见 Java中也有静态方法java.util.UUID#randomUUID()直接生成UUID 因为其独特的唯一性有时候可以选择生成UUID作为数据库的主键类型。ClickHouse直接定义了一种UUID类型 严格来说这种类型不是字符串 但是因为在文档上它的位置顺序排在字符串类型之下 日期时间类型之上 形式上看起来也像字符串 并且它仅仅支持字符串类型的内置函数 所以笔者也把它归类为字符串类型。ClickHouse中的UUID实际上是一个16字节的数字 它的通用格式如下
8-4-4-4-4-12 ## 例子 61f0c404-5cb3-11e7-907b-a6006ad3dba0 ## 零值 00000000-0000-0000-0000-000000000000 复制代码
UUID类型列定义格式如下
column_name UUID 复制代码
可以通过内置函数generateUUIDv4()直接生成UUID数据 测试一下
f5abc88ff7e4 :) CREATE TABLE test_u(id UInt64,u UUID) ENGINE Memory; CREATE TABLE test_u id UInt64, u UUID ENGINE Memory 0 rows in set. Elapsed: 0.018 sec. f5abc88ff7e4 :) INSERT INTO test_u VALUES (1,generateUUIDv4()); INSERT INTO test_u VALUES 1 rows in set. Elapsed: 0.005 sec. f5abc88ff7e4 :) SELECT * FROM test_u; SELECT * FROM test_u ┌─id─┬────────────────────────────────────u─┐ │ 1 │ fc379d2c-0753-45a3-8589-1ef95ee0d8c9 │ └────┴──────────────────────────────────────┘ 1 rows in set. Elapsed: 0.004 sec. 复制代码
日期时间类型包括Date 表示年月日 、DateTime 表示年月日时分秒 和DateTime64 表示年月日时分秒亚秒 。
Date表示年月日 但是这种类型在ClickHouse中使用2字节 2 byte - 16 bit 无符号整数去存储距离Unix纪元 1970-01-01 的天数 不支持时区 能够表示的最大年份为2105年。基于这个特性 在插入Date类型数据的时候可以采用yyyy-MM-dd格式或者无符号整数。见下面的测试
f5abc88ff7e4 :) CREATE TABLE test_dt(date Date) ENGINE Memory; CREATE TABLE test_dt date Date ENGINE Memory 0 rows in set. Elapsed: 0.025 sec. f5abc88ff7e4 :) INSERT INTO dt VALUES(1),(2),( 0000-00-00 ),( 2020-11-11 INSERT INTO dt VALUES Received exception from server (version 20.10.3): Code: 60. DB::Exception: Received from clickhouse-server:9000. DB::Exception: Table default.dt doesn t exist.. 0 rows in set. Elapsed: 0.007 sec. f5abc88ff7e4 :) INSERT INTO test_dt VALUES(1),(2),( 0000-00-00 ),( 2020-11-11 INSERT INTO test_dt VALUES 4 rows in set. Elapsed: 0.025 sec. f5abc88ff7e4 :) SELECT * FROM test_dt; SELECT * FROM test_dt ┌───────date─┐ │ 1970-01-02 │ │ 1970-01-03 │ │ 1970-01-01 │ │ 2020-11-11 │ └────────────┘ 4 rows in set. Elapsed: 0.005 sec. 复制代码
Date类型中的0或者 0000-00-00 代表1970-01-01
DateTime是通常概念中的年月日时分秒 支持时区 但是不支持毫秒表示 也就是此类型精确到秒。它的定义格式为
column_name DateTime[(time_zone)] 复制代码
可以表示的范围 [1970-01-01 00:00:00, 2105-12-31 23:59:59]。使用DateTime的时候需要注意几点
可以测试一下
f5abc88ff7e4 :) CREATE TABLE test_dt(t DateTime,tz DateTime( Asia/Shanghai )) ENGINE Memory; CREATE TABLE test_dt t DateTime, tz DateTime( Asia/Shanghai ) ENGINE Memory 0 rows in set. Elapsed: 0.029 sec. f5abc88ff7e4 :) INSERT INTO test_dt VALUES(1605194721, 2020-11-01 00:00:00 # -------------- 这里的1605194721是北京时间2020-11-12 23:25:21的Unix时间戳 INSERT INTO test_dt VALUES 1 rows in set. Elapsed: 0.006 sec. f5abc88ff7e4 :) SELECT * FROM test_dt; SELECT * FROM test_dt ┌───────────────────t─┬──────────────────tz─┐ │ 2020-11-12 15:25:21 │ 2020-11-01 00:00:00 │ └─────────────────────┴─────────────────────┘ 1 rows in set. Elapsed: 0.005 sec. f5abc88ff7e4 :) SELECT toDateTime(t, Asia/Shanghai ) AS sh_time,toDateTime(tz, Europe/London ) AS lon_time FROM test_dt; SELECT toDateTime(t, Asia/Shanghai ) AS sh_time, toDateTime(tz, Europe/London ) AS lon_time FROM test_dt ┌─────────────sh_time─┬────────────lon_time─┐ │ 2020-11-12 23:25:21 │ 2020-10-31 16:00:00 │ └─────────────────────┴─────────────────────┘ 1 rows in set. Elapsed: 0.004 sec. 复制代码
DateTime64其实和DateTime类型差不多 不过可以额外表示亚秒 所谓亚秒 精度就是10 ^ (-n) 10的负n次方 秒 例如0.1秒、0.01秒等等。它的定义格式为
column_name DateTime64(precision [, time_zone]) 复制代码
测试一下
f5abc88ff7e4 :) SELECT toDateTime64(now(), 5, Asia/Shanghai ) AS column, toTypeName(column) AS x; SELECT toDateTime64(now(), 5, Asia/Shanghai ) AS column, toTypeName(column) AS x ┌────────────────────column─┬─x──────────────────────────────┐ │ 2020-11-12 23:45:56.00000 │ DateTime64(5, Asia/Shanghai ) │ └───────────────────────────┴────────────────────────────────┘ 1 rows in set. Elapsed: 0.005 sec. f5abc88ff7e4 :) CREATE TABLE test_dt64(t DateTime64(2),tz DateTime64(3, Asia/Shanghai )) ENGINE Memory; CREATE TABLE test_dt64 t DateTime64(2), tz DateTime64(3, Asia/Shanghai ) ENGINE Memory 0 rows in set. Elapsed: 0.017 sec. f5abc88ff7e4 :) INSERT INTO test_dt64 VALUES(1605194721, 2020-11-01 00:00:00 INSERT INTO test_dt64 VALUES 1 rows in set. Elapsed: 0.005 sec. f5abc88ff7e4 :) SELECT * FROM test_dt64; SELECT * FROM test_dt64 ┌──────────────────────t─┬──────────────────────tz─┐ │ 1970-07-05 18:52:27.21 │ 2020-11-01 00:00:00.000 │ └────────────────────────┴─────────────────────────┘ 1 rows in set. Elapsed: 0.004 sec. 复制代码
相关文章
- 数据库行业的 “叛逆者”:大数据已“死”,MotherDuck 当立
- 数据库设计中的14个技巧
- ASP.NET实现弹出框真分页将复选框选择的数据存到数据库中(一)
- Python scrapy爬虫数据保存到MySQL数据库
- 发现一款超牛逼的数据库工具,IDEA 公司出品,功能超多,吊炸天。。
- 使用JDBC连接MySQL数据库--典型案例分析(八)----实现员工数据的分页查询
- 83.(后端)商品分类model编写与数据初始化——migrate建立数据库模型与mysql插入数据
- 6.(后端)数据库模型建立与映射
- thinkphp 数据库连接报错 SQLSTATE[HY000] [2002] No such file or directory
- Ajax实现xml文件数据插入数据库(一)--- 构建解析xml文件的js库
- 深入理解 WordPress 数据库中的用户数据 wp_user
- 解析大数据时代的数据库集群技术
- python2.7传数据到数据库OperationalError: (1366, "Incorrect string value")
- python使用插入带有%的字符串到mysql数据库
- linux操作Mysql数据库基本命令
- mysql数据库拷贝
- linux 下备份MySQL数据库 并删除7天前的备份数据
- [Navicat]把1个库的数据迁移到另1个库--数据库备份
- 数据库优化策略小结
- ClickHouse数据库数据定义手记之数据类型(下)
- 数据库建索引
- SpringBoot+Vue+token实现(表单+图片)上传、图片地址保存到数据库。上传图片保存位置自己定义、图片可以在前端回显(一))
- 大数据Spark “蘑菇云”行动第57课: Spark 2.0.1稳定版本解析及广告点击案例数据库和动态黑名单过滤代码
- 京东智联云MySQL数据库如何保障数据的可靠性?
- ORA-12537:TNS:连接关闭 -------数据库最大连接数问题
- sql语句备份/导入 mysql数据库或表命令
- mysql数据库表插入数据
- Cancer数据库 | 数据集 | CRC | oncogene | tumor suppressor gene
- 【Redis】使用 Jedis 操作 Redis 数据库 ② ( Jedis API 规律 | Redis 命令与 Jedis 函数名称基本一致 | Jedis API 使用示例 )
- C# 批量插入表SQLSERVER SqlBulkCopy往数据库中批量插入数据
- 数据库连接池导读 | C++实现