NodeJS connects to MySQL database and performs detailed explanation of adding, deleting, modifying and searching operations

Driver installation

npm install mysql

Connect to the database

In the following example, modify the database user name, password and database name according to your actual configuration:

var mysql = require('mysql');

var connection = ({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'gov'
});

(); // Connect to the database
('select * from gov_policy', function (error, results, fields) {
    if (error) throw error;
 ('The results is: ', results);
 (fields);
});
() // Close the connection

Database connection parameter description

parameter	describe
host	Host address (default: localhost)
user	username
password	password
port	Port number (default: 3306)
database	Database name
charset	Connect the character set (default: 'UTF8_GENERAL_CI', note that the letters of the character set must be capitalized)
localAddress	This IP is used for TCP connections (optional)
socketPath	Connect to the unix domain path, which is ignored when using host and port
timezone	Time zone (default: 'local')
connectTimeout	Connection timeout (default: no limit; unit: milliseconds)
stringifyObjects	Whether to serialize objects
typeCast	Whether to convert column values to local JavaScript type values (default: true)
queryFormat	Custom query statement formatting method
supportBigNumbers	When the database supports bigint or decimal type columns, this option needs to be set to true (default: false)
bigNumberStrings	supportBigNumbers and bigNumberStrings enabled Force bigint or decimal columns to be returned as JavaScript string type (default: false)
dateStrings	Force the timestamp, datetime, data types to be returned as string types, instead of JavaScript Date types (default: false)
debug	Turn on debugging (default: false)
multipleStatements	Is it possible to have multiple MySQL statements in a query (default: false)
flags	Used to modify the connection flag
ssl	Using ssl parameters (to the parameter format one to the parameter format) or a string containing the name of the ssl configuration file, currently only Amazon RDS configuration files are bundled

For more instructions, please refer to:/mysqljs/mysql

Database Operations (CURD)

Before performing database operations, you can operate on existing data, create new data to operate, or use the NodeJS Faker module to generate batches of fake data to operate. The MySQL username, root, password, and gov, you need to modify it according to your own configuration.

Query data

Execute the following code to query the data:

var mysql = require('mysql');

var connection = ({
    host: 'localhost',
    user: 'root',
    password: 'root',
    port: '3306',
    database: 'gov'
});

();

var sql = 'select * from gov_policy';
//check(sql, function (err, result) {
    if (err) {
        ('[SELECT ERROR] - ', );
        return;
    }

    ('--------------------------SELECT----------------------------');
    (result);
    ('------------------------------------------------------------\n\n');
});

();

Execute the above command and the output result is:

$ node 
--------------------------SELECT----------------------------
[ RowDataPacket {
    id: 1,
    name: 'Google',
    url: '/',
    alexa: 1,
    country: 'USA' },
  RowDataPacket {
    id: 2,
    name: 'Taobao',
    url: '/',
    alexa: 13,
    country: 'CN' },
    ......
  RowDataPacket {
    id: 4,
    name: 'Weibo',
    url: '/',
    alexa: 20,
    country: 'CN' },
  RowDataPacket {
    id: 5,
    name: 'Facebook',
    url: '/',
    alexa: 3,
    country: 'USA' } ]
------------------------------------------------------------

Insert data

We can insert data into the data table:

var mysql = require('mysql');

var connection = ({
    host: 'localhost',
    user: 'root',
    password: 'root',
    port: '3306',
    database: 'gov'
});

();

var addSql = 'INSERT INTO websites(Id,name,url,alexa,country) VALUES(0,?,?,?,?)';
var addSqlParams = ['Blog Forum', '', 'NodeJS', 'China'];
//increase(addSql, addSqlParams, function (err, result) {
    if (err) {
        ('[INSERT ERROR] - ', );
        return;
    }

    ('--------------------------INSERT----------------------------');
    //('INSERT ID:',);
    ('INSERT ID:', result);
    ('-----------------------------------------------------------------\n\n');
});

();

Update data

We can also modify the database data:

var mysql = require('mysql');

var connection = ({
    host: 'localhost',
    user: 'root',
    password: 'root',
    port: '3306',
    database: 'gov'
});

();

var modSql = 'UPDATE websites SET name = ?,url = ? WHERE Id = ?';
var modSqlParams = ['SiFu Forum', '', 6];
//change(modSql, modSqlParams, function (err, result) {
    if (err) {
        ('[UPDATE ERROR] - ', );
        return;
    }
    ('--------------------------UPDATE----------------------------');
    ('UPDATE affectedRows', );
    ('-----------------------------------------------------------------\n\n');
});

();

The output result is:

--------------------------UPDATE----------------------------
UPDATE affectedRows 1
-----------------------------------------------------------------

Delete data

We can use the following code to delete data with id 6:

var mysql = require('mysql');

var connection = ({
    host: 'localhost',
    user: 'root',
    password: 'root',
    port: '3306',
    database: 'gov'
});

();

var delSql = 'DELETE FROM websites where id=6';
//delete(delSql, function (err, result) {
    if (err) {
        ('[DELETE ERROR] - ', );
        return;
    }

    ('--------------------------DELETE----------------------------');
    ('DELETE affectedRows', );
    ('-----------------------------------------------------------------\n\n');
});

();

Execute the following command and the output is:

--------------------------DELETE----------------------------
DELETE affectedRows 1
-----------------------------------------------------------------

Stream data reading

When there are tens of millions of data, the above ordinary query operations will cause excessive memory usage, that is, the data will be cached first and then returned to us. If the data is huge, it is necessary to use streaming reading, which takes up less memory:

var mysql = require('mysql');

var connection = ({
    host: 'localhost',
    user: 'root',
    password: 'root',
    port: '3306',
    database: 'gov'
});

();

var sql = 'SELECT * FROM gov_policy_old';

const query = (sql);
('error', function (err) {
    (err);
}).on('fields', function (fields) {
    // (fields);
}).on('result', function (row) {
    (row['title']);
}).on('end', function () {
    ();
})

refer to

/mysqljs/mysql

The above is the detailed explanation of NodeJS's connection to MySQL database and performs addition, deletion, modification and search operations. For more information on NodeJS's connection to MySQL database and performs addition, deletion, modification and search operations, please pay attention to my other related articles!