Node.js 操作MySQL

var mysql = require("mysql");

var cnn = mysql.createConnection({
    host:'127.0.0.1',
	port:3306,
	database:'test',
	user:'root',
	password:''
});

//连接池
var pool = mysql.createPool({
    host:'127.0.0.1',
	port:3306,
	database:'test',
	user:'root',
	password:''
});
var pcnn = pool.getConnection((err, pcnn) => {
	console.log("使用连接池");
	var query = pcnn.query("SELECT * FROM datatables_demo");
	query.on('error', (err) => {
		console.log("数据读取错误");

		// 释放连接回到池
		pcnn.release();
	}).on('fields', (fields) => {
		//console.log(fields);
		fields.forEach((field) => {
			console.log(field.name);
		});
	}).on('result', (row) => {
		pcnn.pause();
		console.log(row.first_name+" "+row.last_name+" "+row.age);
		pcnn.resume();
	}).on('end', () => {
		console.log("读取完毕");
		// 释放连接回到池
		pcnn.release();
	});	
	console.log("使用连接池结束");
});

var pcnn = pool.getConnection((err, pcnn) => {
	console.log("使用连接池");
	var query = pcnn.query("SELECT * FROM datatables_demo");
	query.on('error', (err) => {
		console.log("数据读取错误");

		// 释放连接回到池
		pcnn.release();
	}).on('fields', (fields) => {
		//console.log(fields);
		fields.forEach((field) => {
			console.log(field.name);
		});
	}).on('result', (row) => {
		pcnn.pause();
		console.log(row.first_name+" "+row.last_name+" "+row.age);
		pcnn.resume();
	}).on('end', () => {
		console.log("读取完毕");
		// 释放连接回到池
		pcnn.release();
	});	
	console.log("使用连接池结束");
});

// 关闭整个池
//pool.end((err) => {
//	console.log("关闭连接池");
//});

///////////////////////////////////////////////////////////
cnn.connect(function(err){
	if(err) {
		console.log("数据库连接错误");
	} else {
		console.log("数据库连接成功");
	}	
});

// 连接丢失重连
cnn.on('error', function(err){
	if(err.code === 'PROTOCOL_CONNECTION_LOST') {
		console.log('连接丢失');
		setTimeout(function(){
			cnn.connetct((err)=>{
				console.log("数据库重连");
			});
		}, 10000);
	}
});

//cnn.query("INSERT INTO posts SET ?", {id:1, title:'Hello MySQL'});
//cnn.query("UPDATE posts SET title = :title", {title:'Hello MySQL'});
//cnn.query("SELECT * FROM users WHERE ID = ?", [userId]);

// 以流的方法读取数据,以行为单位
// 正确姿势
var query = cnn.query("SELECT * FROM datatables_demo");
query.on('error', (err) => {
	console.log("数据读取错误");
}).on('fields', (fields) => {
	//console.log(fields);
	fields.forEach((field) => {
		console.log(field.name);
	});
}).on('result', (row) => {
	cnn.pause();
	console.log(row.first_name+" "+row.last_name+" "+row.age);
	cnn.resume();
}).on('end', () => {
	console.log("读取完毕");
});

// 一次读整个结果,然后对结果循环
cnn.query("SELECT * FROM datatables_demo LIMIT 20", function(err, result){
	//console.log(result);
	for(row in result) {
		//console.log(result[row]);
		console.log(result[row].first_name+" "+result[row].last_name+" "+result[row].age);
	}
	console.log("------------------------------------------");
	result.forEach((row) => {
		 console.log(row.first_name+" "+row.last_name+" "+row.age);
	});
});


cnn.end(function(err){
	console.log("数据库关闭。");
});