How to operate MySQL database

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

Basic use

On the top, the most popular mysql package is the mysql module.

npm install mysql

Then directly reference it in the js script

var mysql   = require('mysql');

Configure the database connection of mysql.

var connection = ({
 host   : 'ip',
 user   : 'username',
 password : 'password',
 database : 'dbname'
});
();

This gives you a connection.

Then you can perform various curd operations happily.

Curds to the database are all in the query method. This is very different from that.

All your operations are obtained from the query callback function

('SELECT 1 + 1 AS solution', function (error, results, fields) {
 if (error) throw error;
 ('The solution is: ', results[0].solution);
});

Connection pool operation

In stand-alone software, we use it simply to get a connection and then it's all.

However, in Internet-oriented web services, frequent creation and closing of connections consumes server performance.

So our predecessors invented various pools. For example, thread pools in multi-threaded operations, object pools in game development, and of course, connection pools for database operations.

Create a connection pool:

var mysql = require('mysql');
var pool = ({
 connectionLimit : Number of connection pools,
 host      : 'ip address',
 user      : 'account',
 password    : 'password',
 database    : 'Database Name'
});

Then there is the curd operation as above

//Get a connection from the connection pool(function(err, connection) {
  if (err) throw err; // not connected!
 
  // Use this connection to curd  ('SELECT something FROM sometable', function (error, results, fields) {
    // After using it, remember to put this connection into the connection pool    ();
 
    // Handle error after the release.
    if (error) throw error;
 
  });
});

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

Packaged into Promise

In ES6, you can directly call js' asynchronous functions using the same syntax as C#'s await.

However, this function must be async declaration and return value is a Promise object.

query = function (sql, arr, callback) {
  ('Get a connection');
  return new Promise(function (resolve, reject) {
    (function (err, connection) {
      if (err) {
        reject(err);// not connected!
      } else {
        ('Start query');
        (sql, arr, function (error, results, fields) {
          ();
          ('The connection has been released, return the result');

          if (error) reject(error);
          // callback && callback(results, fields)
          resolve({
            rows: results,
            fields: fields
          })
        });
      }


    });
  })


}

The simple usage ends here. Of course, there are more advanced usages, such as MySQL's Cluster operation, etc. If you are interested, you can study it, because I can't use this function at the moment, so I won't go into it further.

The above is the detailed content of how to operate MySQL database. For more information about operating the database, please follow my other related articles!