Mysql 5.7 JSON 與 物聯網裝置上數完美結合

標籤：mysql 5.7 json 與 物聯網裝置上數完美結合

MySQL 5.7.15 新特性一 (json)

更多更濃參考官網

https://dev.mysql.com/doc/refman/5.7/en/json.html

一、測試過程

1.建立庫表

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類型測試資料 

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簡單查詢方法

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.建立虛擬列

ALTER TABLE t_equipment_traffic  ADD description_speed int(10) GENERATED ALWAYS AS (description->‘$.speed‘) VIRTUAL;

5.建立索引

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 解決資料手動同步可能導致的主鍵衝突，經常欄位變更，資料的定期刪除，保留了MVCC特性。

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

本文出自 “dba003” 部落格，請務必保留此出處http://dba003.blog.51cto.com/12318731/1881313

Mysql 5.7 JSON 與 物聯網裝置上數完美結合