普通连接的增删改查
- 查
var mysql = require('mysql'); //调用MySQL模块
//创建一个connection
var connection = mysql.createConnection({
host : '127.0.0.1', //主机
user : 'root', //MySQL认证用户名
password:'12345',
port: '3306',
database: 'node'
});
//创建一个connection,每一次connect只能执行一次query
connection.connect(function(err){
if(err){
console.log('[query] - :'+err);
return;
}
console.log('[connection connect] succeed!');
});
//执行SQL语句，查
connection.query('select * from t_students where name=?',['小明'],function(err,result,fields){
if(!err){
console.log(result[0].name);
fields.forEach(function(v){//fields是一个json数组，每个json包含了字段信息
console.log(v);
});
}
});
//小明
//[connection end] succeed!
//FieldPacket {catalog: "def", db: "node_demo", table: "t_students", orgTable: "t_students", name: //"stu_id", …}
//FieldPacket {catalog: "def", db: "node_demo", table: "t_students", orgTable: "t_students", name: "name", //…}
//FieldPacket {catalog: "def", db: "node_demo", table: "t_students", orgTable: "t_students", name: "age", …}
//关闭connection
connection.end(function(err){
if(err){
return;
}
console.log('[connection end] succeed!');
});
- 改
改：
var userSql = "update seckill set number = number-1 where seckill_id = ?";
var param = [1000, 2];
connection.query(userSql, param, function (error, result) {
if(error)
{
console.log(error.message);
}else{
console.log('affectedRows: '+result.affectedRows);
}
});
- 增
var addVip = 'delete from seckill where seckill_id = 1005';
connection.query(addVip, function(error, result){
if(error)
{
console.log(error.message);
}else{
console.log('affectedRows: '+result.affectedRows);
}
});
连接池
Pool options //mysql的github直接拷贝的，创建pool时的配置
Pools accept all the same options as a connection. When creating a new connection, the options are simply passed to the connection constructor. In addition to those options pools accept a few extras:
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. (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)
中文释义：
waitForConnections：当连接池没有连接或超出最大限制时，设置为true且会把连接放入队列，设置为false会返回error
connectionLimit：连接数限制，默认：10
queueLimit：最大连接请求队列限制，设置为0表示不限制，默认：0
连接池事件
acquire
The pool will emit an acquire event when a connection is acquired from the pool. This is called after all acquiring activity has been performed on the connection, right before the connection is handed to the callback of the acquiring code.
pool.on('acquire', function (connection) {
console.log('Connection %d acquired', connection.threadId);
});
connection
The pool will emit a connection event when a new connection is made within the pool. If you need to set session variables on the connection before it gets used, you can listen to the connection event.
pool.on('connection', function (connection) {
connection.query('SET SESSION auto_increment_increment=1')
});
enqueue
The pool will emit an enqueue event when a callback has been queued to wait for an available connection.
pool.on('enqueue', function () {
console.log('Waiting for available connection slot');
});
release
The pool will emit a release event when a connection is released back to the pool. This is called after all release activity has been performed on the connection, so the connection will be listed as free at the time of the event.
pool.on('release', function (connection) {
console.log('Connection %d released', connection.threadId);
});
连接池示例代码
var mysql = require("mysql");
var pool = mysql.createPool({
host: '127.0.0.1',
user: 'root',
password:'12345',
port:'3306',
database:'node'
});
//监听connection事件
pool.on('connection', function(connection) {
connection.query('select * from seckill', function(error, results, fields){
if (error) {
throw error;
}
if (results) {
for(var i = 0; i < results.length; i++)
{
console.log('%s\t%s',results[i].name,results[i].end_time);
}
}
});
});
//连接池可以直接使用，也可以共享一个连接或管理多个连接（引用官方示例）
//直接使用
pool.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
if (err) throw err;
console.log('The solution is: ', rows[0].solution);
});
//共享连接，连接池最常用的方法，可以进一步封装
pool.getConnection(function(err, connection) {
connection.query(sql, function(err, result) {
console.log(result);
//释放连接
connection.release();
});
//Error: Connection already released,应该每次到连接池中再次获取
// connection.query( 'SELECT * FROM seckill;', function(err, result) {
// console.log(result);
// connection.release();
// });
});
myQuery('SELECT * FROM seckill;');
myQuery('SELECT * FROM seckill;');
//连接池的关闭，一般用不到。除非项目停止运行
pool.end(function (err) {
// all connections in the pool have ended
});
以下为封装后的js,单独列为一个js文件然后导出
var db = {};
var mysql = require('mysql');
var pool = mysql.createPool({
connectionLimit : 10,
host : 'localhost',
user : 'root',
password : '123456',
database : 'nodejs'
});
//获取连接
db.getConnection = function(callback){
pool.getConnection(function(err, connection) {
if (err) {
callback(null);
return;
}
callback(connection);
});
}
module.exports = db;
连接池集群
//创建连接池集群
var poolCluster = mysql.createPoolCluster();
//添加配置 config是一个连接池配置
poolCluster.add(config);//使用自动名称添加配置
poolCluster.add('MASTER',masterConfig);//添加命名配置
poolCluster.add('SLAVE1',slave1config);
poolCluster.add('SLAVE2',slave2config);
//删除配置
poolCluster.remove('SLAVE1');//根据配置名字
poolCluster.remove('SLAVE*')//根据匹配到的
//获取连接 从所有的连接池里获得 默认选择器
poolCluster.getConnectiuon(function(err,connection){});
//从 一个连接池里面获取连接
poolCluster.getConnectiuon('MASTER',function(err,connection){});
//从匹配到的连接池组里面获取连接 按照顺序
//如果SLAVE1出错 就从SLAVE2获得连接
poolCluster.getConnectiuon('SLAVE*','ORDER',function(err,connection){} );
//触发事件 当删除连接池时触发
poolCluster.on('remove',function(nodeId){
console.log(nodeId);//被删除的连接池名字
});
//配置 选择器 从SLAVE1 SLAVE2 里面随机获得连接
var pool = poolCluster.of('SLAVE*','RANDOM');
pool.getConnectiuon(function(err,connection){});
//关闭连接池集群
poolCluster.end();
简单事务
Transactions事务
Simple transaction support is available at the connection level:
connection.beginTransaction(function(err) {
if (err) { throw err; }
connection.query('INSERT INTO posts SET title=?', title, function (error, results, fields) {
if (error) {
return connection.rollback(function() {
throw error;
});
}
var log = 'Post ' + results.insertId + ' added';
connection.query('INSERT INTO log SET data=?', log, function (error, results, fields) {
if (error) {
return connection.rollback(function() {
throw error;
});
}
connection.commit(function(err) {
if (err) {
return connection.rollback(function() {
throw err;
});
}
console.log('success!');
});
});
});
});
