Introduction to using node to operate mysql database instances

This article mainly introduces how to operate a mysql database by node and analyzes in detail the connection, addition, deletion, modification, and query, transaction processing, and error handling related operation skills of the node database in the form of instances, for more information about node operations on mysql databases, the connection, addition, deletion, modification, query, transaction processing, and error handling related operation skills of the node database are analyzed in detail based on the instance form. For more information, see

This article describes how to operate a mysql database by node. We will share this with you for your reference. The details are as follows:

1. Establish a database connection:createConnection(Object)Method

This method accepts an object as a parameter. This object has four common attributes: host, user, password, and database. The parameter is the same as the parameter used to connect to a database in php. The attribute list is as follows:

Host	Host Name of the database to be connected. (default: localhost)
Port	Connection port. (default: 3306)
LocalAddress	IP address used for TCP connection. (optional)
SocketPath	The path to the unix domain. This parameter is ignored when host and port are used.
User	The username of the MySQL user.
Password	The password of the MySQL user.
Database	The name of the database to be linked to (optional ).
Charset	Character Set to be connected. (default: 'utf8 _ GENERAL_CI '. Set this value to uppercase !)
Timezone	Time zone for storing local time. (default: 'local ')
StringifyObjects	Whether to serialize the object. See issue #501. (default: 'false ')
InsecureAuth	Whether to allow the old authentication method to connect to the database instance. (default: false)
TypeCast	Determine whether to convert the column value to the column value of the local Javascript type. (default: true)
QueryFormat	Format a custom query statement.
SupportBigNumbers	When the database processes large numbers (long integers and decimals), it should be enabled (default: false ).
BigNumberStrings	Enable supportBigNumbers and bigNumberStrings and force these numbers to be returned as strings (default: false ).
DateStrings	The forced DATE type (TIMESTAMP, DATETIME, Date) is returned as a string, instead of a javascript DATE object. (default: false)
Debug	Whether to enable debugging. (default: false)
MultipleStatements	Whether multiple query statements can be passed in one query. (Default: false)
Flags	Link flag.

You can also use a string to connect to a database, for example:

var connection = mysql.createConnection('mysql://user:pass@host/db?debug=true&charset=BIG5_CHINESE_CI&timezone=-0700');

2. End database connectionend()Anddestroy()

End () accepts a callback function and is triggered only after the query is completed. If a query error occurs, the link is terminated and the error is passed to the callback function for processing.

Destroy () immediately terminates the database connection. Even if the query is not completed, the subsequent callback function will not be triggered.

3. Create a connection pool createPool(Object)The Object and createConnection parameters are the same.

You can listen to connection events and set session values.

pool.on('connection', function(connection) { connection.query('SET SESSION auto_increment_increment=1')});

Connection. release () releases the link to the connection pool. To close the connection and delete it, use connection. destroy ()

In addition to accepting the same parameters as connection, the pool also accepts several extended parameters.

CreateConnection	Function used to create a link. (Default: mysql. createConnection)
WaitForConnections	Determines the action of the pool when no connection pool exists or the number of connections reaches the maximum value. If it is set to true, the link will be put into the queue and available is called. If it is set to false, an error will be returned immediately. (Default: true)
ConnectionLimit	Maximum number of connections. (Default: 10)
QueueLimit	The maximum length of the strong connection request in the connection pool. If the length is exceeded, an error is returned. If the value is 0, there is no limit. (Default: 0)

4. Connection Pool Cluster

Allow different host links

// createvar poolCluster = mysql.createPoolCluster();poolCluster.add(config); // anonymous grouppoolCluster.add('MASTER', masterConfig);poolCluster.add('SLAVE1', slave1Config);poolCluster.add('SLAVE2', slave2Config);// Target Group : ALL(anonymous, MASTER, SLAVE1-2), Selector : round-robin(default)poolCluster.getConnection(function (err, connection) {});// Target Group : MASTER, Selector : round-robinpoolCluster.getConnection('MASTER', function (err, connection) {});// Target Group : SLAVE1-2, Selector : order// If can't connect to SLAVE1, return SLAVE2. (remove SLAVE1 in the cluster)poolCluster.on('remove', function (nodeId) {   console.log('REMOVED NODE : ' + nodeId); // nodeId = SLAVE1});poolCluster.getConnection('SLAVE*', 'ORDER', function (err, connection) {});// of namespace : of(pattern, selector)poolCluster.of('*').getConnection(function (err, connection) {});var pool = poolCluster.of('SLAVE*', 'RANDOM');pool.getConnection(function (err, connection) {});pool.getConnection(function (err, connection) {});// destroypoolCluster.end();

Optional parameter of the linked Cluster

CanRetry	If the value is true, allow retry upon connection failure (Default: true)
RemoveNodeErrorCount	When the connection fails, the errorCount value will increase. When the errorCount value is greater than removeNodeErrorCount, a node will be deleted from PoolCluster. (Default: 5)
DefaultSelector	Default selector. (Default: RR)
RR	Loop. (Round-Robin)
RANDOM	Select a node using a random function.
ORDER	Select the first available node unconditionally.

5. Switch user/change connection status

Mysql allows users to be switched when the connection is disconnected.

connection.changeUser({user : 'john'}, function(err) { if (err) throw err;});

Parameters

User	New users (the default is the previous one ).
Password	The new password of the new user (the default is the previous one ).
Charset	New character set (the default value is the previous one ).
Database	New Database Name (the default value is the previous one ).

6. process server disconnection

var db_config = {  host: 'localhost',  user: 'root',  password: '',  database: 'example'};var connection;function handleDisconnect() { connection = mysql.createConnection(db_config); // Recreate the connection, since                         // the old one cannot be reused. connection.connect(function(err) {       // The server is either down  if(err) {                   // or restarting (takes a while sometimes).   console.log('error when connecting to db:', err);   setTimeout(handleDisconnect, 2000); // We introduce a delay before attempting to reconnect,  }                   // to avoid a hot loop, and to allow our node script to });                   // process asynchronous requests in the meantime.                     // If you're also serving http, display a 503 error. connection.on('error', function(err) {  console.log('db error', err);  if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually   handleDisconnect();             // lost due to either server restart, or a  } else {                   // connnection idle timeout (the wait_timeout   throw err;                 // server variable configures this)  } });}handleDisconnect();

7. Escape query value

To avoid SQL injection attacks, You must escape the data submitted by the user. Availableconnection.escape()Orpool.escape()

For example:

var userId = 'some user provided value';var sql  = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);connection.query(sql, function(err, results) {   // ...});

Or use? As a placeholder

connection.query('SELECT * FROM users WHERE id = ?', [userId], function(err, results) {   // ...});

Conversion results of different types of Values

Numbers unchanged
Booleans converts the string to 'true'/'false'
The Date object is converted to the string 'yyyy-mm-dd HH: ii: ss'
Buffers is converted to a 6-digit string.
Strings unchanged
Arrays => ['A', 'B'] to 'A', 'B'
Nested arrays [['A', 'B'], ['C', 'D'] are converted to ('A', 'B'), ('C ', 'd ')
Objects is converted to key = 'val 'pairs. The nested object is converted to a string.
Undefined/null ==> NULL
NaN/Infinity remains unchanged. MySQL does not support these values. inserting these values will cause errors unless supported by tools.

Conversion instance:

var post = {id: 1, title: 'Hello MySQL'};var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {   // Neat!});console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

Or manually convert

var query = "SELECT * FROM posts WHERE title=" + mysql.escape("Hello MySQL");console.log(query); // SELECT * FROM posts WHERE title='Hello MySQL'

8. Convert the query identifier

If you cannot trust the SQL identifier (Database Name, table name, and column name), you can use the conversion method mysql. escapeId (identifier );

var sorter = 'date';var query = 'SELECT * FROM posts ORDER BY ' + mysql.escapeId(sorter);console.log(query); // SELECT * FROM posts ORDER BY `date`

Multiple meanings are supported.

var sorter = 'date';var query = 'SELECT * FROM posts ORDER BY ' + mysql.escapeId('posts.' + sorter);console.log(query); // SELECT * FROM posts ORDER BY `posts`.`date`

Yes ?? As a placeholder for the identifier

var userId = 1;var columns = ['username', 'email'];var query = connection.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId], function(err, results) {   // ...});console.log(query.sql); // SELECT `username`, `email` FROM `users` WHERE id = 1

9. Prepare for query

You can use mysql. format to prepare query statements. This function automatically selects appropriate method escape parameters.

var sql = "SELECT * FROM ?? WHERE ?? = ?";var inserts = ['users', 'id', userId];sql = mysql.format(sql, inserts);

10. Custom formatting Functions

connection.config.queryFormat = function (query, values) {   if (!values) return query;   return query.replace(/\:(\w+)/g, function (txt, key) {    if (values.hasOwnProperty(key)) {     return this.escape(values[key]);    }    return txt;   }.bind(this));};connection.query("UPDATE posts SET title = :title", { title: "Hello MySQL" });

11. Get the id of the inserted row

Obtains the id of the inserted row when the auto-increment primary key is used, for example:

connection.query('INSERT INTO posts SET ?', {title: 'test'}, function(err, result) {   if (err) throw err;   console.log(result.insertId);  });

12. Stream Processing

Sometimes you want to select a large number of rows and process them when the data arrives, you can use this method.

var query = connection.query('SELECT * FROM posts');  query   .on('error', function(err) {    // Handle error, an 'end' event will be emitted after this as well   })   .on('fields', function(fields) {    // the field packets for the rows to follow   })   .on('result', function(row) {    // Pausing the connnection is useful if your processing involves I/O    connection.pause();    processRow(row, function() {     connection.resume();    });   })   .on('end', function() {    // all rows have been received   });

13. Hybrid query statement (Multi-statement query)

Because hybrid queries are vulnerable to SQL injection attacks, they are not allowed by default. You can use:

var connection = mysql.createConnection({multipleStatements: true});

Enable this function.

Hybrid query instance:

connection.query('SELECT 1; SELECT 2', function(err, results) {   if (err) throw err;   // `results` is an array with one element for every statement in the query:   console.log(results[0]); // [{1: 1}]   console.log(results[1]); // [{2: 2}]  });

You can also use stream processing to process the mixed query results:

var query = connection.query('SELECT 1; SELECT 2');  query   .on('fields', function(fields, index) {    // the fields for the result rows that follow   })   .on('result', function(row, index) {    // index refers to the statement this result belongs to (starts at 0)   });

If one of the query statements has an Error, the Error object will contain the id of the err. index indicating the Error statement, and the entire query will be terminated.

The stream processing method of mixed query results is experimental and unstable.

14. Transaction Processing

Simple transaction processing at the connection level

connection.beginTransaction(function(err) {   if (err) { throw err; }   connection.query('INSERT INTO posts SET title=?', title, function(err, result) {    if (err) {     connection.rollback(function() {      throw err;     });    }    var log = 'Post ' + result.insertId + ' added';    connection.query('INSERT INTO log SET data=?', log, function(err, result) {     if (err) {      connection.rollback(function() {       throw err;      });     }     connection.commit(function(err) {      if (err) {       connection.rollback(function() {        throw err;       });      }      console.log('success!');     });    });   });  });

15. handle errors

err.code = stringerr.fatal => boolean

The above describes how to use node to operate mysql database instances. For more information, see other related articles in the first PHP community!