数据类型

数据模型确定了，在创建表之前，还需要知道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. 扩展数据类型

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

无法预知指标集，即表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是二进制存储，并且进行了压缩，存储空间更小，查询效率更高。

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

3. 空间数据类型

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