数据类型
数据模型确定了,在创建表之前,还需要知道MatrixDB包含哪些数据类型。因为表是由列组成,每个列都需要有确定的类型。
MatrixDB支持的数据类型包括如下几类:
- 基础数据类型
- 数值
- 字符串
- 日期和时间
- 扩展数据类型
- json/jsonb
- mxkv
- 空间数据类型
1. 基础数据类型
1.1 数值
类型 | 别名 | 大小 | 范围 | 说明 |
---|---|---|---|---|
smallint | int2 | 2字节 | -32768 ~ 32767 | 小范围整数 |
int | int4 | 4字节 | -2147483648 ~ 2147483647 | 整数类型 |
bigint | int8 | 8字节 | -9223372036854775808 ~ 9223372036854775807 | 大范围整数 |
serial | serial4 | 4字节 | 1 ~ 2147483647 | 自增型整数 |
bigserial | serial8 | 8字节 | 1 ~ 9223372036854775807 | 大范围自增型整数 |
real | float4 | 4字节 | 6位精度 | 可变精度 |
double precision | float8 | 8字节 | 15位精度 | 可变精度 |
decimal | numeric | 可变 | 无限制 | 固定精度 |
从表格可以看出,MatrixDB的数值类型分4类:
- 整数:不同大小表示范围不同,并且都是有符号数
- smallint
- int
- bigint
- 定点数:可以表示小数点位数固定的数
- decimal
- 浮点数:可以表示小数点位数不固定的数
- real
- double precision
- 自增类型:用于自增ID列
- serial
- bigserial
1.2 字符串
类型 | 别名 | 大小 | 范围 | 说明 |
---|---|---|---|---|
character [ (n) ] | char [ (n) ] | 1 字节 + n | 最长 n 个字符的字符串 | 定长字符串,未指定的字符用空格填充 |
character varying [ (n) ] | varchar [ (n) ] | 1 字节 + 字符串大小 | 最长 n 个字符的字符串 | 有长度限制的变长字符串,上限10485760字节 |
text | 1 字节 + 字符串长度 | 任意长度字符串 | 无长度限制的变长字符串 |
从表格可以看出,MatrixDB字符串类型分2类:
- 定长:char
- 变长
- varchar:有长度限制
- text:无长度限制
1.3 日期和时间
类型 | 别名 | 大小 | 范围 | 说明 |
---|---|---|---|---|
date | 4字节 | 4713 BC ~ 294,277 AD | 日历日期 (年, 月, 日) | |
time [ (p) ] [ without time zone ] | 8 字节 | 00:00:00[.000000] ~ 24:00:00[.000000] | 一天内的时间 | |
time [ (p) ] with time zone | timetz | 12 字节 | 00:00:00+1359 ~ 24:00:00-1359 | 一天内的时间, 带时区 |
timestamp [ (p) ] [ without time zone ] | 8 字节 | 4713 BC ~ 294,277 AD | 日期和时间 | |
timestamp [ (p) ] with time zone | timestamptz | 8 字节 | 4713 BC ~ 294,277 AD | 日期和时间, 带时区 |
从表格可以看出,MatrixDB的日期时间类型分为3种:
- 日期:仅能表示年-月-日,表示不了时间
- date
- 时间:仅能表示时间,表示不了日期
- time [without time zone](不带时区)
- time with time zone(带时区)
- 日期和时间:既能表示日期又能表示时间
- timestamp [without time zone](不带时区)
- timestamp with time zone(带时区)
时区对时间类型的影响
从时间类型中可以看到一个特殊信息time zone即时区。不指定的话默认为without time zone,即无时区。那么时区对于时间类型有什么影响呢?下面我们来创建一张测试表来演示说明。
CREATE TABLE test (
c1 int,
c2 timestamp,
c3 timestamp with time zone
)DISTRIBUTED BY (c1);
测试表除了分布键外包含了两个时间戳列,一个默认不带时区,一个带时区。然后插入测试数据并查询:
INSERT INTO test VALUES(1, now(), now());
SELECT * FROM test;
c1 | c2 | c3
----+--------------------------+-----------------------------
1 | 2021-12-01 14:54:09.4783 | 2021-12-01 14:54:09.4783+08
(1 row)
SHOW timezone;
TimeZone
---------------
Asia/Shanghai
(1 row)
从查询结果可以看到,两个时间戳列显示的时间是一样,带时区的列后面有一个+08,表示该时间戳所属时区为东八区。然后通过SHOW timezone命令看到了当前时区为'Asia/Shanghai',即北京时间。
下面我们修改时区再查看结果:
SET timezone TO 'Japan';
SELECT * FROM test;
c1 | c2 | c3
----+--------------------------+-----------------------------
1 | 2021-12-01 14:54:09.4783 | 2021-12-01 15:54:09.4783+09
(1 row)
可以看到,当把数据库时区改到日本后,带时区的c3列显示的时间已经和c2不一样了,并且后面时区信息变成了+09,即东九区。
所以,带时区的时间戳列存储的时间是有时区信息的,会在不同的时区下显示不同的时间;而不带时区的时间戳列则在任何时区下显示的时间都是相同的。当设备部署在不同时区的地域时,则时间戳必须要有时区信息。对于timestamp类型有无时区占用存储空间相同,所以建议加上。
时序场景基础数据类型使用建议
时序场景数据可以分为如下几类:
- 时间戳:指标采集时间既要包括日期,还要包括时间,所以建议使用timestamp类型,并带时区
- 设备标识
- 如果是使用字符串标识设备则建议使用varchar和text。char因为长度不可变,适合于存储定长的设备编号信息,如序列号
- 如果为设备单独创建设备表,可使用serial或bigserial做自增ID,来唯一标识设备
- 采集指标:通常为数值可根据取值范围和是否有小数来确定类型
- 如果采集结果为整数,则选择整型数据类型,根据结果范围确定是使用smallint、int还是bigint
- 如果采集结果带小数,并且小数点精确的位数固定,取值范围也相对固定,例如温度,适合使用decimal
- 如果采集结果带小数,并且小数点精确的位数不固定,取值范围较大,则需要根据精度来选择float或double precision
2. 扩展数据类型
在关系模型中,基础数据类型,每个列表示一个属性的值,如:姓名、身高、体重。但是面临如下挑战:
- 无法预知指标集,即表schema可能要经常变
- 指标集合过大,超过了MatrixDB表最多1600列的列数限制
这个时候,就需要使用扩展数据类型。所谓的扩展数据类型,就是可以在单个列中存储多个属性。
实现扩展数据类型的方式有很多,比如可以自定义序列化存储格式和编码方式,然后存储到字符串类型中。MatrixDB则提供了两种扩展数据类型:
- json/jsonb
- mxkv
2.1 json/jsonb
json/jsonb类型继承自PostgreSQL数据库,和直接用字符串类型的列保存json串不同,json/jsonb类型提供了常用的json操作函数,用于读取key值,以及合并、删除等操作。
json与jsonb使用方法基本一致,也可以直接相互转换,区别是:
- json保存为文本,jsonb保存为二进制
- json写入时不需要做二进制转换,写入速度更快一些
- jsonb因为做了二进制转存,解析速度更快一些
下面给出了常用的json/jsonb类型操作方法:
使用->提取key值
SELECT '{"a":1, "b":2, "c":3}'::json->'a';
?column?
----------
1
(1 row)
如果json/jsonb层级是嵌套的,多次使用->即可:
SELECT '{"a":1, "b":2, "c":{"d":2}}'::json->'c'->'d';
?column?
----------
2
(1 row)
删除key
使用 - 号即可删除key。在对json对象做写操作时必须将其转换为jsonb类型:
SELECT '{"a":1, "b":2, "c":3}'::jsonb - 'a';
?column?
------------------
{"b": 2, "c": 3}
(1 row)
合并
合并操作可以将两个jsonb类型的key合并到一起,使用 || 操作符:
SELECT '{"a":1, "b":2}'::jsonb || '{"c":3}'::jsonb;
?column?
--------------------------
{"a": 1, "b": 2, "c": 3}
(1 row)
更新
|| 操作符在key值相同的时候,会对value做更新:
SELECT '{"a":1, "b":2}'::jsonb || '{"b":3}'::jsonb;
?column?
------------------
{"a": 1, "b": 3}
(1 row)
更多的json操作方法,请参考PostgreSQL文档。
2.2 mxkv
mxkv是MatrixDB自研的高效可扩展存储类型,使用方法和json类似,和json相比的优势是:mxkv是二进制存储,并且进行了压缩,存储空间更小,查询效率更高。
3. 空间数据类型
MatrixDB 4.3开发了增强版的PostGIS组件,支持空间数据类型的存储和计算。具体使用方法请参考文档。