#0 系列目录#
- 深入浅出Node.js系列
#1 node-mysql介绍# felixge/node-mysql是一个纯nodejs的用javascript实现的一个MySQL客户端程序。felixge/node-mysql封装了Nodejs对MySQL的基本操作，100% MIT公共许可证。
项目地址：
#2 建立MySQL测试库# 本地创建MySQL测试库：nodejs
~ mysql -uroot -pmysql> CREATE DATABASE nodejs;mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || mysql || nodejs || performance_schema |+--------------------+4 rows in set (0.00 sec)
新建一个user表：
CREATE TABLE t_user( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(16) NOT NULL , create_date TIMESTAMP NULL DEFAULT now())ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE UNIQUE INDEX t_quiz_IDX_0 on t_user(name);
#3 node-mysql安装#
~ D:\workspace\javascript\nodejs-node-mysql>npm install mysql@2.0.0-alpha9npm http GET https://registry.npmjs.org/mysql/2.0.0-alpha9npm http 200 https://registry.npmjs.org/mysql/2.0.0-alpha9npm http GET https://registry.npmjs.org/mysql/-/mysql-2.0.0-alpha9.tgznpm http 200 https://registry.npmjs.org/mysql/-/mysql-2.0.0-alpha9.tgznpm http GET https://registry.npmjs.org/require-all/0.0.3npm http GET https://registry.npmjs.org/bignumber.js/1.0.1npm http 304 https://registry.npmjs.org/require-all/0.0.3npm http 304 https://registry.npmjs.org/bignumber.js/1.0.1mysql@2.0.0-alpha9 node_modules\mysql├── require-all@0.0.3└── bignumber.js@1.0.1
创建node程序启动文件：app.js
~ vi app.jsvar mysql = require('mysql');var conn = mysql.createConnection({ host: 'localhost', user: 'nodejs', password: 'nodejs', database:'nodejs', port: 3306});conn.connect();conn.query('SELECT 1 + 1 AS solution', function(err, rows, fields) { if (err) throw err; console.log('The solution is: ', rows[0].solution);});conn.end(); 运行node：
~ D:\workspace\javascript\nodejs-node-mysql>node app.jsThe solution is: 2
这样我们就让Nodejs连接上了MySQL。
#4 node-mysql使用# ##4.1 表增删改查## 修改app.js
~ vi app.jsvar mysql = require('mysql');var conn = mysql.createConnection({ host: 'localhost', user: 'nodejs', password: 'nodejs', database: 'nodejs', port: 3306});conn.connect();var insertSQL = 'insert into t_user(name) values("conan"),("fens.me")';var selectSQL = 'select * from t_user limit 10';var deleteSQL = 'delete from t_user';var updateSQL = 'update t_user set name="conan update" where name="conan"';//deleteconn.query(deleteSQL, function (err0, res0) { if (err0) console.log(err0); console.log("DELETE Return ==> "); console.log(res0); //insert conn.query(insertSQL, function (err1, res1) { if (err1) console.log(err1); console.log("INSERT Return ==> "); console.log(res1); //query conn.query(selectSQL, function (err2, rows) { if (err2) console.log(err2); console.log("SELECT ==> "); for (var i in rows) { console.log(rows[i]); } //update conn.query(updateSQL, function (err3, res3) { if (err3) console.log(err3); console.log("UPDATE Return ==> "); console.log(res3); //query conn.query(selectSQL, function (err4, rows2) { if (err4) console.log(err4); console.log("SELECT ==> "); for (var i in rows2) { console.log(rows2[i]); } }); }); }); });});//conn.end(); 由于node的异步的，上面是一个连续的操作，代码会被写的支离破碎。我们可以通过async库对上面代码进行封装，请参考文章：
##4.2 连接池配置## 增加文件：app-pooling.js
~ vi app-pooling.jsvar mysql = require('mysql');var pool = mysql.createPool({ host: 'localhost', user: 'nodejs', password: 'nodejs', database: 'nodejs', port: 3306});var selectSQL = 'select * from t_user limit 10';pool.getConnection(function (err, conn) { if (err) console.log("POOL ==> " + err); conn.query(selectSQL,function(err,rows){ if (err) console.log(err); console.log("SELECT ==> "); for (var i in rows) { console.log(rows[i]); } conn.release(); });}); 控制台输出：
D:\workspace\javascript\nodejs-node-mysql>node app-pooling.jsSELECT ==>{ id: 39, name: 'conan update', create_date: Wed Sep 11 2013 13:41:18 GMT+0800 (中国标准时间) }{ id: 40, name: 'fens.me', create_date: Wed Sep 11 2013 13:41:18 GMT+0800 (中国标准时间) } ##4.3 MySQL断线重连##
~ vi app-reconnect.jsvar mysql = require('mysql');var conn;function handleError () { conn = mysql.createConnection({ host: 'localhost', user: 'nodejs', password: 'nodejs', database: 'nodejs', port: 3306 }); //连接错误，2秒重试 conn.connect(function (err) { if (err) { console.log('error when connecting to db:', err); setTimeout(handleError , 2000); } }); conn.on('error', function (err) { console.log('db error', err); // 如果是连接断开，自动重新连接 if (err.code === 'PROTOCOL_CONNECTION_LOST') { handleError(); } else { throw err; } });}handleError();function query(){ console.log(new Date()); var sql = "show variables like 'wait_timeout'"; conn.query(sql, function (err, res) { console.log(res); });}query();setInterval(query, 15*1000); ##4.4 MySQL连接池的超时测试## 针对wait_timeout问题，我们再对连接做一下测试。修改app-pooling.js文件：
var mysql = require('mysql');var pool = mysql.createPool({ host: 'localhost', user: 'nodejs', password: 'nodejs', database: 'nodejs', port: 3306});var selectSQL ="show variables like 'wait_timeout'";pool.getConnection(function (err, conn) { if (err) console.log("POOL ==> " + err); function query(){ conn.query(selectSQL, function (err, res) { console.log(new Date()); console.log(res); conn.release(); }); } query(); setInterval(query, 5000);}); 控制台输出：
D:\workspace\javascript\nodejs-node-mysql>node app-pooling.jsWed Sep 11 2013 15:32:25 GMT+0800 (中国标准时间)[ { Variable_name: 'wait_timeout', Value: '10' } ]Wed Sep 11 2013 15:32:30 GMT+0800 (中国标准时间)[ { Variable_name: 'wait_timeout', Value: '10' } ]Wed Sep 11 2013 15:32:35 GMT+0800 (中国标准时间)[ { Variable_name: 'wait_timeout', Value: '10' } ] 连接池，已经解决了自动重连的问题了，后面我们的开发，可以尽量使用pooling的方式。