mysql学习笔记3

通过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节点输入相关的数据库操作指令。