一、背景

应业主方需求，设备监控指标不要显示经纬度值，而是显示其对应的地理位置信息。但是设计的时候，多维度的设备监控指标是在一张表中存储，另一张表存储设备的经纬度值及其对应的地理位置。

接到需求时，想着直接更改库表来完成需求，但是改动的地方太多，改完了还要进行测试，因此就多方寻找解决方案，后来灵机一动，想着MySQL应该有拼接的功能，就找到了UNION ALL指令。

二、表结构

2.1 设备监控数据表

create table device_kv
(uuid                varchar(32)     not null comment '序号'primary key,device_id           bigint unsigned not null comment '设备ID',metric_union_key_id int             not null comment '指标联合编码',metric_value        varchar(255)    null comment '指标值',user_id             bigint          null comment '用户ID',latest_time         datetime        not null comment '数据采集时间'
)comment '设备监控数据';create index idx_device_idon device_kv (device_id);create index idx_latest_timeon device_kv (latest_time);

该表用于存储每个设备的多维度遥测指标值，其中metric_union_key_id等于8时对应着经纬度。

2.2 设备监控指标最新数据表

create table device_kv_latest
(uuid                varchar(32)               not null comment '序号'primary key,device_id           bigint unsigned           not null comment '设备号',metric_union_key_id int                       not null comment '指标联合编码ID',metric_value        varchar(255)              null comment '指标值',create_time         datetime                  null comment '关联时间',latest_time         datetime                  not null comment '最新时间',user_id             bigint unsigned default 0 not null comment '用户ID'
)comment '设备监控指标最新数据';create index idx_device_idon device_kv_latest (device_id);create index idx_latest_timeon device_kv_latest (latest_time);

该表用于存储设备最新的遥测数据信息。

2.3 设备定位数据表

create table device_location_track
(id        varchar(32)     not null comment '设备位置ID'primary key,device_id bigint unsigned not null comment '设备ID',gps_data  varchar(100)    null comment 'GPS数据',lng       double          null,lat       double          null,address   varchar(255)    null comment '地理解析位置',data_time datetime        null comment '数据时间',user_id   bigint unsigned null comment '用户ID'
)comment '设备定位数据';create index idx_data_timeon device_location_track (data_time)comment '数据采集时间索引';create index idx_device_idon device_location_track (device_id)comment '设备ID索引';

该表用于存储设备定位数据信息。

三、查询

项目基于Mybatis-plus半自动 ORM 框架进行开发。

3.1 查询最新的监控指标信息

 select (select metric_name from device_metrics_dict where id = dkl.metric_union_key_id) as metric_name,dkl.metric_value,dkl.latest_timefrom device_kv_latest dklwhere dkl.metric_union_key_id  ]]> 8 and dkl.device_id = #{deviceId}UNION ALL (SELECT '地理位置' as metric_name,dlt.address as metric_value,dlt.data_time as latest_timefrom device_location_track dltwhere dlt.device_id = #{deviceId} order by dlt.data_time desc limit 1)order by latest_time desc

3.2 查询历史监控指标信息

select (select metric_name from device_metrics_dict where id = dk.metric_union_key_id) as metric_name,dk.metric_value,dk.latest_timefrom device_kv dkwhere dk.metric_union_key_id  ]]> 8and dk.device_id = #{deviceId}UNION ALL SELECT '地理位置' as metric_name,dlt.address as metric_value,dlt.data_time as latest_timefrom device_location_track dltwhere dlt.device_id = #{deviceId}ORDER BY latest_time desc

四、查询效果