alex9ufo 聰明人求知心切: ESP32+MQTT+Node-RED+MySQL

2020年10月3日星期六

ESP32+MQTT+Node-RED+MySQL

1) Esp32程式

http://www.mediafire.com/file/c3alg5x86lmdnx7/ESP32_MQTT_LED.ino/file

2) Node-RED 程式

請注意　VALUES ( '" + status + "');"　錯誤讓費時間

var status = msg.payload ;

msg.topic = "INSERT INTO mysql_led_status (`LED_MQTT_Status`) VALUES ( '" + status + "');"

node.status({text:msg.topic});

return msg;

Node-RED程式

[{"id":"51754690.ffee38","type":"mqtt in","z":"c1eadc43.582be","name":"","topic":"alex9ufo/led/led_event","qos":"1","datatype":"auto","broker":"841df58d.ee5e98","x":140,"y":80,"wires":[["94302818.ae36a8","96f2f0d8.1fbfa","d1d1e654.843568","b99597a3.65f958"]]},{"id":"94302818.ae36a8","type":"ui_text","z":"c1eadc43.582be","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"name":"","label":"MQTT Suscribe Data","format":"{{msg.payload}}","layout":"col-center","x":400,"y":80,"wires":[]},{"id":"e7f38845.851508","type":"ui_button","z":"c1eadc43.582be","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED ON","tooltip":"","color":"white","bgcolor":"","icon":"fa-circle","payload":"ON","payloadType":"str","topic":"","x":120,"y":300,"wires":[["e4961251.9311c"]]},{"id":"19bfc564.049e4b","type":"ui_button","z":"c1eadc43.582be","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED OFF","tooltip":"","color":"black","bgcolor":"","icon":"fa-circle-o","payload":"OFF","payloadType":"str","topic":"","x":120,"y":340,"wires":[["e4961251.9311c"]]},{"id":"e4961251.9311c","type":"mqtt out","z":"c1eadc43.582be","name":"","topic":"alex9ufo/led/led_event","qos":"1","retain":"false","broker":"841df58d.ee5e98","x":400,"y":340,"wires":[]},{"id":"96f2f0d8.1fbfa","type":"function","z":"c1eadc43.582be","name":"Format timestamp","func":"var date = new Date();\nvar h = date.getHours();\nvar m = date.getMinutes();\nvar s = date.getSeconds();\nif(h<10){\n h = '0'+h;\n}\nif(m<10){\n m = '0' + m;\n}\nif(s<10){\n s = '0' + s;\n}\nmsg.payload = msg.payload + ' --> Time:(' + h + ':' + m + ':' + s + ')' ;\n\nreturn msg;","outputs":1,"noerr":0,"x":150,"y":240,"wires":[["c5252b6e.bebcc8"]]},{"id":"c5252b6e.bebcc8","type":"function","z":"c1eadc43.582be","name":"Set Line API ","func":"msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer A4wwPNh2WqB7dlfeQyyIAwtggn1kfZSI5LkkCdia1gB'};\nmsg.payload = {\"message\":msg.payload};\nreturn msg;\n\n//oR7KdXvK1eobRr2sRRgsl4PMq23DjDlhfUs96SyUBZu","outputs":1,"noerr":0,"x":290,"y":280,"wires":[["7f86a659.2321c8"]]},{"id":"7f86a659.2321c8","type":"http request","z":"c1eadc43.582be","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":440,"y":240,"wires":[["847c3e7e.3b5fc"]]},{"id":"847c3e7e.3b5fc","type":"debug","z":"c1eadc43.582be","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":550,"y":280,"wires":[]},{"id":"d1d1e654.843568","type":"debug","z":"c1eadc43.582be","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":390,"y":20,"wires":[]},{"id":"e473ddb0.8d753","type":"ui_audio","z":"c1eadc43.582be","name":"","group":"6c9116b.b62d4e8","voice":"zh-TW","always":true,"x":520,"y":120,"wires":[]},{"id":"fc1ee7eb.f3c0d8","type":"ui_button","z":"c1eadc43.582be","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED Flashing","tooltip":"","color":"blue","bgcolor":"","icon":"fa-circle-o","payload":"FLASH","payloadType":"str","topic":"","x":140,"y":380,"wires":[["e4961251.9311c"]]},{"id":"b99597a3.65f958","type":"function","z":"c1eadc43.582be","name":"","func":"var st1;\nif (msg.payload === \"ON\") {\n st1=\"LED開\"; \n} \nelse if (msg.payload === \"OFF\") {\n st1=\"LED關\";\n}\nelse if (msg.payload === \"FLASH\") {\n st1=\"LED閃爍\";\n}\nelse if (msg.payload === \"TIMER\") {\n st1=\"LED開五秒鐘\";\n}\n\nmsg.payload=st1;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":330,"y":120,"wires":[["e473ddb0.8d753","faeddd08.b340c"]]},{"id":"75bd5e9a.f45f5","type":"ui_button","z":"c1eadc43.582be","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED ON 5 Sec","tooltip":"","color":"purple","bgcolor":"","icon":"fa-circle-o","payload":"TIMER","payloadType":"str","topic":"","x":140,"y":420,"wires":[["e4961251.9311c"]]},{"id":"f465a5e1.e917d8","type":"mysql","z":"c1eadc43.582be","mydb":"73a92d28.0c3eb4","name":"LED STATUS","x":540,"y":160,"wires":[["272e1d81.bb3b82"]]},{"id":"faeddd08.b340c","type":"function","z":"c1eadc43.582be","name":"MySQL Function","func":"var status = msg.payload ;\nmsg.topic = \"INSERT INTO mysql_led_status (`LED_MQTT_Status`) VALUES ( '\" + status + \"');\"\nnode.status({text:msg.topic});\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":230,"y":180,"wires":[["f465a5e1.e917d8"]]},{"id":"272e1d81.bb3b82","type":"debug","z":"c1eadc43.582be","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":670,"y":200,"wires":[]},{"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":"6c9116b.b62d4e8","type":"ui_group","z":"","name":"Storing IOT Data ","tab":"eeb8e179.a47a4","order":1,"disp":true,"width":"6","collapse":false},{"id":"73a92d28.0c3eb4","type":"MySQLdatabase","z":"","name":"mysql_mqtt_led","host":"127.0.0.1","port":"3306","db":"mysql_mqtt_led","tz":"GMT +8"},{"id":"eeb8e179.a47a4","type":"ui_tab","z":"","name":"MySQL","icon":"dashboard","disabled":false,"hidden":false}]

Node-red 程式 http://www.mediafire.com/file/a76i9ccj7w32ale/flows_MQTT_LED_Mysql.json/file

3) MySQL 設定

如何安裝MySQL 請參考 http://alex9ufoexploer.blogspot.com/2020/08/mysql.html

帳號root 密碼alex9981alex