Storing IoT data in MySQL

 Storing IoT data in MySQL 

1. Setting up MySQL server on local system.
2. Setting up Node-red to get the temperature and humidity data from IoT device
3. Creating Flow for database node in Node-red to store data into database

參考來源 https://aliakbarfani.wordpress.com/storing-iot-data-in-mysql-and-integrating-real-time-analytics/

取小數電後1位數

var originalNumber = msg.payload

msg.payload = originalNumber.toFixed(1)

return msg;

===============================

temperature = msg.payload[0] ;

humidity = msg.payload[1];

//humidity = msg.payload.humidity

//temperature = msg.payload.temperature

msg.topic = "INSERT INTO sensors ( `temperature`, `humidity`) VALUES ( "+ temperature+", "+ humidity +");"

return msg;

Node-Red 程式

==================================================

[{"id":"df15bbaf.dd2c08","type":"tab","label":"流程4","disabled":false,"info":""},{"id":"7b7f0885.63a278","type":"mqtt out","z":"df15bbaf.dd2c08","name":"","topic":"alex9ufo/IOT/temperature_humidity","qos":"1","retain":"false","broker":"841df58d.ee5e98","x":480,"y":420,"wires":[]},{"id":"bb1d0332.5b2fd","type":"mqtt in","z":"df15bbaf.dd2c08","name":"","topic":"alex9ufo/IOT/temperature_humidity","qos":"1","datatype":"auto","broker":"841df58d.ee5e98","x":160,"y":80,"wires":[["e9fe04c3.e96618"]]},{"id":"3a140877.2c8a88","type":"inject","z":"df15bbaf.dd2c08","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":340,"wires":[["9a2d36e3.c37a18","aae3f56.23f5b08"]]},{"id":"9a2d36e3.c37a18","type":"random","z":"df15bbaf.dd2c08","name":" temperature","low":"0","high":"50","inte":"false","property":"payload","x":270,"y":320,"wires":[["f0b10e6c.edba8"]]},{"id":"aae3f56.23f5b08","type":"random","z":"df15bbaf.dd2c08","name":"humidity","low":"0","high":"100","inte":"false","property":"payload","x":260,"y":360,"wires":[["19a22c5b.3f80a4"]]},{"id":"19b8f913.5db8c7","type":"join","z":"df15bbaf.dd2c08","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":630,"y":340,"wires":[["7b7f0885.63a278"]]},{"id":"ae23998a.2b61b8","type":"function","z":"df15bbaf.dd2c08","name":"MySQL Function","func":"temperature = msg.payload[0] ;\nhumidity = msg.payload[1];\n\n//humidity = msg.payload.humidity\n//temperature = msg.payload.temperature\nmsg.topic = \"INSERT INTO sensors ( `temperature`, `humidity`) VALUES ( \"+ temperature+\", \"+ humidity +\");\"\nreturn msg;\n\n","outputs":1,"noerr":0,"x":590,"y":80,"wires":[["a5e2f3b1.0a897"]]},{"id":"f0b10e6c.edba8","type":"function","z":"df15bbaf.dd2c08","name":"取小數電後1位數","func":"var originalNumber = msg.payload\nmsg.payload = originalNumber.toFixed(1)\nreturn msg;","outputs":1,"noerr":0,"x":450,"y":320,"wires":[["19b8f913.5db8c7"]]},{"id":"19a22c5b.3f80a4","type":"function","z":"df15bbaf.dd2c08","name":"","func":"var originalNumber = msg.payload\nmsg.payload = originalNumber.toFixed(1)\nreturn msg;","outputs":1,"noerr":0,"x":410,"y":360,"wires":[["19b8f913.5db8c7"]]},{"id":"a5e2f3b1.0a897","type":"mysql","z":"df15bbaf.dd2c08","mydb":"3ec1eff7.3a59c","name":"MySQLDatabase","x":790,"y":80,"wires":[["63552ff9.dd45a"]]},{"id":"63552ff9.dd45a","type":"debug","z":"df15bbaf.dd2c08","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":870,"y":140,"wires":[]},{"id":"e9fe04c3.e96618","type":"json","z":"df15bbaf.dd2c08","name":"","property":"payload","action":"","pretty":false,"x":390,"y":80,"wires":[["ae23998a.2b61b8","4e5783a6.4385cc","d80bd55e.713468"]]},{"id":"30fed640.27d95a","type":"ui_gauge","z":"df15bbaf.dd2c08","name":"","group":"6c9116b.b62d4e8","order":14,"width":0,"height":0,"gtype":"gage","title":"gauge","label":"units","format":"{{value}}","min":0,"max":"50","colors":["#00b500","#e6e600","#ca3838"],"seg1":"20","seg2":"35","x":670,"y":140,"wires":[]},{"id":"4145d701.1c7608","type":"ui_gauge","z":"df15bbaf.dd2c08","name":"","group":"6c9116b.b62d4e8","order":15,"width":0,"height":0,"gtype":"gage","title":"gauge","label":"units","format":"{{value}}","min":0,"max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"35","seg2":"60","x":670,"y":200,"wires":[]},{"id":"4e5783a6.4385cc","type":"function","z":"df15bbaf.dd2c08","name":"Get temperature","func":"msg.payload = msg.payload[0] ;\nreturn msg;\n\n","outputs":1,"noerr":0,"x":500,"y":140,"wires":[["30fed640.27d95a"]]},{"id":"d80bd55e.713468","type":"function","z":"df15bbaf.dd2c08","name":"Get humidity","func":"msg.payload = msg.payload[1] ;\nreturn msg;\n\n","outputs":1,"noerr":0,"x":490,"y":200,"wires":[["4145d701.1c7608"]]},{"id":"841df58d.ee5e98","type":"mqtt-broker","z":"","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"3ec1eff7.3a59c","type":"MySQLdatabase","z":"","name":"Storing IoT data","host":"127.0.0.1","port":"3306","db":"mysql_sensor","tz":"GMT +8"},{"id":"6c9116b.b62d4e8","type":"ui_group","z":"","name":"Test","tab":"eeb8e179.a47a4","order":1,"disp":true,"width":"6","collapse":false},{"id":"eeb8e179.a47a4","type":"ui_tab","z":"","name":"MySQL","icon":"dashboard","disabled":false,"hidden":false}]