How to connect to MySQL database using Node-Red

To connect Node-red to the database (mysql), you first need to download the MySQL software on your computer and install Navicat for easy operation. It is already installed by default.

Download controls in Node-Red

In Node-Red, you need to download the required MySQL control first:

1. First, click Settings in the upper left corner and find Node Management ;
2. Click Install in Node Management;
3. Enter the node-red-node-mysql control in the query window of the installation interface, select Download after querying, and wait for the download to complete;
   

Use of mysql control

Create a new local connection root and set Database to the local connection name

Node information <br /> Define the JavaScript code (body of the function) to process the received message.
The input message is passed in a JavaScript object named msg.
Typically, msg.topic must hold the query to the database, and then return the result in the msg.payload attribute.
This function typically returns a message object (or multiple message objects), but can also return nothing in order to stop the flow. Create a database

The nodes that need to be used are inject , function , mysql , and debug .

//function node function writing: create database Data_test
var sql = "CREATE DATABASE Data_test;";
var topic = {"topic":sql};
return topic;

After completion, click the small square of the inject node to complete the creation of the database Data_test, and refresh and view it in Navicat.

Create a data table table_name

The nodes you need to use are inject , function , mysql , debug

//Function node function writing: create data table table_name
var sql = "CREATE TABLE IF NOT EXISTS `runoob_tbl`( `runoob_id` INT UNSIGNED AUTO_INCREMENT, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` DATE, PRIMARY KEY ( `runoob_id` ))ENGINE=InnoDB DEFAULT CHARSET=utf8;";
var topic = {"topic":sql};
return topic;

After completion, click the small square of the inject node to complete the creation of the database table table_name, and refresh and view it in Navicat.

Add student information

Before adding student information, you need to create a new table student in Navicat, which contains name, age, grade, class_name

The nodes you need to use are inject , function , mysql , debug

Method 1

//Function node function writing: add student information var Student="INSERT INTO student(name, age,grade, class_num) VALUES ('wangwu', 11, 4, '3')";
var newMySQLData = { "topic": Student }
return newMySQLData;

Method 2

//Function node function writing: add student information var Student="INSERT INTO student(name, age,grade, class_num) VALUES ('%s', %d, %d, '%s')";
var newMySQLData = {
    "topic": util.format(Student, "lisi",12,6, "1")
}
return newMySQLData;

The student information in method 2 can also be transmitted by selecting {}JSON in the inject node and entering the student information, and parsed in the function in the form of msg.payload.name .

After completion, click the small square of the inject node to complete the addition of student information, and refresh and view it in Navicat.

This is the end of this article about Node-Red to achieve MySQL database connection. For more relevant MySQL database connection content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!