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，如需转载请自行联系原作者
