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  NULL   COMMENT  '采集时间' ,    `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_traffic   ADD  description_speed  int (10)  GENERATED ALWAYS  AS  (description-> '$.speed' ) VIRTUAL;

5.创建索引

1	alter  table   t_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 解决数据手动同步可能导致的主键冲突，经常字段变更，数据的定期删除。

###############################################

本文转自 roidba 51CTO博客，原文链接：http://blog.51cto.com/roidba/1881401，如需转载请自行联系原作者