20190521- MySQL连接池

[onvno]

单次连接断开开销太大，可以使用连接池

单次连接基本使用

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});

connection.connect();

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

connection.end();

连接池基本使用

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});

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

Using pool.getConnection() is useful to share connection state for subsequent queries. This is because two calls to pool.query() may use two different connections and run in parallel. This is the basic structure:

var mysql = require('mysql');
var pool  = mysql.createPool(...);

pool.getConnection(function(err, connection) { if (err) throw err; // not connected!

// Use the connection connection.query('SELECT something FROM sometable', function (error, results, fields) { // When done with the connection, release it. connection.release();

// Handle error after the release.
if (error) throw error;

// Don't use the connection here, it has been returned to the pool.

}); });

#### 连接池参数
* acquireTimeout: The milliseconds before a timeout occurs during the connection acquisition. This is slightly different from connectTimeout, because acquiring a pool connection does not always involve making a connection. If a connection request is queued, the time the request spends in the queue does not count towards this timeout. (Default: 10000)
* waitForConnections: Determines the pool's action when no connections are available and the limit has been reached. If true, the pool will queue the connection request and call it when one becomes available. If false, the pool will immediately call back with an error. (Default: true)
* **connectionLimit**: The maximum number of connections to create at once. (Default: 10)
* queueLimit: The maximum number of connection requests the pool will queue before returning an error from getConnection. If set to 0, there is no limit to the number of queued connection requests. (Default: 0)
其他参数同`connection`

以上来自官方文档：[mysql](https://www.npmjs.com/package/mysql#connection-options)

[onvno]

MySql数据库连接池专题：介绍连接池使用场景及优点： 连接池的主要优点有以下三个方面。

第一、减少连接创建时间。连接池中的连接是已准备好的、可重复使用的，获取后可以直接访问数据库，因此减少了连接创建的次数和时间。

第二、简化的编程模式。当使用连接池时，每一个单独的线程能够像创建一个自己的JDBC连接一样操作，允许用户直接使用JDBC编程技术。

第三、控制资源的使用。如果不使用连接池，每次访问数据库都需要创建一个连接，这样系统的稳定性受系统连接需求影响很大，很容易产生资源浪费和高负载异常。连接池能够使性能最大化，将资源利用控制在一定的水平之下。连接池能控制池中的连接数量，增强了系统在大量用户应用时的稳定性。

[onvno]

node.js中mysql连接池的使用:一些使用问题及建议

[onvno]

在写 SQL 语句的时间尽量不要使用 SQL 拼装，因为很容易被 SQL注入，从而引发安全问题，如果数据和 SQL 语句需要分离，那么请使用 占位符 的方式。 https://www.jmjc.tech/less/113

connection.query("select * from users where id = ? and name = ?", [1, 'jmjc'], (err, result)=>{}) // 这种方式 mysql 模块内部会调用 escape 方法，过滤掉一些非法的操作

/*
  当前我们也可以自己使用 escape 方法
*/
connection.query('select * from users where id = ' + connection.escape(userId), (err, result) => {})

/*
 或者 format 方法
*/
const sql = "select * from ?? where ?? = ?"
const inserts = ['users', 'id', 1]
sql = mysql.format(sql, inserts) // select * from users where id = 1