Node-RED中连接Mysql数据库并实现增删改查的操作
场景
Node-RED中建立静态网页和动态网页内容：
https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/124211653
在上面的基础上，怎样实现连接Mysql数据库并将数据库中的数据显示在网页上，以及执行插入、更新、删除操作。
注：
博客：
https://blog.csdn.net/badao_liumang_qizhi
关注公众号
霸道的程序猿
获取编程相关电子书、教程推送与免费下载。
实现
1、安装NodeRED节点
节点管理搜索node-red-node-mysql,安装
安装成功之后就会有Mysql节点了
编辑function节点，使其执行查询语句
msg.topic = "SELECT * FROM user"; return msg;
3、编辑Mysql节点
编辑连接输入数据库连接信息
4、编辑template节点修改html显示模板
用户
| {{name}} | {{pass}} |
5、部署运行访问
http://127.0.0.1:1880/allUser
编辑function节点，编辑插入语句
执行查看效果
插入流程json数据
[ { "id": "1d9518ddb606e944", "type": "inject", "z": "f4a4d8eab7935bc8", "name": "", "props": [ { "p": "payload" }, { "p": "topic", "vt": "str" } ], "repeat": "", "crontab": "", "once": false, "onceDelay": 0.1, "topic": "", "payloadType": "date", "x": 210, "y": 380, "wires": [ [ "e28fd0d0403014e0" ] ] }, { "id": "e28fd0d0403014e0", "type": "function", "z": "f4a4d8eab7935bc8", "name": "INSERT", "func": "\nvar user = 'insertUser';\nvar pass = '123456';\n\nmsg.topic = \"INSERT INTO user \" +\n \"(name,pass)\" +\n \"VALUES ('\" + user +\n \"','\"+ pass + \"' )\";\nreturn msg;", "outputs": 1, "noerr": 0, "initialize": "", "finalize": "", "libs": [], "x": 340, "y": 380, "wires": [ [ "fdde79cf9a1b1722" ] ] }, { "id": "fdde79cf9a1b1722", "type": "mysql", "z": "f4a4d8eab7935bc8", "mydb": "51c3503.61936b", "name": "", "x": 480, "y": 380, "wires": [ [ "5ea56f3eec020d0f" ] ] }, { "id": "5ea56f3eec020d0f", "type": "debug", "z": "f4a4d8eab7935bc8", "name": "", "active": true, "tosidebar": true, "console": false, "tostatus": false, "complete": "payload", "targetType": "msg", "statusVal": "", "statusType": "auto", "x": 630, "y": 380, "wires": [] }, { "id": "51c3503.61936b", "type": "MySQLdatabase", "name": "", "host": "127.0.0.1", "port": "3306", "db": "test", "tz": "", "charset": "UTF8", "credentials": {} } ]
8、新增更新语句执行流程设计如下
模板值可以在字符找那个嵌入运算式或变量。
但是注意的是字符串是使用反引号，在Tab上方的按钮括起，而不是使用单引号括起。
编辑function节点
var id = 3; var name = 'updateName'; var pass = '5555'; msg.topic = `UPDATE user SET name='${name}',` + `pass='${pass}' WHERE id=${id}`; return msg;
部署运行看效果
更新流程json数据
[ { "id": "5426aed100d65d31", "type": "inject", "z": "f4a4d8eab7935bc8", "name": "", "repeat": "", "crontab": "", "once": false, "onceDelay": 0.1, "topic": "", "payload": "", "payloadType": "date", "x": 210, "y": 520, "wires": [ [ "a911307ea6b68a16" ] ] }, { "id": "a911307ea6b68a16", "type": "function", "z": "f4a4d8eab7935bc8", "name": "UPDATE", "func": "var id = 3;\nvar name = 'updateName';\nvar pass = '5555';\nmsg.topic = `UPDATE user SET name='${name}',` +\n `pass='${pass}' WHERE id=${id}`;\nreturn msg;", "outputs": 1, "noerr": 0, "initialize": "", "finalize": "", "libs": [], "x": 340, "y": 520, "wires": [ [ "eb4b3bd2a53dbb37", "d991b7f159d90812" ] ] }, { "id": "eb4b3bd2a53dbb37", "type": "mysql", "z": "f4a4d8eab7935bc8", "mydb": "51c3503.61936b", "name": "", "x": 480, "y": 520, "wires": [ [ "c83545834bc01135" ] ] }, { "id": "c83545834bc01135", "type": "debug", "z": "f4a4d8eab7935bc8", "name": "", "active": true, "tosidebar": true, "console": false, "tostatus": false, "complete": "payload", "targetType": "msg", "statusVal": "", "statusType": "auto", "x": 630, "y": 520, "wires": [] }, { "id": "d991b7f159d90812", "type": "debug", "z": "f4a4d8eab7935bc8", "name": "", "active": true, "tosidebar": true, "console": false, "tostatus": false, "complete": "topic", "targetType": "msg", "statusVal": "", "statusType": "auto", "x": 480, "y": 600, "wires": [] }, { "id": "51c3503.61936b", "type": "MySQLdatabase", "name": "", "host": "127.0.0.1", "port": "3306", "db": "test", "tz": "", "charset": "UTF8" } ]
9、设计删除流程布局
编辑template节点
DELETE FROM user WHERE id='{{payload}}'部署运行查看效果
删除流程json数据
[ { "id": "883fb789f22f352f", "type": "inject", "z": "f4a4d8eab7935bc8", "name": "", "props": [ { "p": "payload" }, { "p": "topic", "vt": "str" } ], "repeat": "", "crontab": "", "once": false, "onceDelay": 0.1, "topic": "", "payload": "1", "payloadType": "str", "x": 210, "y": 720, "wires": [ [ "15479540478f4720" ] ] }, { "id": "fd30c40a7a9d0baf", "type": "mysql", "z": "f4a4d8eab7935bc8", "mydb": "51c3503.61936b", "name": "", "x": 480, "y": 720, "wires": [ [ "6eb37dd7c809dd01" ] ] }, { "id": "6eb37dd7c809dd01", "type": "debug", "z": "f4a4d8eab7935bc8", "name": "", "active": true, "tosidebar": true, "console": false, "tostatus": false, "complete": "payload", "targetType": "msg", "statusVal": "", "statusType": "auto", "x": 630, "y": 720, "wires": [] }, { "id": "15479540478f4720", "type": "template", "z": "f4a4d8eab7935bc8", "name": "DELETE", "field": "topic", "fieldType": "msg", "format": "handlebars", "syntax": "mustache", "template": "DELETE FROM user WHERE id='{{payload}}'", "output": "str", "x": 340, "y": 720, "wires": [ [ "fd30c40a7a9d0baf" ] ] }, { "id": "51c3503.61936b", "type": "MySQLdatabase", "name": "", "host": "127.0.0.1", "port": "3306", "db": "test", "tz": "", "charset": "UTF8" } ]
10、增删改查全流程json数据
[ { "id": "f4a4d8eab7935bc8", "type": "tab", "label": "流程 3", "disabled": false, "info": "", "env": [] }, { "id": "c428312d1f6165d1", "type": "mqtt-broker", "name": "mqtt", "broker": "127.0.0.1", "port": "1883", "clientid": "", "autoConnect": true, "usetls": false, "protocolVersion": "4", "keepalive": "60", "cleansession": true, "birthTopic": "", "birthQos": "0", "birthPayload": "", "birthMsg": {}, "closeTopic": "", "closeQos": "0", "closePayload": "", "closeMsg": {}, "willTopic": "", "willQos": "0", "willPayload": "", "willMsg": {}, "sessionExpiry": "" }, { "id": "51eee50b4ec9422f", "type": "ui_group", "name": "dashboardDemo", "tab": "29ae4c620f43ee0d", "order": 1, "disp": true, "width": "6", "collapse": false, "className": "" }, { "id": "29ae4c620f43ee0d", "type": "ui_tab", "name": "Home", "icon": "dashboard", "disabled": false, "hidden": false }, { "id": "a506c767a5c1edbd", "type": "ui_base", "theme": { "name": "theme-light", "lightTheme": { "default": "#0094CE", "baseColor": "#0094CE", "baseFont": "-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif", "edited": true, "reset": false }, "darkTheme": { "default": "#097479", "baseColor": "#097479", "baseFont": "-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif", "edited": false }, "customTheme": { "name": "Untitled Theme 1", "default": "#4B7930", "baseColor": "#4B7930", "baseFont": "-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif" }, "themeState": { "base-color": { "default": "#0094CE", "value": "#0094CE", "edited": false }, "page-titlebar-backgroundColor": { "value": "#0094CE", "edited": false }, "page-backgroundColor": { "value": "#fafafa", "edited": false }, "page-sidebar-backgroundColor": { "value": "#ffffff", "edited": false }, "group-textColor": { "value": "#1bbfff", "edited": false }, "group-borderColor": { "value": "#ffffff", "edited": false }, "group-backgroundColor": { "value": "#ffffff", "edited": false }, "widget-textColor": { "value": "#111111", "edited": false }, "widget-backgroundColor": { "value": "#0094ce", "edited": false }, "widget-borderColor": { "value": "#ffffff", "edited": false }, "base-font": { "value": "-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif" } }, "angularTheme": { "primary": "indigo", "accents": "blue", "warn": "red", "background": "grey", "palette": "light" } }, "site": { "name": "Node-RED Dashboard", "hideToolbar": "false", "allowSwipe": "false", "lockMenu": "false", "allowTempTheme": "true", "dateFormat": "DD/MM/YYYY", "sizes": { "sx": 48, "sy": 48, "gx": 6, "gy": 6, "cx": 6, "cy": 6, "px": 0, "py": 0 } } }, { "id": "248cbbb0.18e794", "type": "ui_group", "name": "MyGroup", "tab": "3f79c420.cfc1bc", "order": 1, "disp": true, "width": "6", "collapse": false }, { "id": "3f79c420.cfc1bc", "type": "ui_tab", "name": "Home", "icon": "dashboard", "disabled": false, "hidden": false }, { "id": "a0954be5.a7f7e8", "type": "ui_group", "name": "MyInput", "tab": "3f79c420.cfc1bc", "order": 2, "disp": true, "width": "6", "collapse": false }, { "id": "508bd6f8398ab80f", "type": "ui_group", "name": "OpenWeatherMap", "tab": "3f79c420.cfc1bc", "order": 3, "disp": true, "width": "6", "collapse": false }, { "id": "8824dea1.83e31", "type": "websocket-client", "path": "ws://localhost:9898/", "tls": "", "wholemsg": "false" }, { "id": "51c3503.61936b", "type": "MySQLdatabase", "name": "", "host": "127.0.0.1", "port": "3306", "db": "test", "tz": "", "charset": "UTF8" }, { "id": "571a8ee8.5883d", "type": "http in", "z": "f4a4d8eab7935bc8", "name": "", "url": "/allUser", "method": "get", "upload": false, "swaggerDoc": "", "x": 210, "y": 160, "wires": [ [ "71ec3368.c3cf5c" ] ] }, { "id": "7eb81f73.1710c", "type": "template", "z": "f4a4d8eab7935bc8", "name": "HTML网页", "field": "payload", "fieldType": "msg", "format": "handlebars", "syntax": "mustache", "template": "\n \n用户 \n \n \n\n {{#payload}}\n
\n \n", "x": 570, "y": 160, "wires": [ [ "766f42f9.87fbcc" ] ] }, { "id": "71ec3368.c3cf5c", "type": "function", "z": "f4a4d8eab7935bc8", "name": "SQL Query", "func": "msg.topic = \"SELECT * FROM user\";\nreturn msg;", "outputs": 1, "noerr": 0, "initialize": "", "finalize": "", "libs": [], "x": 290, "y": 240, "wires": [ [ "1c7d1dcf.bb2ad2" ] ] }, { "id": "766f42f9.87fbcc", "type": "http response", "z": "f4a4d8eab7935bc8", "name": "", "statusCode": "", "headers": {}, "x": 610, "y": 240, "wires": [] }, { "id": "1c7d1dcf.bb2ad2", "type": "mysql", "z": "f4a4d8eab7935bc8", "mydb": "51c3503.61936b", "name": "", "x": 410, "y": 160, "wires": [ [ "7eb81f73.1710c" ] ] }, { "id": "1d9518ddb606e944", "type": "inject", "z": "f4a4d8eab7935bc8", "name": "", "props": [ { "p": "payload" }, { "p": "topic", "vt": "str" } ], "repeat": "", "crontab": "", "once": false, "onceDelay": 0.1, "topic": "", "payloadType": "date", "x": 210, "y": 380, "wires": [ [ "e28fd0d0403014e0" ] ] }, { "id": "e28fd0d0403014e0", "type": "function", "z": "f4a4d8eab7935bc8", "name": "INSERT", "func": "\nvar user = 'insertUser';\nvar pass = '123456';\n\nmsg.topic = \"INSERT INTO user \" +\n \"(name,pass)\" +\n \"VALUES ('\" + user +\n \"','\"+ pass + \"' )\";\nreturn msg;", "outputs": 1, "noerr": 0, "initialize": "", "finalize": "", "libs": [], "x": 340, "y": 380, "wires": [ [ "fdde79cf9a1b1722" ] ] }, { "id": "fdde79cf9a1b1722", "type": "mysql", "z": "f4a4d8eab7935bc8", "mydb": "51c3503.61936b", "name": "", "x": 480, "y": 380, "wires": [ [ "5ea56f3eec020d0f" ] ] }, { "id": "5ea56f3eec020d0f", "type": "debug", "z": "f4a4d8eab7935bc8", "name": "", "active": true, "tosidebar": true, "console": false, "tostatus": false, "complete": "payload", "targetType": "msg", "statusVal": "", "statusType": "auto", "x": 630, "y": 380, "wires": [] }, { "id": "5426aed100d65d31", "type": "inject", "z": "f4a4d8eab7935bc8", "name": "", "repeat": "", "crontab": "", "once": false, "onceDelay": 0.1, "topic": "", "payload": "", "payloadType": "date", "x": 210, "y": 520, "wires": [ [ "a911307ea6b68a16" ] ] }, { "id": "a911307ea6b68a16", "type": "function", "z": "f4a4d8eab7935bc8", "name": "UPDATE", "func": "var id = 3;\nvar name = 'updateName';\nvar pass = '5555';\nmsg.topic = `UPDATE user SET name='${name}',` +\n `pass='${pass}' WHERE id=${id}`;\nreturn msg;", "outputs": 1, "noerr": 0, "initialize": "", "finalize": "", "libs": [], "x": 340, "y": 520, "wires": [ [ "eb4b3bd2a53dbb37", "d991b7f159d90812" ] ] }, { "id": "eb4b3bd2a53dbb37", "type": "mysql", "z": "f4a4d8eab7935bc8", "mydb": "51c3503.61936b", "name": "", "x": 480, "y": 520, "wires": [ [ "c83545834bc01135" ] ] }, { "id": "c83545834bc01135", "type": "debug", "z": "f4a4d8eab7935bc8", "name": "", "active": true, "tosidebar": true, "console": false, "tostatus": false, "complete": "payload", "targetType": "msg", "statusVal": "", "statusType": "auto", "x": 630, "y": 520, "wires": [] }, { "id": "d991b7f159d90812", "type": "debug", "z": "f4a4d8eab7935bc8", "name": "", "active": true, "tosidebar": true, "console": false, "tostatus": false, "complete": "topic", "targetType": "msg", "statusVal": "", "statusType": "auto", "x": 480, "y": 600, "wires": [] }, { "id": "883fb789f22f352f", "type": "inject", "z": "f4a4d8eab7935bc8", "name": "", "props": [ { "p": "payload" }, { "p": "topic", "vt": "str" } ], "repeat": "", "crontab": "", "once": false, "onceDelay": 0.1, "topic": "", "payload": "1", "payloadType": "str", "x": 210, "y": 720, "wires": [ [ "15479540478f4720" ] ] }, { "id": "fd30c40a7a9d0baf", "type": "mysql", "z": "f4a4d8eab7935bc8", "mydb": "51c3503.61936b", "name": "", "x": 480, "y": 720, "wires": [ [ "6eb37dd7c809dd01" ] ] }, { "id": "6eb37dd7c809dd01", "type": "debug", "z": "f4a4d8eab7935bc8", "name": "", "active": true, "tosidebar": true, "console": false, "tostatus": false, "complete": "payload", "targetType": "msg", "statusVal": "", "statusType": "auto", "x": 630, "y": 720, "wires": [] }, { "id": "15479540478f4720", "type": "template", "z": "f4a4d8eab7935bc8", "name": "DELETE", "field": "topic", "fieldType": "msg", "format": "handlebars", "syntax": "mustache", "template": "DELETE FROM user WHERE id='{{payload}}'", "output": "str", "x": 340, "y": 720, "wires": [ [ "fd30c40a7a9d0baf" ] ] } ]\n {{/payload}}\n {{name}} {{pass}}