普通連接的增刪改查
- 查
var mysql = require('mysql'); //調(diào)用MySQL模塊
//創(chuàng)建一個(gè)connection
var connection = mysql.createConnection({
host : '127.0.0.1', //主機(jī)
user : 'root', //MySQL認(rèn)證用戶名
password:'12345',
port: '3306',
database: 'node'
});
//創(chuàng)建一個(gè)connection,每一次connect只能執(zhí)行一次query
connection.connect(function(err){
if(err){
console.log('[query] - :'+err);
return;
}
console.log('[connection connect] succeed!');
});
//執(zhí)行SQL語(yǔ)句，查
connection.query('select * from t_students where name=?',['小明'],function(err,result,fields){
if(!err){
console.log(result[0].name);
fields.forEach(function(v){//fields是一個(gè)json數(shù)組，每個(gè)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", …}
//關(guān)閉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直接拷貝的，創(chuàng)建pool時(shí)的配置
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：當(dāng)連接池沒有連接或超出最大限制時(shí)，設(shè)置為true且會(huì)把連接放入隊(duì)列，設(shè)置為false會(huì)返回error
connectionLimit：連接數(shù)限制，默認(rèn)：10
queueLimit：最大連接請(qǐng)求隊(duì)列限制，設(shè)置為0表示不限制，默認(rèn)：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'
});
//監(jiān)聽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);
}
}
});
});
//連接池可以直接使用，也可以共享一個(gè)連接或管理多個(gè)連接（引用官方示例）
//直接使用
pool.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
if (err) throw err;
console.log('The solution is: ', rows[0].solution);
});
//共享連接，連接池最常用的方法，可以進(jìn)一步封裝
pool.getConnection(function(err, connection) {
connection.query(sql, function(err, result) {
console.log(result);
//釋放連接
connection.release();
});
//Error: Connection already released,應(yīng)該每次到連接池中再次獲取
// connection.query( 'SELECT * FROM seckill;', function(err, result) {
// console.log(result);
// connection.release();
// });
});
myQuery('SELECT * FROM seckill;');
myQuery('SELECT * FROM seckill;');
//連接池的關(guān)閉，一般用不到。除非項(xiàng)目停止運(yùn)行
pool.end(function (err) {
// all connections in the pool have ended
});
以下為封裝后的js,單獨(dú)列為一個(gè)js文件然后導(dǎo)出
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;
連接池集群
//創(chuàng)建連接池集群
var poolCluster = mysql.createPoolCluster();
//添加配置 config是一個(gè)連接池配置
poolCluster.add(config);//使用自動(dòng)名稱添加配置
poolCluster.add('MASTER',masterConfig);//添加命名配置
poolCluster.add('SLAVE1',slave1config);
poolCluster.add('SLAVE2',slave2config);
//刪除配置
poolCluster.remove('SLAVE1');//根據(jù)配置名字
poolCluster.remove('SLAVE*')//根據(jù)匹配到的
//獲取連接 從所有的連接池里獲得 默認(rèn)選擇器
poolCluster.getConnectiuon(function(err,connection){});
//從 一個(gè)連接池里面獲取連接
poolCluster.getConnectiuon('MASTER',function(err,connection){});
//從匹配到的連接池組里面獲取連接 按照順序
//如果SLAVE1出錯(cuò) 就從SLAVE2獲得連接
poolCluster.getConnectiuon('SLAVE*','ORDER',function(err,connection){} );
//觸發(fā)事件 當(dāng)刪除連接池時(shí)觸發(fā)
poolCluster.on('remove',function(nodeId){
console.log(nodeId);//被刪除的連接池名字
});
//配置 選擇器 從SLAVE1 SLAVE2 里面隨機(jī)獲得連接
var pool = poolCluster.of('SLAVE*','RANDOM');
pool.getConnectiuon(function(err,connection){});
//關(guān)閉連接池集群
poolCluster.end();
簡(jiǎn)單事務(wù)
Transactions事務(wù)
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!');
});
});
});
});
