Node.js 入门笔记(6) - mysql

node.js 访问mysql

1. mysql模块使用

模块安装：npm install mysql --save

模块引用：var mysql = require('mysql'); 

创建连接池：var pool = mysql.createPool(options);  其中options为

{
	host: '127.0.1',
	user: 'node',
	password: '123456',
	database: 'node',
	port: 3306
}

获得连接

pool.getConnection(function(err, connection){
     //以下代码省略
});

执行sql语句：connection.query(sql,[parameters],[function(err, result){}])；执行返回的数据为JSON格式。

释放链接：connection.release();

2. 实例

定义SQLSt字典变量，存放各种SQL语句。用module.exports方式定义各种方法，其中callback是将数据返回给前端的回调函数。

var mysql = require('mysql');
var $conf = require('../../conf');

var pool = mysql.createPool($conf.mysql);

var SQLStmt = {
    list: 'SELECT  id, eng_name, chn_name, ip, type_flag, vaild_flag, '
          +' date_format(created, "%Y-%m-%d")  as created '
          +'FROM machine order by id',
    listByKey: 'SELECT  id, eng_name, chn_name, ip, type_flag, vaild_flag, '
          +' date_format(created, "%Y-%m-%d")  as created '
          +'FROM machine WHERE id=?',
    add: 'INSERT INTO machine ( eng_name, chn_name, ip, ' 
          +' type_flag, vaild_flag, created) values (?,?,?,?,?,?) ',
    edit: 'UPDATE machine SET eng_name=?, chn_name=?, ip=?, '
          +'type_flag=?, vaild_flag=?, created=? WHERE id=?',
    del: 'DELETE FROM machine WHERE id=?'
};

module.exports = {
    //查询数据
    list: function(req, res, next, callback){
	pool.getConnection(function(err, connection){
            if(connection){
			connection.query(SQLStmt.list, function(err, result){
					callback(res, result);
			});
			connection.release();
		}else{
			//console.log(err);
			callback(res, null);
		}
	});
    },
    //增加
    add: function(req, res, next, callback){
        var obj = req.body;
        if (obj === null){
            console.log("上传数据为空");
            callback(res, null);
        }else{
            pool.getConnection(function(err, connection){
                if(connection){
                    connection.query(SQLStmt.add, [obj.eng_name, obj.chn_name, obj.ip,
                        obj.type_flag, obj.vaild_flag, obj.created], function(err, result){
                            //console.log(err);
                            //console.log(result);
                            callback(res, result);
                    });
                    connection.release();
                }else{
                    console.log(err);
                    callback(res, null);
                }
            });
        }
    },
    //修改
    edit: function(req, res, next, callback){
        var obj = req.body;
        if (obj === null){
            console.log("上传数据为空");
            callback(res, null);
        }else{
            console.log(obj);
            pool.getConnection(function(err, connection){
                if(connection){
                    connection.query(SQLStmt.edit, [obj.eng_name, obj.chn_name, obj.ip, obj.type_flag, 
                        obj.vaild_flag, obj.created, obj.id], function(err, result){
                        //console.log(err);
                        callback(res, result);
                    });
                    connection.release();
                }else{
                    console.log(err);
                    callback(res, null);
                }
            });
        }
    },
    //删除
    del: function(req, res, next, callback){
        var obj = req.body;
        console.log(obj);
        if (obj === null){
            console.log("上传数据为空");
            callback(res, null);
        }else{
            pool.getConnection(function(err, connection){
                if(connection){
                    connection.query(SQLStmt.del, [parseInt(obj.id)], function(err, result){
                        //console.log(err);
                        callback(res, result);
                    });
                    connection.release();
                }else{
                    console.log(err);
                    callback(res, null);
                }
            });
        }
    }
}