MySQL Flexible Schema for IoT

用于物联网的MySQL灵活模式

展开查看详情

1.

2.● ○ ○

3.● Part 1: IoT demo ● Part 2: MySQL, JSON and Flexible storage

4.

5.

6.

7.

8. … $ node particle_mysql_all.js Starting... INSERT INTO cloud_data_json (name, data) values ('particle', '{\"data\":\"null\",\"ttl\":60,\"published_at\":\"2017-09-28T19:40:49.869Z\",\"coreid\":\"1f0039000947343337373738 \",\"name\":\"Server Error\"}') ...

9.console.log("Starting..."); var Particle = require('particle-api-js'); var particle = new Particle(); fs = require('fs') var token = fs.readFileSync('.token', 'ascii').replace(/\n$/, ''); //Get your devices events // MySQL Connection var mysql = require('mysql'); var connection = mysql.createConnection({ host : 'localhost', user : 'photon', password : 'photon', database : 'particle' });

10.... particle.getEventStream({deviceId: 'mine', auth: token}).then(function(stream) { stream.on('event', function(data) { var query = connection.query(' INSERT INTO cloud_data (name, data) values (?, ?)', [data.name, data.data], function(err, result) { if (err) { console.log('Error in ' + query.sql + err); } }); console.log(query.sql); }); });

11.…

12.

13.Storing data in MySQL … $ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 8.0.3-rc MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

14.CREATE TABLE `sensor_wide` ( `id` int(11) NOT NULL AUTO_INCREMENT, `light` int(11) DEFAULT NULL, `temp` double DEFAULT NULL, `humidity` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB alter table sensor_wide add water_level double ...;

15.CREATE TABLE `cloud_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `data` text DEFAULT NULL, `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP PRIMARY KEY (`id`) ) ENGINE=InnoDB + More Flexible - High overhead on storage

16.Everyone knows what JSON is, right?

17.CREATE TABLE `cloud_data_json` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `data` JSON, `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP PRIMARY KEY (`id`) ) ENGINE=InnoDB; + Most Flexible + Indexes - Overhead on storage

18.... stream.on('event', function(data) { var query = connection.query( 'INSERT INTO cloud_data_json (client_name, data) VALUES (?, ?)', ['particle', JSON.stringify(data)] ) ... (demo)

19.

20. { "ttl": 60, "data": "FvGav,tagkey=beer-store spFridge=7.00,pvFridge=7.44", "name": "LOG_DATA_DEBUG", const mysqlx = require('@mysql/xdevapi'); "coreid": "3600....", // MySQL Connection "published_at": var mySession = "2017-09-28T18:21:16.517Z" mysqlx.getSession({ } host: 'localhost', port: 33060, dbUser: 'photon' }); ... session.getSchema("particle").getCollection("cloud_data_docstore") .add( data ) .execute(function (row) { }).catch(err => { console.log(err); }) .then( function (notices) { console.log("Wrote to MySQL") }); … https://dev.mysql.com/doc/dev/connector-nodejs/

21.select data->>'$.name' as data_name, data->>'$.data' as data, data->>'$.published_at' as published from cloud_data_json order by data->'$.published_at' desc limit 10;

22.EXPLAIN select data->>'$.name' as data_name … order by data->>'$.published_at' desc limit 10 select_type: SIMPLE table: cloud_data_json partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 101589 filtered: 100.00 Extra: Using filesort

23.mysql> ALTER TABLE cloud_data_json -> ADD published_at DATETIME(6) -> GENERATED ALWAYS AS (STR_TO_DATE(data->>'$.published_at',"%Y-%m-%dT%T.%fZ")) VIRTUAL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE cloud_data_json -> ADD data_name VARCHAR(255) -> GENERATED ALWAYS AS (data->>'$.name') VIRTUAL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0

24.mysql> alter table cloud_data_json add key (published_at); Query OK, 0 rows affected (0.31 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select data_name, published_at, data->>'$.data' as data from cloud_data_json order by published_at desc limit 10\G table: cloud_data_json partitions: NULL type: index possible_keys: NULL key: published_at key_len: 9 ref: NULL rows: 10 filtered: 100.00 Extra: Backward index scan

25.mysql> explain select data_name, published_at, data->>'$.data' as data from cloud_data_json order by published_at desc, data_name asc limit 10\G select_type: SIMPLE table: cloud_data_json partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 101589 filtered: 100.00 Extra: Using filesort

26. New in MySQL 8.0 mysql> alter table cloud_data_json add key published_at_data_name (published_at desc, data_name asc); Query OK, 0 rows affected (0.44 sec) Records: 0 Duplicates: 0 Warnings: 0

27.mysql> explain select data_name, published_at, data->>'$.data' as data from cloud_data_json order by published_at desc limit 10\G select_type: SIMPLE table: cloud_data_json partitions: NULL type: index possible_keys: NULL key: published_at_data_name key_len: 267 ref: NULL rows: 10 filtered: 100.00 Extra: NULL

28.JSON field type was introduced in 5.7, improved in 8.0 ● Partial updates ○ in-place update of a JSON column instead of removing the old document and writing the new document in its entirety to the column ○ Only functions are supported: JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() Use JSON_STORAGE_FREE(json_val) to see how much storage space was freed in its binary representation after it was updated in place

29.mysql> select json_pretty(data) from cloud_data_json where data->>'$.data' like '%beer%' limit 1\G ... json_pretty(data): { "ttl": 60, "data": "FvGav,tagkey=beer-store spFridge=7.00,pvFridge=7.44", "name": "LOG_DATA_DEBUG", "coreid": "3600....", "published_at": "2017-09-28T18:21:16.517Z" }