数据类型

数据模型确定了,在创建表之前,还需要知道MatrixDB包含哪些数据类型。因为表是由列组成,每个列都需要有确定的类型。

MatrixDB支持的数据类型包括如下几类:

  • 基础数据类型
    1. 数值
    2. 字符串
    3. 日期和时间
  • 扩展数据类型
    1. json/jsonb
    2. 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类:

  • 整数:不同大小表示范围不同,并且都是有符号数
    1. smallint
    2. int
    3. bigint
  • 定点数:可以表示小数点位数固定的数
    1. decimal
  • 浮点数:可以表示小数点位数不固定的数
    1. real
    2. double precision
  • 自增类型:用于自增ID列
    1. serial
    2. bigserial

1.2 字符串

类型 别名 大小 范围 说明
character [ (n) ] char [ (n) ] 1 字节 + n 最长 n 个字符的字符串 定长字符串,未指定的字符用空格填充
character varying [ (n) ] varchar [ (n) ] 1 字节 + 字符串大小 最长 n 个字符的字符串 有长度限制的变长字符串,上限10485760字节
text 1 字节 + 字符串长度 任意长度字符串 无长度限制的变长字符串

从表格可以看出,MatrixDB字符串类型分2类:

  • 定长:char
  • 变长
    1. varchar:有长度限制
    2. 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种:

  • 日期:仅能表示年-月-日,表示不了时间
    1. date
  • 时间:仅能表示时间,表示不了日期
    1. time [without time zone](不带时区)
    2. time with time zone(带时区)
  • 日期和时间:既能表示日期又能表示时间
    1. timestamp [without time zone](不带时区)
    2. 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类型,并带时区
  • 设备标识
    1. 如果是使用字符串标识设备则建议使用varchar和text。char因为长度不可变,适合于存储定长的设备编号信息,如序列号
    2. 如果为设备单独创建设备表,可使用serial或bigserial做自增ID,来唯一标识设备
  • 采集指标:通常为数值可根据取值范围和是否有小数来确定类型
    1. 如果采集结果为整数,则选择整型数据类型,根据结果范围确定是使用smallint、int还是bigint
    2. 如果采集结果带小数,并且小数点精确的位数固定,取值范围也相对固定,例如温度,适合使用decimal
    3. 如果采集结果带小数,并且小数点精确的位数不固定,取值范围较大,则需要根据精度来选择float或double precision

2. 扩展数据类型

在关系模型中,基础数据类型,每个列表示一个属性的值,如:姓名、身高、体重。但是面临如下挑战:

  1. 无法预知指标集,即表schema可能要经常变
  2. 指标集合过大,超过了MatrixDB表最多1600列的列数限制

这个时候,就需要使用扩展数据类型。所谓的扩展数据类型,就是可以在单个列中存储多个属性。

实现扩展数据类型的方式有很多,比如可以自定义序列化存储格式和编码方式,然后存储到字符串类型中。MatrixDB则提供了两种扩展数据类型:

  1. json/jsonb
  2. mxkv

2.1 json/jsonb

json/jsonb类型继承自PostgreSQL数据库,和直接用字符串类型的列保存json串不同,json/jsonb类型提供了常用的json操作函数,用于读取key值,以及合并、删除等操作。

json与jsonb使用方法基本一致,也可以直接相互转换,区别是:

  1. json保存为文本,jsonb保存为二进制
  2. json写入时不需要做二进制转换,写入速度更快一些
  3. 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是二进制存储,并且进行了压缩,存储空间更小,查询效率更高。

详细的使用方法请参考文档blog

3. 空间数据类型

MatrixDB 4.3开发了增强版的PostGIS组件,支持空间数据类型的存储和计算。具体使用方法请参考文档