Node-RED软网关使用手册-最佳实践（MySQL数据库—示例）

云腾小编

2025年2月12日2025年2月10日

提示💡：下面所有操作，都是已经安装好mysql数据库的情况下操作；具体，如何安装数据库，这里不说明。

1、准备工作

1-1、创建数据库名【nodered】,字符集【utf8】;

1-2、执行表结构文件

下面为示例数据库的表结构:

deviceData.sql

2、MySQL数据库常规操作

2-1、示例代码

[{"id":"4660fa22b87d8d64","type":"function","z":"cb5f1a5e643cfd9e","name":"INSERT Data【插入数据】","func":"\nvar datetime = formatDateTime();//创建日期格式的时间\nvar tsLong = new Date().getTime();\n\nvar minBound = 15, maxBound = 25;//创建温度模拟数据15~25\nvar randomNum = Math.random() * (maxBound - minBound) + minBound;\nvar wendu = randomNum.toFixed(2);\n\nmsg.topic = \"INSERT INTO deviceData (attributeName,attributeValue,createTime,tsLong) VALUES (?,?,?,?)\";\nmsg.payload = [\"温度值\", wendu, datetime, tsLong];\nreturn msg;\n\n//得到当前日期 【YYYY-MM-DD HH:mm:ss】\nfunction formatDateTime() {\n    var now = new Date();\n    var year = now.getFullYear(); // 获取年份\n    var month = now.getMonth() + 1; // 获取月份，月份从0开始，所以需要+1\n    var day = now.getDate(); // 获取日期\n    var hours = now.getHours(); // 获取小时\n    var minutes = now.getMinutes(); // 获取分钟\n    var seconds = now.getSeconds(); // 获取秒数\n\n    // 将单个数字格式化为两位数（例如，1变为01）\n    month = month < 10 ? '0' + month : month;\n    day = day < 10 ? '0' + day : day;\n    hours = hours < 10 ? '0' + hours : hours;\n    minutes = minutes < 10 ? '0' + minutes : minutes;\n    seconds = seconds < 10 ? '0' + seconds : seconds;\n\n    // 拼接成最终的字符串\n    var dateTimeStr = year + '-' + month + '-' + day + ' ' + hours + ':' + minutes + ':' + seconds;\n    return dateTimeStr;\n}\n\n//msg.topic = \"INSERT INTO user (name,address,year) VALUES (?,?,?)\";\n//msg.payload = [ \"test老大\",\"成都高新区\",\"2021-10-28\"];\n//return msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":380,"wires":[["1d1af8373ab6c326","c454653a7d28bfbe"]]},{"id":"026e258025329cde","type":"inject","z":"cb5f1a5e643cfd9e","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payloadType":"date","x":90,"y":380,"wires":[["4660fa22b87d8d64"]]},{"id":"402e02dd0e5b188f","type":"debug","z":"cb5f1a5e643cfd9e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":690,"y":500,"wires":[]},{"id":"26dbb51b82fc931c","type":"inject","z":"cb5f1a5e643cfd9e","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payloadType":"date","x":90,"y":440,"wires":[["6ea9847db36c1e83"]]},{"id":"6ea9847db36c1e83","type":"function","z":"cb5f1a5e643cfd9e","name":"Update Data【更新数据】","func":"msg.topic = \"Update deviceData Set attributeValue = ? where id=?\";\nmsg.payload=['22.55','4']\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":270,"y":440,"wires":[["1d1af8373ab6c326"]]},{"id":"2ed4f22e8e973be0","type":"function","z":"cb5f1a5e643cfd9e","name":"Delete Data【删除数据】","func":"msg.topic = \"Delete From deviceData where id = ?\";\nmsg.payload = [\"4\"];\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":270,"y":580,"wires":[["1d1af8373ab6c326"]]},{"id":"2b7a45f6001c3eb5","type":"inject","z":"cb5f1a5e643cfd9e","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payloadType":"date","x":90,"y":580,"wires":[["2ed4f22e8e973be0"]]},{"id":"71f48d84444e52ed","type":"inject","z":"cb5f1a5e643cfd9e","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"warehouse","payloadType":"str","x":90,"y":520,"wires":[["244bfcbfc5d9355c"]]},{"id":"244bfcbfc5d9355c","type":"function","z":"cb5f1a5e643cfd9e","name":"SELECT Data【查询数据】","func":"msg.topic = \"SELECT * FROM deviceData  order by tsLong desc LIMIT 10;\";\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":520,"wires":[["1d1af8373ab6c326"]]},{"id":"f9e725cdeb9db454","type":"comment","z":"cb5f1a5e643cfd9e","name":"mysql常规操作：使用时，注意配置好的数据库地址\\用户名\\密码","info":"","x":250,"y":320,"wires":[]},{"id":"a8144cc72bc2c57f","type":"comment","z":"cb5f1a5e643cfd9e","name":"mysql模拟测试-双击打开查看【操作表的结构】","info":"-- ----------------------------\n-- Table structure for devicedata\n-- ----------------------------\nDROP TABLE IF EXISTS `devicedata`;\nCREATE TABLE `devicedata`  (\n  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',\n  `attributeName` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '属性名称',\n  `attributeValue` double(10, 2) NULL DEFAULT NULL COMMENT '数据点值',\n  `createTime` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建时间',\n  `tsLong` bigint(255) NULL DEFAULT NULL COMMENT 'TS时间戳',\n  PRIMARY KEY (`id`) USING BTREE\n) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '存储时序数据' ROW_FORMAT = Dynamic;","x":200,"y":260,"wires":[]},{"id":"1d1af8373ab6c326","type":"mysql","z":"cb5f1a5e643cfd9e","mydb":"b2e1fbf20d178b80","name":"mysql数据库操作","x":510,"y":500,"wires":[["402e02dd0e5b188f"]]},{"id":"c454653a7d28bfbe","type":"debug","z":"cb5f1a5e643cfd9e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":550,"y":380,"wires":[]},{"id":"f627c7d851c92678","type":"comment","z":"cb5f1a5e643cfd9e","name":"双击打开查看【官方使用文档】","info":"官方使用文档：\nhttps://yunteng.yuque.com/to2an3/rrd8yw/vnyz2422t47m2gk7","x":150,"y":200,"wires":[]},{"id":"b2e1fbf20d178b80","type":"MySQLdatabase","name":"mysql数据库","host":"192.168.1.31","port":"3306","db":"nodered","tz":"","charset":"UTF8"}]