MySQL database as the most popular open source database. It is basically one of the database programs that every Web developer must master.

 

The basic use

The most popular mysql package on Node. js is the mysql module.

npm install mysql
Copy the code

Then reference it directly in the JS script

var mysql      = require('mysql');
Copy the code

Configure the mysql database connection.

Var connection = mysql.createconnection ({host: 'IP ', user:' username ', password: 'password ', database: 'dbname'}); connection.connect();Copy the code

So you get a connection.

Then you can happily perform various CURD operations.

Node.js curds to the database are all in the query method. This is very different from ado.net.

All of your operations get results from the query callback function

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});
Copy the code

 

Connection pool operation

In standalone software, we use simple to get a connection between us, and then we’re done.

However, in Internet-oriented Web services, the frequent creation and closing of connections can be costly to server performance.

So our predecessors invented all kinds of pools. For example, thread pools for multithreaded operations, object pools for game development, and of course, connection pools for database operations.

Create a connection pool:

var mysql = require('mysql'); Var pool = mysql.createpool ({connectionLimit: number of connection pools, host: 'IP address ', user:' account ', password: 'password ', database: 'database name'});Copy the code

Then there is the same curD operation as above

// Get a connection from the connection pool

pool.getConnection(function(err, connection) {

  if (err) throw err; // not connected!

 

// Use this connection curd

  connection.query(‘SELECT something FROM sometable’, function (error, results, fields) {

// Add the connection to the pool

    connection.release();

 

    // Handle error after the release.

    if (error) throw error;

 

  });

});

If your program wants to exit, call the end() method of the connection pool. Otherwise, the program will get stuck in the background and fail to exit.

Encapsulated into a Promise

In ES6, asynchronous js functions can be called directly with the same syntax as await in C#.

However, this function must be async and declare and return a Promise object.

Query = function (SQL, arr, callback) {console.log(' get a connection '); return new Promise(function (resolve, reject) { pool.getConnection(function (err, connection) { if (err) { reject(err); // not connected! } else {console.log(' start query '); connection.query(sql, arr, function (error, results, fields) { connection.release(); Console. log(' Connection released, result returned '); if (error) reject(error); // callback && callback(results, fields) resolve({ rows: results, fields: fields }) }); }}); })}Copy the code

The simple usage ends there. Of course, there are more advanced uses, such as MySQL Cluster operation, etc. If you are interested, you can go to research, because I don’t use this function right now, so I won’t go further.

Welcome to join QQ group: 545594312