通过AIoTedge-NodeRED连接MySQL数据库｜最佳实践

Node-RED是一个基于流的开发工具，用于连接物联网设备、API和在线服务。通过Node-RED，可以快速接入物联网设备，实现数据的读取、控制和监控。本文演示采用AIoTedge物联网边缘计算平台集成的NodeRED软网关进行MySQL数据库接入演示，演示Flow流程代码，可以导入到你的NodeRED环境中，直接使用。

AIoTedge NodeRED在线体验地址：https://www.yunteng.com/docs/aiotedge/aiotedge-demo

预览一下

01. 准备工作

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

1-2、执行表结构文件

数据库表结构如下：

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;
-- ------------------------------ Table structure for devicedata-- ----------------------------DROP TABLE IF EXISTS `devicedata`;CREATE TABLE `devicedata`  (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',`attributeName` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '属性名称',`attributeValue` double(10, 2) NULL DEFAULT NULL COMMENT '数据点值',`createTime` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建时间',`tsLong` bigint(255) NULL DEFAULT NULL COMMENT 'TS时间戳',  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '存储时序数据' ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;

02. 详细教程

2-1、示例代码

可导入到你的NodeRED环境中，直接使用。

[{"id":"4660fa22b87d8d64","type":"function","z":"cb5f1a5e643cfd9e","name":"INSERT Data【插入数据】","func":"nvar datetime = formatDateTime();//创建日期格式的时间nvar tsLong = new Date().getTime();nnvar minBound = 15, maxBound = 25;//创建温度模拟数据15~25nvar randomNum = Math.random() * (maxBound - minBound) + minBound;nvar wendu = randomNum.toFixed(2);nnmsg.topic = "INSERT INTO deviceData (attributeName,attributeValue,createTime,tsLong) VALUES (?,?,?,?)";nmsg.payload = ["温度值", wendu, datetime, tsLong];nreturn msg;nn//得到当前日期 【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开始，所以需要+1n    var day = now.getDate(); // 获取日期n    var hours = now.getHours(); // 获取小时n    var minutes = now.getMinutes(); // 获取分钟n    var seconds = now.getSeconds(); // 获取秒数nn    // 将单个数字格式化为两位数（例如，1变为01）n    month = month ,"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 devicedatan-- ----------------------------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 BTREEn) 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"}]