数据库表动态列的设计

问题描述

曾经开发过一个物联网数据展示平台，软件整体架构如下图：

平台数据采集器通过mqtt协议对接，收到采集器数据后存入数据库。采集器采集的数据项不是固定的，以纺织行业举例，染缸需要采集用电量、用水量和蒸汽用量，而定型机只需要采集电量和天然气用量，为了让平台更通用，字段需要动态可配置，所以数据表的设计需要考虑动态列。

方案描述

方案一：动态修改数据表结构

设计思路

考虑使用三张表，设备表，设备数据项表，数据表，结构如下：

• 设备表

字段名	说明
id	主键
meter_name	设备名
。。。	其他属性

• 设备数据项表

字段名	说明
id	主键
measure_name	字段名称，例：开关量、电压、电流
table_col	数据表中的列
mqtt_col	与mqtt上传数据对应
。。。	其他属性

• 数据表

字段名	说明
id	主键
meter_id	对应设备
data_time	数据时间
col1	数据1
col2	数据2
coln	数据n

接入设备时，设备表添加记录，之后用户为设备配合采集数据项，设备数据项表会添加记录，数据表则对应添加表字段。

评价

简单易实现，维护也方便，但是不安全，由于表数据量较大，这里不考虑。

方案二：预留字段

设计思路

是方案一基础上的改进，将数据表的字段预留多一点，从而不在业务中修改表结构。

评价

简单易实现，解决了安全问题，但是预留字段会浪费一定空间，并且数据采集项数量有上限。上文中的平台使用了这个方案。

方案三：使用json存

mysql5.7之后支持json字段，即将所有数据存在一个json字段中，会更加灵活可变，缺点是无法建立索引，并且使用起来比较麻烦。

方案四：列改行

将每列数据改成行存储，形式如下：
通常数据为：

meter_id	data_time	UA	UB	UC
1	2021-10-26 00:00:00	220	220	220

列改行后：

meter_id	data_time	mqtt_col	value
1	2021-10-26 00:00:00	UA	220
1	2021-10-26 00:00:00	UB	220
1	2021-10-26 00:00:00	UC	220

优点是采集数据项可以无限扩展，缺点也显而易见，一条数据变成了多条，数据量翻了好几倍，适用于数据量不大的场景。