通過node-mysql搭建Windows+Node.js+MySQL環(huán)境的教程

更新時(shí)間：2016年03月01日 17:04:08 作者：張丹

這篇文章主要介紹了通過node-mysql搭建Windows+Node.js+MySQL環(huán)境的教程,node-mysql是JavaScript編寫的一個(gè)Node的MySQL驅(qū)動(dòng),需要的朋友可以參考下

前言

MySQL是一款常用的開源數(shù)據(jù)庫產(chǎn)品，通常也是免費(fèi)數(shù)據(jù)庫的首選。查了一下NPM列表，發(fā)現(xiàn)Nodejs有13庫可以訪問MySQL，felixge/node-mysql似乎是最受關(guān)注項(xiàng)目，我也決定嘗試用一下。

要注意名字，”felixge/node-mysql”非”node-mysql”，安裝部分會(huì)介紹這個(gè)小插曲！

node-mysql介紹
建立MySQL測(cè)試庫
node-mysql安裝
node-mysql使用

1. node-mysql介紹

felixge/node-mysql是一個(gè)純nodejs的用javascript實(shí)現(xiàn)的一個(gè)MySQL客戶端程序。felixge/node-mysql封裝了Nodejs對(duì)MySQL的基本操作，100% MIT公共許可證。

項(xiàng)目地址：https://github.com/felixge/node-mysql

2. 建立MySQL測(cè)試庫

本地創(chuàng)建MySQL測(cè)試庫：nodejs

~ mysql -uroot -p
mysql> 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 -p
Enter password: ******

mysql> SHOW DATABASES;
+--------------------+
| Database      |
+--------------------+
| information_schema |
| nodejs       |
| test        |
+--------------------+
3 rows in set (0.00 sec)

mysql> USE nodejs
Database changed

新建一個(gè)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安裝

我的系統(tǒng)環(huán)境

win7 64bit
Nodejs:v0.10.5
Npm:1.2.19
MySQL:Server version: 5.6.11 MySQL Community Server (GPL)
創(chuàng)建工程: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-mysql
node-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)

這里有一個(gè)小插曲

安裝“node-mysql”后，打開package.json文件發(fā)現(xiàn)，這個(gè)項(xiàng)目地址是

https://github.com/redblaze/node-mysql.git
從依賴關(guān)系可以看到，它依賴于mysql庫，是對(duì)felixge/node-mysql的封裝。

node-mysql1

由于這個(gè)項(xiàng)目star是0，fork也是0. 所以，我也不準(zhǔn)備花時(shí)間測(cè)試了，重新安裝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-alpha9
npm http GET https://registry.npmjs.org/mysql/2.0.0-alpha9
npm http 200 https://registry.npmjs.org/mysql/2.0.0-alpha9
npm http GET https://registry.npmjs.org/mysql/-/mysql-2.0.0-alpha9.tgz
npm http 200 https://registry.npmjs.org/mysql/-/mysql-2.0.0-alpha9.tgz
npm http GET https://registry.npmjs.org/require-all/0.0.3
npm http GET https://registry.npmjs.org/bignumber.js/1.0.1
npm http 304 https://registry.npmjs.org/require-all/0.0.3
npm http 304 https://registry.npmjs.org/bignumber.js/1.0.1
mysql@2.0.0-alpha9 node_modules\mysql
├── require-all@0.0.3
└── bignumber.js@1.0.1

這回就對(duì)了，繼續(xù)下面的開發(fā)！

創(chuàng)建node程序啟動(dòng)文件：app.js

第一個(gè)測(cè)試

~ 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();

運(yùn)行node

~ D:\workspace\javascript\nodejs-node-mysql>node app.js
The solution is: 2

這樣我們就讓Nodejs連接上了MySQL。

4. node-mysql使用

下面我們要對(duì)node-mysql的API進(jìn)行常用的測(cè)試。

表新刪改查
連接池配置
MySQL斷線重連
連接池超時(shí)測(cè)試
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"';

//delete
conn.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();

控制臺(tái)輸出：

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 (中國標(biāo)準(zhǔn)時(shí)間) }
{ id: 34,
 name: 'fens.me',
 create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中國標(biāo)準(zhǔn)時(shí)間) }
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 (中國標(biāo)準(zhǔn)時(shí)間) }
{ id: 34,
 name: 'fens.me',
 create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中國標(biāo)準(zhǔn)時(shí)間) }

由于node的異步的，上面是一個(gè)連續(xù)的操作，代碼會(huì)被寫的支離破碎。我們可以通過async庫對(duì)上面代碼進(jìn)行封裝，請(qǐng)參考文章：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();
  });
});

控制臺(tái)輸出：

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 (中國標(biāo)準(zhǔn)時(shí)間) }
{ id: 40,
 name: 'fens.me',
 create_date: Wed Sep 11 2013 13:41:18 GMT+0800 (中國標(biāo)準(zhǔn)時(shí)間) }

3). MySQL斷線重連

分別模擬3種錯(cuò)誤

a.登陸密碼錯(cuò)誤
b.數(shù)據(jù)庫宕機(jī)
c.數(shù)據(jù)庫連接超時(shí)
新增文件：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
  });

  //連接錯(cuò)誤，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);
    // 如果是連接斷開，自動(dòng)重新連接
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      handleError();
    } else {
      throw err;
    }
  });
}
handleError();

a. 模擬密碼錯(cuò)誤

修改password: ‘nodejs11'

控制臺(tái)輸出。

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 pass
rd: 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 pass
rd: YES)]
 code: 'ER_ACCESS_DENIED_ERROR',
 errno: 1045,
 sqlState: '28000',
 fatal: true }

b. 模擬數(shù)據(jù)庫宕機(jī)
正常啟動(dòng)node，然后殺掉mysqld的進(jìn)程。

控制臺(tái)輸出。

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)

這個(gè)異常，直接導(dǎo)致node程序被殺死！

c. 模擬連接超時(shí)，PROTOCOL_CONNECTION_LOST
切換到root賬戶, 修改MySQL的wait_timeout參數(shù)，設(shè)置為10毫秒超時(shí)。

~ mysql -uroot -p
mysql> 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);

程序會(huì)每融15秒，做一次查詢。

控制臺(tái)輸出

D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.js
Wed Sep 11 2013 15:21:14 GMT+0800 (中國標(biāo)準(zhǔn)時(shí)間)
[ { 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 (中國標(biāo)準(zhǔn)時(shí)間)
[ { 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 (中國標(biāo)準(zhǔn)時(shí)間)
[ { Variable_name: 'wait_timeout', Value: '10' } ]

我們自己的程序捕獲了“PROTOCOL_CONNECTION_LOST”異常，并自動(dòng)的實(shí)現(xiàn)了數(shù)據(jù)庫重連。

4). MySQL連接池的超時(shí)測(cè)試

針對(duì)wait_timeout問題，我們?cè)賹?duì)連接做一下測(cè)試。

修改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);
});

控制臺(tái)輸出：

D:\workspace\javascript\nodejs-node-mysql>node app-pooling.js
Wed Sep 11 2013 15:32:25 GMT+0800 (中國標(biāo)準(zhǔn)時(shí)間)
[ { Variable_name: 'wait_timeout', Value: '10' } ]
Wed Sep 11 2013 15:32:30 GMT+0800 (中國標(biāo)準(zhǔn)時(shí)間)
[ { Variable_name: 'wait_timeout', Value: '10' } ]
Wed Sep 11 2013 15:32:35 GMT+0800 (中國標(biāo)準(zhǔn)時(shí)間)
[ { Variable_name: 'wait_timeout', Value: '10' } ]

連接池，已經(jīng)解決了自動(dòng)重連的問題了，后面我們的開發(fā)，可以盡量使用pooling的方式。

您可能感興趣的文章: