Mysql 5.7 JSON is a perfect combination of the number of IoT devices

MySQL 5.7.15 new feature One (JSON)

More More strong Reference website

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

First, the test process

1. Create a library table

create database veh_history;use veh_history; create table  ' t_equipment_traffic '   (   ' id '  bigint ()  NOT NULL  auto_increment comment  ' self-increment primary key ',   ' CN '  char (+)  NOT NULL COMMENT  ' Device number ',   ' st '  datetime NOT NULL  COMMENT  ' acquisition time ',   ' description '  json,  PRIMARY KEY  (' id ', ' st '),  key  ' idx_cn_ct '   (' cn '),   KEY  ' Idx_st '   (' st ')  )  engine=innodb auto_increment=1 default charset= utf8 comment= ' Historical data ' 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. Inserting JSON type test data

Insert INTO ' t_equipment_traffic ' (cn,st,description) VALUES (' Liaoa12345roiland ', now (), ' {"Time": "2015-01-01 13:00:00 "," Speed ":" + "," result ":" Fail "} '), insert INTO ' t_equipment_traffic ' (cn,st,description) VALUES (' Liaoa12345roiland ', now (), ' {' Time ': ' 2017-01-01 13:00:00 ', ' Speed ': ', ' ' 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 Simple Query method

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. Create a virtual column

ALTER TABLE t_equipment_traffic ADD description_speed Int (ten) GENERATED always as (description-> ' $.speed ') VIRTUAL;

5. Create an index

ALTER TABLE t_equipment_traffic Add index idx_speed (description_speed);

6. View the execution plan

Explain partitions select * from t_equipment_traffic where description_speed=100;

Second, summary

1.mysql 5.7 has started to support JSON data types, and the storage engine uses InnoDB

2. For IoT devices storage can be fully JSON-based, large data volume, often changing the field of the scene is very suitable.

3.uuid+json+innodb+partition resolves primary key conflicts that can result from manual synchronization of data, frequent field changes, periodic deletion of data, and retention of the MVCC feature.

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

This article is from the "dba003" blog, make sure to keep this source http://dba003.blog.51cto.com/12318731/1881313

Mysql 5.7 JSON is a perfect combination of the number of IoT devices