Node-red操作数据库MySQL

node-Red 操作数据库

教程环境：

​ **Mysql8.0 **（本地window）

​ node-red-node-mysql-v1.0.3

​ Navicat15

​ Node-red-v3.0.1

具备能力参考

​ mysql基础

​ node.js 基础

​ 电脑使用基础

---

参考学习文献：

​ mysql远程连接权限grant all privileges on . to ‘root‘@‘%‘ identified by ‘123456‘ with grant option语句报错_chenshida_的博客-CSDN博客

​ MySQL开启远程访问权限-阿里云开发者社区 (aliyun.com)

​ Node-red MySQL 节点手册

​ MySQL 教程 | 菜鸟教程 (runoob.com)

​ MySQL 函数 | 菜鸟教程 (runoob.com)

环境配置

​ 安装数据库，个人推荐学生使用社区版，免费够用。（我是小白）

​ Mysql 安装教程： mysql8.0.25安装配置教程(windows 64位)最详细_聚精会神搞学习的博客-CSDN博客_mysql安装教程8.0.25 感谢大佬分享

​ 下载完Mysql之后，使用一个数据库操作软件：Navicat

​ Navicat下载安装教程：Navicat Premium15安装与激活(完整激活版) - 小芒果测开笔记 - 博客园 (cnblogs.com)

​ Node-red 需要自己安装，不提供教程（可以单独是由Node-red 也可以和Home Assistant一起使用）推荐后者

---

在Node-red中安装Mysql节点

流程：进入节点管理器——> 选择安装选项 输入：Mysql

​ 选择node-red-node-mysql 点击安装即可

​ 等待下载完成

结果显示：

---

Mysql 设置

-------------------------Mysql 准备操作

​ 在上文中的MySQL中安装，按教程安装到第六步结束即可.

这里需要注意

​ 在虚拟机中安装Mysql 和在Window安装Mysql 在后面的使用Mysql 的步骤是有区别的

​ ①Mysql 安装在Window下，node-red 安装在虚拟机（即linux环境下）

​ 需要开启Mysql 的远程访问权限（这里有坑的，详细解决方案参考上述文献），操作请看下面的操作

​ ②Mysql和Node-red 同时安装在Window下（<<<<<<作者环境)

​ 直接使用就好了

打开Mysql 服务器 ，登录Mysql （需要一直保持运行）

打开Navicat 点击连接 选择 MySQL ，输入连接名称及密码，其他地方不需要操作

注意：这里可以点击测试连接，用来测试密码或者其他设置是否正确，正确提示：连接成功

【图片：测试连接：连接成功】

流程：开启Mysql本地服务器——> 使用Navicat 连接数据库

---

-------------------------Mysql 创建数据库及表（方法一）<推荐使用这种方式创建表>

①、双击连接好的MySQL服务器,这时服务器开始连接，连接成功小海豚变绿色

​ 数据库服务器中会自带四个数据库

②、创建数据库

​ 在数据库服务器上右键，唤出右键菜单 新建数据库

③、填写数据库信息

​ 填写 数据库名、字符集（使用中文：gb2312）、排序规则（可以不填）

这是会在数据库服务器上出现一个灰色的数据库，可以双击打开，也可以右键打开

④、创建表

​ 在表上右键新建表，创建字段

​ 创建完成点击保存

点击确定

这里如果没有刷新，可以右键 ‘表’ ——>刷新就会出现表了，前提正确设置

需要注意的是：字段为主键 ，不接受NULL输入 ，所以 选择了主键 必须选择 “不是null" 单选框，不然报错

双击创建的表就会出现我们设置好的表

为此创建表格完成

---

-------------------------Mysql 创建表（方法二）<使用Node-red创建表>

这里需要先提前创建好一个数据库

因为Node-red 在使用Mysql 节点时需要配置好数据的连接

打开Node-red，拉出一个function节点，inject节点（默认变成时间戳），debug节点，Mysql 节点，分别依次连接起来

时间戳————Function————Mysql————Debug

双击 Mysql 节点，弹出编辑Mysql节点，点击小铅笔图案（默认Database是：添加新的 MySQLdatabase 节点）

按照我的方式填入信息

填写完成后点击添加

点击部署，如果数据库正常的话，部署完之后会在Mysql 节点下面出现 ” connected “字样

完成Node-red 连接 Mysql 操作

---

创建表

在function节点中写入下面的代码（双击打开）

msg.topic ="CREATE TABLE `adminTest` (   `ID` int(16) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '主键ID',   `AdminGroupID` bigint unsigned NOT NULL COMMENT '组ID',   `Name` varchar(255) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL COMMENT '名称',   `PassWord` varchar(40) NOT NULL COMMENT '密码，权限操作',   `EnterUseDT` datetime(6) NOT NULL COMMENT '卡号添加时间',   `StudentID` bigint unsigned NOT NULL COMMENT '学生ID,不可重复，主键',   `Sex` int(4) unsigned zerofill NOT NULL COMMENT '性别，长度不足填充零',   `Birthday` datetime(6) DEFAULT NULL COMMENT '生日',   `IDCardNumber` varchar(40) NOT NULL COMMENT '身份证号，强制主键',   `IPhoneNumber` varchar(40) NOT NULL COMMENT '电话，强制主键',   `Address` varchar(200) DEFAULT NULL COMMENT '地址',   `Memo` varchar(400) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci DEFAULT NULL COMMENT '填充备注',   PRIMARY KEY (`ID`,`StudentID`,`IDCardNumber`,`IPhoneNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk;";return msg;

配置Debug节点 ，每次修改之后都要部署一下，这样子修改的配置才会生效

Debug节点 配置 ：输出 —> 与调试输出相同（选择这个的原因是，输出的信息比较多），修改完成后点击 完成

执行我们的我们的操作 ，点击时间戳，Debug 窗口就会出现 响应的信息

在Navicat 中我们可以看到我们创建的表

为此：完成使用Node-red 创建表的操作

数据库的后续操作直接可以修改msg.topic的值，来对数据库进行相关的操作

---

Node-red 操作MySQL数据库的增删改查操作

一：MySQL 数据库的插入操作（增）

在function节点中写入下面的代码：

//数据插入msg.topic="INSERT INTO AdminTest(ID,AdminGroupID,Name,Password,EnterUseDT,StudentID,Sex,Birthday,IDCardNumber,IPhoneNumber,Address,Memo)Value(NULL,1,2,3,NOW(),5,6,NOW(),8,9,0,11);";return msg;

//这个代码我会发布一个流程，导入流程就可以看见。//将上述代码写入function节点，--->完成---> 部署即可,点击时间戳，运行即可

INSERT INTO 表名 ( 列1名称, field2,...fieldN )                VALUES                ( 列1对应的值, value2,...valueN )                

上述是在数据库插入数据的代码格式↑

---

需要注意的是，时间：我们用MySQL的 函数来获取，如果是上传数据的形式，就不要选择datetime 类型

当前时间使用函数 ：NOW() 返回当前时间

MySQL 函数参考：

MySQL 函数 | 菜鸟教程 (runoob.com)

二：MySQL 数据库的查询操作（查）

//查询数据msg.topic="SELECT Name ,ID from admintest WHERE name='CSDN';";return msg;

SELECT column_name,column_nameFROM table_name[WHERE Clause][LIMIT N][ OFFSET M]

数据库查询代码格式↑

---

SELECT 返回对应列的信息1,column_name
FROM 表名
[WHERE Clause]
[LIMIT N][ OFFSET M]

例子：
SELECT runoob_id, runoob_title,
runoob_author, submission_date
FROM runoob_tbl //这里可以查询多个表用 ‘ ，’ 隔开
WHERE runoob_author=“RUNOOB.COM”;

------如果查到了数据
这里会返回所在行 的runoob_id， runoob_title，runoob_author，submission_date 数据

------返回所在行所有的数据( 用 * 表示所有数据)
SELECT * from runoob_tbl WHERE runoob_author=‘菜鸟教程’;

------ MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
------mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author=‘RUNOOB.COM’;

三：MySQL 数据库的删除操作（删）

//删除数据：msg.topic="DELETE FROM AdminTest WHERE name='华子肖肖';";return msg;

DELETE FROM table_name [WHERE Clause]

删除数据 格式↑：

---

DELETE FROM 表名 [WHERE Clause]

------例子：

DELETE FROM runoob_tbl WHERE runoob_id=3;

------查询到对应的那一行，执行删除

------也可以全部都删除

------记得要备份好再删除

Note:

------如果没有指定WHERE 语句 ，执行全部删除操作

四：MySQL 数据库的修改操作（改）

//更新数据msg.topic="UPDATE admintest SET Name='华子肖肖' WHERE Name='CSDN'; ";return msg;

UPDATE table_name SET field1=new-value1, field2=new-value2[WHERE Clause]

更新数据 代码格式↑：

---

UPDATE table_name SET 列名= 新的值 , field2=new-value2

[WHERE Clause]

------例子

UPDATE runoob_tbl SET runoob_title=‘学习 c++’ WHERE runoob_id=3;

------更新符合条件的那一行数据里面的 列名 对应的参数

------

你可以同时更新一个或多个字段。 （多个列名）

你可以在 WHERE 子句中指定任何条件。

你可以在一个单独表中同时更新数据。

五：MySQL 数据库的创建数据表操作(详细补充)

msg.topic = "CREATE TABLE `adminTest` (   `ID` int(16) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '主键ID',   `AdminGroupID` bigint unsigned NOT NULL COMMENT '组ID',   `Name` varchar(255) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL COMMENT '名称',   `Password` varchar(40) NOT NULL COMMENT '密码，权限操作',   `EnterUseDT` datetime(6) NOT NULL COMMENT '卡号添加时间',   `StudentID` bigint unsigned NOT NULL COMMENT '学生ID,不可重复，主键',   `Sex` int(4) unsigned zerofill NOT NULL COMMENT '性别，长度不足填充零',   `Birthday` datetime(6) DEFAULT NULL COMMENT '生日',   `IDCardNumber` varchar(40) NOT NULL COMMENT '身份证号，强制主键',   `IPhoneNumber` varchar(40) NOT NULL COMMENT '电话，强制主键',   `Address` varchar(200) DEFAULT NULL COMMENT '地址',   `Memo` varchar(400) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci DEFAULT NULL COMMENT '填充备注',   PRIMARY KEY (`ID`,`StudentID`,`IDCardNumber`,`IPhoneNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk;";//创建表 return msg;

这里可以现在Navicat中创建好表，然后复制Mysql 数据库代码过来即可

（虽然这种方法还不如直接在Navicat 中直接创建来的实际，但是可以学习，怎么创建一个表，在其他软件使用到MySQL 时，至少我们学习过）

六：MySQL 数据库的另一种查询操作 LIKE

//查找数据：msg.topic ="SELECT * from AdminTest WHERE Name Like '%肖肖';";return msg;

SELECT field1, field2,...fieldN FROM table_nameWHERE field1 LIKE condition1(条件1) [AND [OR]] filed2 = 'somevalue'

模糊查询格式↑

---

SELECT field1, field2,…fieldN

FROM table_name

WHERE field1 LIKE condition1(条件1) [AND [OR]] filed2 = ‘somevalue’

------例子：

SELECT * from runoob_tbl WHERE runoob_author LIKE ‘%COM’;

------

​ 这是模糊查找和查找的方法使用方法有所不同，但结果相同

​ 模糊搜索可以比对局部相似，查找只能比对相同的值

​ 在like 中 '%‘字符用来表示任何字符，如果没有使用’%'LIKE 子句和等号的效果一样

------其他用法：

like 匹配/模糊匹配，会与 % 和 _ 结合使用。

‘%a’ //以a结尾的数据

‘a%’ //以a开头的数据

‘%a%’ //含有a的数据

‘a’ //三位且中间字母是a的

‘_a’ //两位且结尾字母是a的

‘a_’ //两位且开头字母是a的

------例子：

查询以 java 字段开头的信息。

SELECT * FROM position WHERE name LIKE ‘java%’;

查询包含 java 字段的信息。

SELECT * FROM position WHERE name LIKE ‘%java%’;

查询以 java 字段结尾的信息。

SELECT * FROM position WHERE name LIKE ‘%java’;

-----

在 where like 的条件查询中，SQL 提供了四种匹配方式。

%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。

_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。

[]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

[^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。
[9,10,11,12] []其中的一个

[9,10,11,12] [^]不在其中的一个

-----

​ 你可以在 WHERE 子句中指定任何条件。

​ 你可以在 WHERE 子句中使用LIKE子句。

​ 你可以使用LIKE子句代替等号 =。

​ LIKE 通常与 % 一同使用，类似于一个元字符的搜索。

​ 你可以使用 AND 或者 OR 指定一个或多个条件。

​ 你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

具体更多的教程请参考： MySQL 教程 | 菜鸟教程 (runoob.com)

---

将获取到的数据提取出来

需要获取里面的Name的时候可以这样子写：

msg.MySQL_Name=msg.payload[0]["Name"];return msg;

Node-red 操作MySQL 的操作说明：

需要防止MySQL 注入

msg.topic 保存的是对数据库的操作代码，当数据流流入MySQL 节点时会执行数据库的相应操作

msg.topic ="CREATE TABLE `adminTest` (   `ID` int(16) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '主键ID',   `AdminGroupID` bigint unsigned NOT NULL COMMENT '组ID',   `Name` varchar(255) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL COMMENT '名称',   `Password` varchar(40) NOT NULL COMMENT '密码，权限操作',   `EnterUseDT` datetime(6) NOT NULL COMMENT '卡号添加时间',   `StudentID` bigint unsigned NOT NULL COMMENT '学生ID,不可重复，主键',   `Sex` int(4) unsigned zerofill NOT NULL COMMENT '性别，长度不足填充零',   `Birthday` datetime(6) DEFAULT NULL COMMENT '生日',   `IDCardNumber` varchar(40) NOT NULL COMMENT '身份证号，强制主键',   `IPhoneNumber` varchar(40) NOT NULL COMMENT '电话，强制主键',   `Address` varchar(200) DEFAULT NULL COMMENT '地址',   `Memo` varchar(400) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci DEFAULT NULL COMMENT '填充备注',   PRIMARY KEY (`ID`,`StudentID`,`IDCardNumber`,`IPhoneNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk;";

返回的内容放在msg.payload 中 返回的数据是RowDataPacket 类型，但不是完全标准的对象

---

写在结尾：

MySQL 开启远程访问的操作

打开MySQL 服务器：登录

【图片：出现Wellcome】

先查看MySQL 当前远程访问权限

use mysqlselect  User,authentication_string,Host from user;

开启远程访问权限

方法一:改表法

将用户权限中”host“项，改成”%“，即所有连接皆可访问

update user set host='%' where user='root';

方法二：授权法

通过GRANT命令可以授予主机远程访问权限

--这里需要注意：MySQL 8.0之前直接使用下面的代码即可（无标注字样代码），8.0之后（包括8.0）执行8.0的代码--赋予任何主机访问权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;--允许指定主机(IP地址)访问权限：GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'root' WITH GRANT OPTION;--8.0之后版本开启远程访问权限 代码create user root@'%' identified by '123456';grant all privileges on *.* to root@'%' with grant option;--创建完账户，也可以使用 方法一 修改远程访问权限

–需要先创建一个账户，然后在修改账户的权限，这里不推荐直接改 root 的远程访问权限
–具体在上文的参考文献中有说明
–8.0之后的操作👆
–root 账号 by 后面是 密码

为此MySQL 开启远程访问权限操作完成

---

在window中设置防火墙的入站规则，才可以使用

按照图片设置的方式就可以了

---

编者注：能力有限，不足之处请指出。

​ MySQL 是初学者
配套的流程图节点：Node-red 控制ＭｙＳＱＬ数据库例程

​

来源地址：https://blog.csdn.net/qq_45086597/article/details/126476587