Mysql 5.7 JSON 与 物联网设备上数完美结合
MySQL 5.7.15 新特性一 (json)更多更浓参考官网
https://dev.mysql.com/doc/refman/5.7/en/json.html
一、测试过程
1.创建库表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create database veh_history;
use veh_history;
CREATE TABLE `t_equipment_traffic` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`cn` char(17) NOT NULL COMMENT '设备号',
`st` datetime NOT NULLCOMMENT '采集时间',
`description` json,
PRIMARY KEY (`id`,`st`),
KEY `idx_cn_ct` (`cn`),
KEY `idx_st` (`st`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='历史数据'
PARTITION BY RANGE (to_days(st))
(PARTITION p201601 VALUES LESS THAN (to_days('2016-12-01')),
PARTITION p201612 VALUES LESS THAN (to_days('2017-01-01')),
PARTITION p201701 VALUES LESS THAN (to_days('2017-02-01')),
PARTITION p201702 VALUES LESS THAN (to_days('2017-03-01'))
);
2.插入json类型测试数据
1
2
3
4
5
6
insert into `t_equipment_traffic` (cn,st,description) values ('liaoa12345roiland',now(),
'{"time":"2015-01-01 13:00:00","speed":"100","result":"fail"}');
insert into `t_equipment_traffic` (cn,st,description) values ('liaoa12345roiland',now(),
'{"time":"2017-01-01 13:00:00","speed":"110","result":"fail"}');
insert into `t_equipment_traffic` (cn,st,description) values ('liaoa12345roiland',now(),
'{"time":"2017-01-02 13:00:00","speed":"110","result":"sucessed"}');
3.json简单查询方法
1
2
3
4
select cn,description from t_equipment_traffic where cn='liaoa12345roiland';
select * from t_equipment_traffic where description ->'$.time' > '2016-12-31';
select cn,json_extract(description,'$.speed') AS 'speed',json_extract(description,'$.result') AS 'result'
from t_equipment_traffic;
4.创建虚拟列
1
2
ALTER TABLE t_equipment_trafficADD description_speed int(10)
GENERATED ALWAYS AS (description->'$.speed') VIRTUAL;
5.创建索引
1
alter tablet_equipment_traffic add index idx_speed (description_speed);
6.查看执行计划
explain partitions select * from t_equipment_traffic where description_speed=100;
二、小结
1.mysql 5.7 已开始支持json数据类型，存储引擎使用Innodb
2.对于物联网设备上数存储完全可以采用json，大数据量，经常会变更字段的场景十分适合。
3.uuid+json+innodb+partition 解决数据手动同步可能导致的主键冲突，经常字段变更，数据的定期删除。
###############################################
页:
[1]