数据库表动态列的设计
问题描述
曾经开发过一个物联网数据展示平台，软件整体架构如下图：
平台数据采集器通过mqtt协议对接，收到采集器数据后存入数据库。采集器采集的数据项不是固定的，以纺织行业举例，染缸需要采集用电量、用水量和蒸汽用量，而定型机只需要采集电量和天然气用量，为了让平台更通用，字段需要动态可配置，所以数据表的设计需要考虑动态列。
方案描述
方案一：动态修改数据表结构
设计思路
考虑使用三张表，设备表，设备数据项表，数据表，结构如下：
-
设备表
-
设备数据项表
-
数据表
接入设备时，设备表添加记录，之后用户为设备配合采集数据项，设备数据项表会添加记录，数据表则对应添加表字段。
评价
简单易实现，维护也方便，但是不安全，由于表数据量较大，这里不考虑。
方案二：预留字段
设计思路
是方案一基础上的改进，将数据表的字段预留多一点，从而不在业务中修改表结构。
评价
简单易实现，解决了安全问题，但是预留字段会浪费一定空间，并且数据采集项数量有上限。上文中的平台使用了这个方案。
方案三：使用json存
mysql5.7之后支持json字段，即将所有数据存在一个json字段中，会更加灵活可变，缺点是无法建立索引，并且使用起来比较麻烦。
方案四：列改行
将每列数据改成行存储，形式如下： 通常数据为：
列改行后：
优点是采集数据项可以无限扩展，缺点也显而易见，一条数据变成了多条，数据量翻了好几倍，适用于数据量不大的场景。
下一篇：
Mybatis-模糊查询的俩种方法