数据类型

本文档介绍了 YMatrix 包含的数据类型。

YMatrix 支持的数据类型包括如下几类：

基础数据类型
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	可变	无限制	固定精度

从表格可以看出，YMatrix 的数值类型分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 字节 + 字符串长度	任意长度字符串	无长度限制的变长字符串

从表格可以看出，YMatrix 字符串类型分 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	日期和时间, 带时区

从表格可以看出，YMatrix 的日期时间类型分为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 扩展数据类型

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

无法预知指标集，即表模式可能要经常变
指标集合过大，超过了 YMatrix 表最多 1600 列的列数限制

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

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

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 是 YMatrix 自研的高效可扩展存储类型，使用方法和 JSON 类似，和 JSON 相比的优势是：MXKV 是二进制存储，并且进行了压缩，存储空间更小，查询效率更高。

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

3 空间数据类型

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