node-mysql
# 1.安装mysql模块
```javascript
npm install mysql
```
![image](https://cloud.githubusercontent.com/assets/17243165/21185737/d395aa4a-c24c-11e6-9a65-a587dffaa855.png)

# 2.新建sql.js，并写上以下代码
```javascript
var mysql = require('mysql');
var connection = mysql.createConnection({
	host: 'localhost',
	user: 'wscats',
	password: '123456789',
	database: 'asm'
});
connection.connect();//连接数据库
connection.query('select * from news', function(err, rows, fields) {
	if(err) throw err;
	console.log('The news is: ', rows[0]);
});//执行SQL语句
connection.end();//关闭连接。
```
连接基本参数

|参数|用法|
|-|-|
|host|主机名，localhost代表本地|
|user|Mysql用户|
|password|密码|
|database|连接的数据库|

由于我本地数据库名叫asm，操作表为news，取第一列数据
![image](https://cloud.githubusercontent.com/assets/17243165/21185940/9fe6de16-c24d-11e6-9050-821770d67bd7.png)
# 3.执行文件
执行sql.js，显示结果如图
```javascript
node sql
```
![image](https://cloud.githubusercontent.com/assets/17243165/21186006/ead6a8ca-c24d-11e6-8900-11b9b1efb14f.png)

注意如果我们要在每一次查询数据库后`connection.end()`关闭一次连接，那我们需要用`mysql.createConnection()`创建一个新的`connection`，也就是每一次的开关都是用唯一一个`connection`来实现
```javascript
var connection;
function createConnection() {
	connection = mysql.createConnection({
		host: 'localhost',
		user: 'laoxie',
		password: '12345678',
		database: 'asm'
	});
}
```

# 4.增删查改分页
注意sql语句不要写错语法
```javascript
//增加记录
connection.query('insert into news (title ,text) values ("wscats" , "eno")');
```
```javascript
//删除记录
connection.query('delete from news where title = "wscats"');
```
```javascript
// 修改记录
connection.query('update news set text = "eno" where title = "wscats"');
```
```javascript
//查找记录
connection.query('select * from news', function(err, rows, fields) {
	if(err) throw err;
	console.log('The news is: ', rows[0]);
});
```
```javascript
//查询记录
var arr = [];
connection.query("select * from news", function selectTable(err, rows, fields) {
	if(err) {
		throw err;
	}
	if(rows) {
		for(var i = 0; i < rows.length; i++) {
			console.log("第" + i + "条", "id: " + rows[i].id, "title: " + rows[i].title, "text: " + rows[i].text);
			//把数据组装成数组对象
			var obj = {};
			obj.id = rows[i].id;
			obj.title = rows[i].title;
			obj.text = rows[i].text;
			arr.push(obj);
		}
	}
	console.log(arr);
});
```
```javascript
//查询记录
connection.query('select * from news where id = 2', function(err, rows, fields) {
	if(err) throw err;
	console.log('The news is: ', rows[0]);
});
```
```js
//分页
//取前5条数据
select * from table limit 0,5
//or
select * from table limit 5
//取第11条到第15条数据，共5条
select * from table limit 10,5
//格式
select * from table limit offset,rows
offset指定要返回的第一行的偏移量，rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)
```

# 5.封装成模块
最后我们可以把它封装成一个模块导出，在其他主模块中调用
注意我在每个原型链的函数结尾处都会调用一个`connection.end()`方法，这个方法`connection.connect()`对应，一个开始，一个结束

配合await和async的封装具体可以参考[这里](https://github.com/Wscats/node-tutorial/blob/master/nodeCms/be/libs/mysql.js)
```javascript
function curd() {
	var mysql = require('mysql');
	this.connection = mysql.createConnection({
		host: 'localhost',
		user: 'wscats',
		password: '123456789',
		database: 'asm'
	});
	//开始链接数据库
	this.connection.connect();
}
curd.prototype.insert = function() {
	//增加记录
	this.connection.query('insert into news (title ,text) values ("wscats" , "eno")');
	//结束链接
	this.connection.end();
}
curd.prototype.update = function() {
	// 修改记录
	this.connection.query('update news set text = "eno" where title = "wscats"');
}
curd.prototype.where = function() {
	this.connection.query('select * from news where id = 2', function(err, rows, fields) {
		if(err) throw err;
		console.log('The news is: ', rows[0]);
	});
	//结束链接
	this.connection.end();
}
curd.prototype.delete = function() {
	//删除记录
	this.connection.query('delete from news where title = "wscats"');
}
curd.prototype.find = function() {
	//查询记录
	var arr = [];
	this.connection.query("select * from news", function(err, rows, fields) {
		if(err) {
			throw err;
		}
		if(rows) {
			for(var i = 0; i < rows.length; i++) {
				console.log("第" + i + "条", "id: " + rows[i].id, "title: " + rows[i].title, "text: " + rows[i].text);
				//把数据组装成数组对象
				var obj = {};
				obj.id = rows[i].id;
				obj.title = rows[i].title;
				obj.text = rows[i].text;
				arr.push(obj);
			}
		}
		console.log(arr);
	});
	this.connection.end();
}
curd.prototype.find2 = function(
	//查找记录
	this.connection.query('select * from news', function(err, rows, fields) {
		if(err) throw err;
		console.log('The news is: ', rows[0]);
	});
)
var db = new curd();
exports.db = db;
//执行
//db.insert()
//外部引用方法
//var db = require('./sql');
//db.db.where();
```

# 6.断线重连

因为mysql连接时间长的话会自动断掉，可以封装一个断线重连的接口
```js
const mysql = require("mysql");
function createConnection() {
	let connection = mysql.createConnection({
		// 域名
		host: 'localhost',
		// 用户名
		user: 'wscats',
		// 密码
		password: '12345678',
		// 数据库
		database: 'corrine'
	});
	//连接错误，2秒重试
	connection.connect((err) => {
		if(err) {
			console.log('error when connecting to db:', err);
			setTimeout(createConnection, 2000);
		}
	});
	connection.on('error', function(err) {
		console.log('db error', err);
		// 如果是连接断开，自动重新连接
		if(err.code === 'PROTOCOL_CONNECTION_LOST') {
			createConnection();
		} else {
			throw err;
		}
	});
	return connection
}
module.exports = createConnection();
```

# 7.自动断线

建议用下面这一段来实现mysql的自动连接和自动断开，那就不会出现too many connections的错误提醒了

```js
var query = function(sql, params, callback) {
	var connection = mysql.createConnection({
		// 域名
		host: 'localhost',
		// 用户名
		user: 'wscats',
		// 密码
		password: '12345678',
		// 数据库
		database: 'corrine'
	});
	//连接错误，2秒重试  
	connection.connect(function(err) {
		if(err) {
			console.log("error when connecting to db:", err);
			setTimeout(query, 2000);
		} else {
			var q = connection.query(sql, params, function(error, results, fields) {
				//关闭连接  
				connection.end();
				//事件驱动回调  
				callback(error, results, fields);
			});
			console.log("sql:::" + q.sql);
		}
	});
	connection.on("error", function(err) {
		console.log("db error", err);
		// 如果是连接断开，自动重新连接  
		if(err.code === "PROTOCOL_CONNECTION_LOST") {
			query();
		} else {
			throw err;
		}
	});
}
```

# 参考文档

- 关于连接数过多的问题(too many connection)[记一次 MySQL 数据库问题排查](http://blog.csdn.net/zhuying_linux/article/details/7810752)
- [使用node连接mySQL，封装自定义模块供外部调用](https://blog.csdn.net/crazy_jialin/article/details/78042855)