通過Node-red對mysql數據庫進行操作

1、環境配置

操作系統

宿主機：UBUNTU
虛擬環境：KVM
虛擬機1：Armbian
虛擬機2：Debian

網絡

虛擬網絡（默認的default配置)：

+-------------------+          +-------------------+
|                   |          |                   |
|  外部網絡 (Internet)          |   宿主機 (Host)    |
|                   |          |                   |
+--------+----------+          +---------+---------+
         |                            |
         |                            |
         |                            |
         |                            |
+--------+---------+            +-----+------+            +-----+------+
|                  |            |             |           |             |
|  virbr0 (192.168.122.1)       |  vnet0      |           |  vnet1      |
|  (Bridge)        |            |  (Armbian)  |           |  (Debian)   |
|  (NAT)           |            |  (IP: 192.168.122.43)   |  (IP: 192.168.122.245)|
+------------------+            +--------------+          +--------------+

軟件

Armbian: Node-red容器

$ sudo docker ps
CONTAINER ID   IMAGE              COMMAND             CREATED       STATUS                             PORTS                                       NAMES
5a7772b55fa0   nodered/node-red   "./entrypoint.sh"   6 weeks ago   Up 19 seconds (health: starting)   0.0.0.0:1880->1880/tcp, :::1880->1880/tcp   mynodered

Debian: Mysql

2、Node-red節點使用

node-red-node-mysql
配置

IP填寫Mysql-server所在虛擬機的IP。
用戶名和密碼按照實際填寫。具體可參見mysql學習筆記1

3、流程配置

節點內容，以創建表格節點為例：

如圖所示，給msg.payload賦字符串，內容就是創建表格的命令。具體可參見mysql學習筆記2。
以此類推，可以實現表格創建刪除以及表格內容的插入和刪除。

4.結果呈現

ssh登陸Debian虛擬機，進入Mysql。

$ sudo mysql -u John_Lenon -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students       |
+----------------+
1 row in set (0.001 sec)

MariaDB [test]> 

可以看到，在test的database下，目前有一個叫students的table。

Node-red界面進行表格插入

點擊插入表格

Mysql查看

MariaDB [test]> show tables;
+--------------------+
| Tables_in_test     |
+--------------------+
| students           |
| weather_of_Beijing |
+--------------------+
2 rows in set (0.001 sec)

MariaDB [test]> select * from weather_of_Beijing;
Empty set (0.001 sec)

可以看到多了一張表格

Node-red界面進行數據插入

Mysql查看

MariaDB [test]> select * from weather_of_Beijing;
+----+---------------------+------+----------+
| id | timestamp           | temp | humidity |
+----+---------------------+------+----------+
|  1 | 2024-10-17 00:08:52 | 25.5 |     60.2 |
+----+---------------------+------+----------+
1 row in set (0.001 sec)

同樣，可以進行表格和數據的刪除等操作。
其實質就是通過流程，對Mysql節點輸入相關的數據庫操作指令。