Of Stack

Explanation of node Operation mysql Database Instance

  • 2021-08-05 08:18:49
  • OfStack

In this paper, an example is given to describe the method of node operating mysql database. Share it for your reference, as follows:

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

This method accepts an object as a parameter, which has four commonly used attributes host, user, password and database. Same parameters as the linked database in php. The list of attributes is as follows:

host 连接数据库所在的主机名. (默认: localhost)
port 连接端口. (默认: 3306)
localAddress 用于TCP连接的IP地址. (可选)
socketPath 链接到unix域的路径。在使用host和port时该参数会被忽略.
user MySQL用户的用户名.
password MySQL用户的密码.
database 链接到的数据库名称 (可选).
charset 连接的字符集. (默认: 'UTF8_GENERAL_CI'.设置该值要使用大写!)
timezone 储存本地时间的时区. (默认: 'local')
stringifyObjects 是否序列化对象. See issue #501. (默认: 'false')
insecureAuth 是否允许旧的身份验证方法连接到数据库实例. (默认: false)
typeCast 确定是否讲column值转换为本地JavaScript类型列值. (默认: true)
queryFormat 自定义的查询语句格式化函数.
supportBigNumbers 数据库处理大数字(长整型和含小数),时应该启用 (默认: false).
bigNumberStrings 启用 supportBigNumbers和bigNumberStrings 并强制这些数字以字符串的方式返回(默认: false).
dateStrings 强制日期类型(TIMESTAMP, DATETIME, DATE)以字符串返回，而不是1javascript Date对象返回. (默认: false)
debug 是否开启调试. (默认: false)
multipleStatements 是否允许在1个query中传递多个查询语句. (Default: false)
flags 链接标志.

You can also use strings to connect to the database, for example: 


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

2. End the database connection end() And destroy()

end () accepts a callback function and will not trigger until query ends. If query fails, the link will still be terminated, and the error will be passed to the callback function for processing.

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

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

You can listen for connection events and set session values 


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

connection. release () releases links to the connection pool. If you need to close the connection and delete it, you need to use connection. destroy ()

pool accepts several extended parameters in addition to the same parameters as connection

createConnection 用于创建链接的函数. (Default: mysql.createConnection)
waitForConnections 决定当没有连接池或者链接数打到最大值时pool的行为. 为true时链接会被放入队列中在可用是调用，为false时会立即返回error. (Default: true)
connectionLimit 最大连接数. (Default: 10)
queueLimit 连接池中连接请求的烈的最大长度，超过这个长度就会报错，值为0时没有限制. (Default: 0)

4. Connection pool cluster

Allow 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 Parameters for Linked Clusters

canRetry 值为true时，允许连接失败时重试(Default: true)
removeNodeErrorCount 当连接失败时 errorCount 值会增加. 当errorCount 值大于 removeNodeErrorCount 将会从PoolCluster中删除1个节点. (Default: 5)
defaultSelector 默认选择器. (Default: RR)
RR 循环. (Round-Robin)
RANDOM 通过随机函数选择节点.
ORDER 无条件地选择第1个可用节点.

5. Switch users/change connection status

Mysql allows users to be switched more than if they are disconnected 


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

Parameter

user 新的用户 (默认为早前的1个).
password 新用户的新密码 (默认为早前的1个).
charset 新字符集 (默认为早前的1个).
database 新数据库名称 (默认为早前的1个).

6. Disconnect the 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

In order to avoid SQL injection attack, it is necessary to escape the data submitted by the user. You can use the connection.escape() Or pool.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 it? As a placeholder 


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

Conversion results of different types of values

Numbers unchanged
Booleans to string 'true'/'false'
Date object to string 'YYYY-mm-dd HH: ii: ss'
Buffers is converted to a hexadic string
Strings unchanged
Arrays = > ['a', 'b'] Convert to 'a', 'b'
Nested arrays [['a', 'b'], ['c', 'd']] are converted to ('a', 'b'), ('c', 'd')
Objects to key = 'val' pairs. Nested objects are converted to strings.
undefined / null === > NULL
NaN/Infinity does not change. MySQL does not support these values and inserting them will cause an error unless supported by a tool.

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 manual conversion 


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

8. Transform the query identifier

If you cannot trust the SQL identifier (database name, table name, column name), you can use the transformation 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 


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

Can be used? ? As a placeholder for identifiers 


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

9. Prepare for inquiry

You can use mysql. format to prepare the query statement, which automatically selects the appropriate method to escape the parameters. 


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

10. Custom formatting function 


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

Get the inserted row id when using the self-increasing primary key, such as: 


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

12. Stream processing

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


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

13. Mixed query statement (multi-statement query)

Because mixed queries are vulnerable to SQL injection attacks, which are not allowed by default, you can use: 


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

Turn this function on.

Mixed query instance: 


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

You can also use streams to process 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 goes wrong, the Error object contains err. index indicates the error statement's id, and the entire query terminates.

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

14. Transaction processing

connection-level simple transactions 


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

15. Error handling 


err.code = string
err.fatal => boolean

For more detailed instructions, please see: https://github.com/felixge/node-mysql

I hope this article is helpful to everyone's nodejs programming.

Related articles: