用Nodejs连接MySQL

从零开始nodejs系列文章,将介绍如何利Javascript做为服务端脚本,通过Nodejs框架web开发。Nodejs框架是基于V8的引擎,是目前速度最快的Javascript引擎。chrome浏览器就基于V8,同时打开20-30个网页都很流畅。Nodejs标准的web开发框架Express,可以帮助我们迅速建立web站点,比起PHP的开发效率更高,而且学习曲线更低。非常适合小型网站,个性化网站,我们自己的Geek网站!!

关于作者

  • 张丹(Conan), 程序员Java,R,PHP,Javascript
  • weibo:@Conan_Z
  • blog: http://blog.fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/nodejs-mysql-intro/

nodejs-mysql

前言

MySQL是一款常用的开源数据库产品,通常也是免费数据库的首选。查了一下NPM列表,发现Nodejs有13库可以访问MySQL,felixge/node-mysql似乎是最受关注项目,我也决定尝试用一下。

要注意名字,”felixge/node-mysql”非”node-mysql”,安装部分会介绍这个小插曲!

目录

  1. node-mysql介绍
  2. 建立MySQL测试库
  3. node-mysql安装
  4. 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 -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

新建一个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-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)

这里有一个小插曲

安装“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-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

这回就对了,继续下面的开发!

创建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.js
The 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"';

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

控制台输出:

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种错误

  • 登陆密码错误
  • 数据库宕机
  • 数据库连接超时

新增文件: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 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. 模拟数据库宕机
正常启动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 -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);

程序会每融15秒,做一次查询。

控制台输出

D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.js
Wed 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.js
Wed 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的方式。

转载请注明出处:
http://blog.fens.me/nodejs-mysql-intro/

打赏作者

This entry was posted in Javascript语言实践, 数据库

  • Pingback: Nodejs异步流程控制Async | 粉丝日志()

  • sky™

    很nice

  • Bunm Jyo

    我的ubuntu xampp集成环境 命令行下连接mysql登录操作都正常,可是nodejs里面执行connect()方法就是报1045 28000的错误,求解

    • 似乎是权限的问题。你把对应账号的权限,允许远程访问,再试一下。

      • Bunm Jyo

        权限是没问题的,我另外一台电脑的windows都能正常使用root访问;是否会是xampp集成环境还是my.cnf的文件权限?my.cnf的默认文件权限是多少呢?

      • Bunm Jyo

        Express server listening on port 3000

        { [Error: ER_ACCESS_DENIED_ERROR: Access denied for user ‘root’@’localhost’ (using password: YES)]
        code: ‘ER_ACCESS_DENIED_ERROR’,

        errno: 1045,

        sqlState: ‘28000’,

        fatal: true }

        • 很明显的权限问题,允许远程访问就行了,虽然root这么做很不安全。

          类似下面的语句
          GRANT ALL PRIVILEGES ON *.* TO root@%*%

          • Bunm Jyo

            已经可以通过另外一台电脑局域网远程访问了,但是就node无法连接;蛋都疼裂了~~ =。=
            休息吧,感谢

          • Bunm Jyo

            在windows下调试ok了,
            貌似不是数据库的配置问题,应该是linux的哪个权限出了问题;我再找找原因,搞定后给结贴

          • Bunm Jyo

            问题,在linux下的nodejs 代码,访问mysql建立连接,若密码为空,则能正常连接,若有密码,就一定提示上面的密码错误 1045.确认密码是正确的,linux 命令行下相同密码都能正常连接访问.问题出在哪里呢?是nodejs代码还是linux系统?应该排除mysql库的配置问题,因为在windows平台下相同的库,都是正常的说

          • 没看明白,你说的。

            1. MySQL运行在什么系统?
            2. Node运行在什么系统?
            3. MySQL和Node是不是同一台机器?
            4. MySQL的访问账号的密码是空还是非空?访问账号是否已支持远程访问?

  • liuwu

    代码有上传吗

    • 核心代码都在文章里写了,没有单独上传。

      • liuwu

        你好,我按照文章操作
        连接池配置

        增加文件:app-pooling.js
        老是提示没有mysql.createPool方法。

        我的系统环境

        win7 64bit

        Nodejs:v0.10.26
        Npm:1.4.13

        MySQL:5.6.16

        • 两个可能的问题:

          没有安装mysql库
          npm install mysql@2.0.0-alpha9

          没有增加mysql引用
          var mysql = require(‘mysql’);

  • skyweaver

    think you very much

  • skyweaver

    thank you very much

  • mikan

    想问下lz,mysql的事务怎么控制

    • 我还没有考虑过用node控制事务!

  • zhumingpeng

    悲剧 什么都安装了。。还是不行。。。搞了好久了。。上图 大神帮我看看

    • 你的执行目录不对吧

      1. Desktoptoptempnodedata.js 这是执行文件,但你的安装的MySQL库在E:programnodejs下面。

      2. 每个node项目,都需要下载对应的库,如同Java,而不是像Python,PHP统一安装Node软件中。

      3. 你可以在Desktoptoptempnode,目录再重新安装mysql库,应该就可以运行了。

  • Yumiao Yan

    well,nice

  • aballam

    我可以同时创建多个连接吗?
    var testCon = connectMysql(“test”);
    var prodCon = connectMysql(“prod”);

    例如这样我需要同时更新测试服务器和生产环境的数据库,不知道是怎么同时创建多个连接实例的呢

    • 可以创建多个连接,和创建一个是一样的。

      var pool1 = mysql.createPool({});
      var pool2 = mysql.createPool({});

  • Sylar

    请问 连接池的自动重连的问题 我们不用关注吗?
    也就是说所有它自己会有重链的机制?

    • 是的,会自动重连,我们不用管他。

  • Sylar

    另外 如果我从连接池里面拿的链接 超时了应该怎么处理?
    我看了下 貌似没有可以配置链接池里面链接的超时时间的选项

    • 怎么会没有配置呢?
      你以“超时”为关键字搜索文章内容,我在文章中已经明确测试了超时的情况。

      • Sylar

        谢谢您的回答!!
        是这样的
        resourcePool.getConnection(function(err, connection) {
        if (err){
        if(err.code==’ETIMEDOUT’){
        //connection timeout
        //if failed will crash
        console.log(‘sorry this connection is timeout!’);
        throw err;
        }

        }
        connection.query(sql, function(err, result) {
        if (err) throw err;
        connection.release();
        resolve(result);
        });
        });
        我想在sorry this connection is timeout这里捕获链接超时。 我在pool中设置了
        waitForConnections : true,
        connectionLimit : 10,
        queueLimit : 100,
        connectTimeout : 1

        但是确实能捕获一种超时,但是这种错误会导致程序直接被杀掉
        我是想问 这种连接池中的链接的超时问题我们怎么捕获和解决呢?
        我看了下github的issue 有一个https://github.com/felixge/node-mysql/pull/726
        这个貌似增加了connecTimeout的属性

        但是我得到的错误是error: uncaughtExceptionError: Can’t set headers after they are sent.

  • Pingback: Nodejs学习路线图 | 粉丝日志()

  • kevinleeleelily

    您好,请问,我直接从数据库里取出数据用render传值到页面不可以吗?怎么传过去的是都是Object对象呢?不用Ajax的话得怎么做呢?

    • 1. 可以用render直接到页面
      2. sql取出来的,都是Array类型的 [{},{},{}]
      3. 和Ajax没啥关系吧。

  • rookie

    你好,我用了你的重连方法,后来提示Cannot enqueue Query after fatal error.是出错后不能访问数据库了,可是按道理我重新建立连接了啊,后来我查看日志,db error { [Error: Connection lost: The server closed the connection.] fatal: true, code: ‘PROTOCOL_CONNECTION_LOST’ }
    Error: Connection lost: The server closed the connection.这是什么情况?

    • rookie

      诡异,看样子连接是重新建立了,我随便修改了一行代码,就可以了
      这是什么诡异问题,跟缓存有关?

      • rookie

        应该是这样的,我开发环境用的supervior,随便改了代码,相当重启了服务器,感觉问题还在。

    • 这个错误看起来是MySQL数据库端的问题,服务器关闭的连接,不是客户端的问题。

      • rookie

        我每天晚上走好好的,第二天早上来就连不上了,而且提示PROTOCOL_CONNECTION_LOST,按道理,我有捕获error然后重新建立连接啊,我打算今天用连接池试一试

        • 连接池可以解决自动重连的问题,自己手动重连,有很多细节要注意。

    • XChen

      这可能是因为你将mysql 部分封装了,exports了connection,便重新连接之后,connection并没有更新,其实是一个js的理解问题。

  • damon

    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);
    你没有报 Connection already released 的错误吗?

    • 你的代码似乎有点问题,conn变量,如果是全局变量,很有可能会出现你这样的错误。

      • Xiangxuan Qu

        丹哥您好,复制您的代码之后,我也是这个问题

        • 多做一步检查
          var pool = null;
          if(pool ==null){
          pool = mysql.createPool({
          }
          让mysql.createPool()只执行一次,变成单例的方法。

          • Xiangxuan Qu

            多谢丹哥

        • 让mysql.createPool()只执行一次,变成单例的方法。

  • 赵继宗

    正在使用node-mysql,就看到了博主的文章,赞赞赞赞

  • mark 好好学习下

  • 小xixi

    你好,为什么明明我已经在nodejs目录下,npm install mysql@2.0.0-alpha9,可为什么还是报can not find module mysql的错误呢??急切希望得到指导和帮助

    • npm install mysql

    • 风……清

      在node-demo下npm install mysql

  • hbedw

    文章不错哈 攒一个

  • xiaolin

    楼主,我有个问题想请教你,就是如果我在多个地方加载了这个app-pooling.js文件,那么会不会创建多个连接池出来呢?这个连接池是单例的吗?

    • 会创建多个池。

      所以,pool这个变量最好全局变量,第二次创建是判断变量是否有值,要自己做单例。

      • xiaolin

        感谢楼主~
        但是现在我的疑惑更多了
        baseDao.js,代码如下:
        var pool = mysql.createPool(options);
        module.exports = {
        获取连接,执行响应查询
        }

        template.js,代码如下:
        var pool = mysql.createPool(options);
        module.exports = {
        获取连接,执行响应查询
        }
        问题一:
        只创建一次的时候,数据库是不是应该有10个连接呢?我看代码里面默认是10条,我没有做限制。
        问题二:
        我在一个路由中调用了这两个文件中的方法,但是我看数据库连接也不是更多了,跟创建一次连接池是一样的,这是为什么呢?

        ps:我每次查询后释放了连接

  • ll

    请问,在a.js中module.exports.a = function(){conn.query的操作}, b.js调用a(),如何才能拿到conn.query里面的数据..这个操作是异步的, 怎么写回调呢?

  • Albee_na

    您好,我最近刚学的node.js,然后要用node.js连接MySQL,将数据库中的内容显示出来。我已经安装了node,也安了MySQL,刚刚看了一下您上面的代码,觉得有点困惑,我是要重新安装node和MySQL吗?