创建连接池
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);
});
使用连接池查询
使用连接池查询的步骤:
- 获取连接
- 使用连接进行查询
- 释放连接
// 1. 获取连接
pool.getConnection(function(err, connection) {
if (err) throw err; // not connected!
// 2. 使用连接执行查询
connection.query('SELECT something FROM sometable', function (error, results, fields) {
// When done with the connection, release it.
connection.release(); // 3. 释放连接
// Handle error after the release.
if (error) throw error;
// Don't use the connection here, it has been returned to the pool.
});
});
可以简写为:
pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results[0].solution);
});
连接池配置
- waitForConnections:
如果没有连接可用时,是否等待连接释放。若设置成true请求会被放入一个队列,直到有连接可用;若设置成false,则无连接可用时直接报错。(Default:
true) - acquireTimeout:
连接超时时间(毫秒),和connectTimeout不同,connectTimeout表示跟数据库建立连接的超时时间,acquireTimeout还包括在队列中的等待时间。
(Default: 10000) - connectionLimit: 一次创建的最大连接数量. (Default: 10)
- queueLimit: 等待队列大小,0表示不限制
监听事件
- 从连接池中获取到连接时
pool.on('acquire', function (connection) {
console.log('Connection %d acquired', connection.threadId);
});
- 有新连接创建时
pool.on('connection', function (connection) {
connection.query('SET SESSION auto_increment_increment=1')
});
- 当获取连接的请求进度队列时
pool.on('enqueue', function () {
console.log('Waiting for available connection slot');
});
- 连接释放时
pool.on('release', function (connection) {
console.log('Connection %d released', connection.threadId);
});
关闭连接池
pool.end(function (err) {
// all connections in the pool have ended
});