前言
MySQL是一款常用的開源數據庫產品，通常也是免費數據庫的首選。查了一下NPM列表，發現Nodejs有13庫可以訪問MySQL，felixge/node-mysql似乎是最受關注項目，我也決定嘗試用一下。
要注意名字，”felixge/node-mysql”非”node-mysql”，安裝部分會介紹這個小插曲！
目錄
1. node-mysql介紹
felixge/node-mysql是一個純nodejs的用javascript實現的一個MySQL客戶端程序。felixge/node-mysql封裝了Nodejs對MySQL的基本操作，100% MIT公共許可證。
項目地址：https://github.com/felixge/node-mysql
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)
mysql> GRANT ALL ON nodejs.* to nodejs@'%' IDENTIFIED BY 'nodejs';mysql> GRANT ALL ON nodejs.* to nodejs@localhost IDENTIFIED BY 'nodejs';
重新登陸MySQL
C:/Users/Administrator>mysql -unodejs -pEnter password: ******mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || nodejs || test |+--------------------+3 rows in set (0.00 sec)
mysql> USE nodejsDatabase changed
新建一個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);
mysql> SHOW TABLES;+------------------+| Tables_in_nodejs |+------------------+| t_user |+------------------+1 row in set (0.04 sec)
3. node-mysql安裝
我的系統環境
win7 64bit
Nodejs:v0.10.5
Npm:1.2.19
MySQL:Server version: 5.6.11 MySQL Community Server (GPL)
創建工程:nodejs-node-mysql
~ D:/workspace/javascript>mkdir nodejs-node-mysql~ D:/workspace/javascript>cd nodejs-node-mysql~ D:/workspace/javascript/nodejs-node-mysql>npm install node-mysqlnode-mysql@0.2.0 node_modules/node-mysql├── better-js-class@0.1.2├── cps@0.1.7├── underscore@1.5.2└── mysql@2.0.0-alpha9 (require-all@0.0.3, bignumber.js@1.0.1)
這里有一個小插曲
安裝“node-mysql”后，打開package.json文件發現，這個項目地址是
https://github.com/redblaze/node-mysql.git
從依賴關系可以看到，它依賴于mysql庫，是對felixge/node-mysql的封裝。
node-mysql1
由于這個項目star是0，fork也是0. 所以，我也不準備花時間測試了，重新安裝felixge/node-mysql的包。
重新安裝node-mysql
~ D:/workspace/javascript/nodejs-node-mysql>rm -rf node_modules~ 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.js
var 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使用
下面我們要對node-mysql的API進行常用的測試。
表新刪改查
連接池配置
MySQL斷線重連
連接池超時測試
1). 表新刪改查
修改app.js
~ vi app.js
var 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();控制臺輸出：
D:/workspace/javascript/nodejs-node-mysql>node app.js
DELETE Return ==>{ fieldCount: 0, affectedRows: 2, insertId: 0, serverStatus: 34, warningCount: 0, message: '', protocol41: true, changedRows: 0 }INSERT Return ==>{ fieldCount: 0, affectedRows: 2, insertId: 33, serverStatus: 2, warningCount: 0, message: '&Records: 2 Duplicates: 0 Warnings: 0', protocol41: true, changedRows: 0 }SELECT ==>{ id: 33, name: 'conan', create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中國標準時間) }{ id: 34, name: 'fens.me', create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中國標準時間) }UPDATE Return ==>{ fieldCount: 0, affectedRows: 1, insertId: 0, serverStatus: 2, warningCount: 0, message: '(Rows matched: 1 Changed: 1 Warnings: 0', protocol41: true, changedRows: 1 }SELECT ==>{ id: 33, name: 'conan update', create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中國標準時間) }{ id: 34, name: 'fens.me', create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中國標準時間) }由于node的異步的，上面是一個連續的操作，代碼會被寫的支離破碎。我們可以通過async庫對上面代碼進行封裝，請參考文章：Nodejs異步流程控制Async
2). 連接池配置
增加文件：app-pooling.js
~ vi app-pooling.js
var 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.js
SELECT ==>{ 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 (中國標準時間) }3). MySQL斷線重連
分別模擬3種錯誤
a.登陸密碼錯誤
b.數據庫宕機
c.數據庫連接超時
新增文件：app-reconnect.js
~ vi app-reconnect.js
var 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();a. 模擬密碼錯誤
修改password: ‘nodejs11'
控制臺輸出。
D:/workspace/javascript/nodejs-node-mysql>node app-reconnect.js
error when connecting to db: { [Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'nodejs'@'localhost' (using passrd: YES)] code: 'ER_ACCESS_DENIED_ERROR', errno: 1045, sqlState: '28000', fatal: true }error when connecting to db: { [Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'nodejs'@'localhost' (using passrd: YES)] code: 'ER_ACCESS_DENIED_ERROR', errno: 1045, sqlState: '28000', fatal: true }b. 模擬數據庫宕機
正常啟動node，然后殺掉mysqld的進程。
控制臺輸出。
D:/workspace/javascript/nodejs-node-mysql>node app-reconnect.js
db error { [Error: read ECONNRESET] code: 'ECONNRESET', errno: 'ECONNRESET', syscall: 'read', fatal: true }Error: read ECONNRESET at errnoException (net.js:884:11) at TCP.onread (net.js:539:19)這個異常，直接導致node程序被殺死！
c. 模擬連接超時，PROTOCOL_CONNECTION_LOST
切換到root賬戶, 修改MySQL的wait_timeout參數，設置為10毫秒超時。
~ mysql -uroot -pmysql> show variables like 'wait_timeout';+---------------+-------+| Variable_name | Value |+---------------+-------+| wait_timeout | 28800 |+---------------+-------+1 row in set (0.00 sec)mysql> set global wait_timeout=10;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'wait_timeout';+---------------+-------+| Variable_name | Value |+---------------+-------+| wait_timeout | 10 |+---------------+-------+1 row in set (0.00 sec)
修改文件：app-reconnection.js，在最后增加代碼
~ vi app-reconnection.js
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);程序會每融15秒，做一次查詢。
控制臺輸出
D:/workspace/javascript/nodejs-node-mysql>node app-reconnect.jsWed Sep 11 2013 15:21:14 GMT+0800 (中國標準時間)[ { Variable_name: 'wait_timeout', Value: '10' } ]db error { [Error: Connection lost: The server closed the connection.] fatal: true, code: 'PROTOCOL_CONNECTION_LOST' }Wed Sep 11 2013 15:21:28 GMT+0800 (中國標準時間)[ { Variable_name: 'wait_timeout', Value: '10' } ]db error { [Error: Connection lost: The server closed the connection.] fatal: true, code: 'PROTOCOL_CONNECTION_LOST' }Wed Sep 11 2013 15:21:43 GMT+0800 (中國標準時間)[ { Variable_name: 'wait_timeout', Value: '10' } ]我們自己的程序捕獲了“PROTOCOL_CONNECTION_LOST”異常，并自動的實現了數據庫重連。
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的方式。
新聞熱點
疑難解答
