Node. Common methods of JavaScript operating MySQL in js are compiled
- 2021-01-18 06:14:56
- OfStack
1. Establish database connection: createConnection(Object) method
This method takes an object with four commonly used properties, host, user, password, and database. The same parameters as the linked database in php. The list of properties is as follows:
You can also connect to a database using strings. For example:
var connection = mysql.createConnection('mysql://user:pass@host/db?debug=true&charset=BIG5_CHINESE_CI&timezone=-0700');
end(); destroy(); destroy()
end() accepts a callback function and will not fire until the end of query. If ES72en fails, the link will still be terminated and the error will be passed to the callback function for handling.
destroy() immediately terminates the database connection, and any subsequent callbacks will not be fired even if query remains unfinished.
Create connection pool createPool(Object)
Object and createConnection have the same parameters.
You can listen for connection events and set the session value
pool.on('connection', function(connection) {
connection.query('SET SESSION auto_increment_increment=1')
});
connection.release () frees the link to the connection pool. If you need to close the connection and delete it, use connection.destroy ()
pool accepts several extended parameters in addition to the same parameters as connection
createConnection: The function used to create links. (Default: mysql.createConnection)
waitForConnections: Determines the behavior of pool when there is no connection pooling or when the maximum number of links is reached. For true, the link will be queued and invoked when available. For false, it will immediately return error. (Default: true)
connectionLimit: Maximum number of connections (Default: 10)
queueLimit: Maximum length of connection requests in the pool, exceeding which error will be reported. A value of 0 has no limit.
4. Connect pool clusters
Allows different host links
// create
var poolCluster = mysql.createPoolCluster();
poolCluster.add(config); // anonymous group
poolCluster.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-robin
poolCluster.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) {});
// destroy
poolCluster.end();
Optional parameter to link the cluster
canRetry: With a value of true, retry is allowed if the connection fails (Default: true)
removeNodeErrorCount: The value of errorCount is increased when the connection fails. When the value of errorCount is greater than removeNodeErrorCount, one node will be removed from PoolCluster.
5. Switch users/change connection state
Mysql allows users to be switched from one connection to the other
connection.changeUser({user : 'john'}, function(err) {
if (err) throw err;
});
parameter
user: New user (default is the previous one). password: New password for the new user (default is the previous one). charset: New character set (default to the previous one). database: New database name (default is the previous one).6. Disconnection of processing server
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 the query value
To avoid SQL injection attacks, user-submitted data needs to be escaped. connection.escape () or pool.escape () can be used
Such as:
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) {
// ...
});
Conversions of values of different types
Numbers The same
Booleans Convert to a string 'true' / 'false'
Date Object to a string 'YYYY-mm-dd HH:ii:ss'
Buffers Is converted to 6 Base string
Strings The same
Arrays => ['a', 'b'] convert 'a', 'b'
Nested array [['a', 'b'], ['c', 'd']] convert ('a', 'b'), ('c', 'd')
Objects convert key = 'val' pairs. Nested objects are converted to strings .
undefined / null ===> NULL
NaN / Infinity The same . MySQL These values are not supported , Inserting these values causes an error unless a tool supports it .
Transformation 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 do it manually
var query = "SELECT * FROM posts WHERE title=" + mysql.escape("Hello MySQL");
console.log(query); // SELECT * FROM posts WHERE title='Hello MySQL'
8. Convert query identifiers
If you cannot trust the SQL identifier (database name, table name, 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`
Support for escaping multiple
var sorter = 'date';
var query = 'SELECT * FROM posts ORDER BY ' + mysql.escapeId('posts.' + sorter);
console.log(query); // SELECT * FROM posts ORDER BY `posts`.`date`
You can use ?? A placeholder for an 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 queries
mysql.format can be used to prepare the query. The function automatically selects the 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" });
10. Get the insert row id
Gets the inserted row id when using the auto-increment primary key, as in:
connection.query('INSERT INTO posts SET ?', {title: 'test'}, function(err, result) {
if (err) throw err;
console.log(result.insertId);
});
101. The stream processing
Sometimes you want to select a large number of rows and process them as soon as they arrive, so 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
});
102. Hybrid Query Statements (Multiple Query Statements)
var = mysql. createConnection({multipleStatements: true}); connection = mysql. createConnection({multipleStatements: true}); Enable this feature.
Mixed query instance:
// create
var poolCluster = mysql.createPoolCluster();
poolCluster.add(config); // anonymous group
poolCluster.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-robin
poolCluster.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) {});
// destroy
poolCluster.end();
You can also use streams to process mixed query results:
// create
var poolCluster = mysql.createPoolCluster();
poolCluster.add(config); // anonymous group
poolCluster.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-robin
poolCluster.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) {});
// destroy
poolCluster.end();
If one of the query statements fails, the Error object contains err.index indicating the incorrect statement, and the whole query terminates.
The stream processing of mixed query results is experimental and unstable.
103. Transaction processing
Simple transaction processing at the connection level
// create
var poolCluster = mysql.createPoolCluster();
poolCluster.add(config); // anonymous group
poolCluster.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-robin
poolCluster.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) {});
// destroy
poolCluster.end();
104. Error handling
err.code = string
err.fatal => boolean
PS: Pits encountered while using the mysql module
Before writing a small program Node News, using the MySQL database, in the local test are no problem. After running the server for a period of time, I accidentally found that when the page was opened, page 1 was in a waiting state until Nginx returned a timeout error. So on the server to check again, found that the program is still running, and can correctly record each request, and then modify the code to track debugging, the original is when querying the database, callback 1 is not executed, the program hung there.
Thought for a long time also don't understand why mysql module did not implement the callback, finally all of a sudden come to see the error log, found that there was a "No reconnection after connection lost" error is not captured, the original is lost, and issues github watched on the document, it says to the connection loss will not automatically reconnect, after will trigger error incident. I quickly added a disconnection auto-reconnect function to the program, and it has been running normally for more than 10 days.
There is a variable in MySQL named wait_timeout, which indicates the operation timeout. When a connection is inactivated for more than a certain amount of time, the connection is automatically closed. The default value is 28800 (i.e. 8 hours).
Automatic reconnect database code:
// create
var poolCluster = mysql.createPoolCluster();
poolCluster.add(config); // anonymous group
poolCluster.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-robin
poolCluster.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) {});
// destroy
poolCluster.end();
Online spread of the majority of the use of mysql module code, often ignore this problem, 1 accidentally let 1 dial 1 dial people stepped into the pit.
There are children's shoes what reply to ask use pool, then went to see the next mysql module source code: The latest version of https that can be installed in https is 2.0.0-alpha7. pool created using mysql.createPool() does not automatically handle connection closure, but the version on github has been fixed (and is not yet published to npm) and will remove the connection object from the connection pool when an error event is triggered. . (source: https: / / github com/felixge/node - mysql/blob/master/lib/Pool js # L119)
Using pool code:
// create
var poolCluster = mysql.createPoolCluster();
poolCluster.add(config); // anonymous group
poolCluster.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-robin
poolCluster.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) {});
// destroy
poolCluster.end();