目录
简介和安装
Node.js与MySQL交互操作有很多库,具体可以在 查看。
我选择了felixge/node-mysql,用的人比较多,先随大溜看看它的使用,暂时没有太过纠结于各库之间的执行性能问题,对其它库有研究的筒子也可以分享一下性能要求较高时的选择^_^!
地址:
This is a node.js driver for mysql. It is written in JavaScript, does not require compiling, and is 100% MIT licensed
用纯JavaScript实现。MIT 100%!
安装
npm install mysql
注意:安装前先把目录cd到node.exe所在目录下,这样执行安装命令时,会找到目录下node_modules,并安装在此目录下,否则使用mysql时,你会出现 Error: Cannot find module 'mysql'
测试MySQL
MySQL版本:5.5
在官方示例的基础下修改一下并测试:
var mysql = require('mysql'); //调用MySQL模块//创建一个connectionvar connection = mysql.createConnection({ host : '192.168.0.200', //主机 user : 'root', //MySQL认证用户名 password : 'abcd', //MySQL认证用户密码 port: '3306', //端口号}); //创建一个connectionconnection.connect(function(err){ if(err){ console.log('[query] - :'+err); return; } console.log('[connection connect] succeed!');}); //执行SQL语句connection.query('SELECT 1 + 1 AS solution', function(err, rows, fields) { if (err) { console.log('[query] - :'+err); return; } console.log('The solution is: ', rows[0].solution); }); //关闭connectionconnection.end(function(err){ if(err){ return; } console.log('[connection end] succeed!');});
成功执行!
认识一下Connection Options
要想创建一个数据库连接,先就要认识清楚Options
host:主机地址 (默认:localhost)
user:用户名
password:密码
port:端口号 (默认:3306)
database:数据库名
charset:连接字符集(默认:'UTF8_GENERAL_CI',注意字符集的字母都要大写)
localAddress:此IP用于TCP连接(可选)
socketPath:连接到unix域路径,当使用 host 和 port 时会被忽略
timezone:时区(默认:'local')
connectTimeout:连接超时(默认:不限制;单位:毫秒)
stringifyObjects:是否序列化对象(默认:'false' ;与安全相关)
typeCast:是否将列值转化为本地JavaScript类型值 (默认:true)
queryFormat:自定义query语句格式化方法
supportBigNumbers:数据库支持bigint或decimal类型列时,需要设此option为true (默认:false)
bigNumberStrings:supportBigNumbers和bigNumberStrings启用 强制bigint或decimal列以JavaScript字符串类型返回(默认:false)
dateStrings:强制timestamp,datetime,data类型以字符串类型返回,而不是JavaScript Date类型(默认:false)
debug:开启调试(默认:false)
multipleStatements:是否许一个query中有多个MySQL语句 (默认:false)
flags:用于修改连接标志,更多详情:
ssl:使用ssl参数(与crypto.createCredenitals参数格式一至)或一个包含ssl配置文件名称的字符串,目前只捆绑Amazon RDS的配置文件
其它:
可以使用URL形式的加接字符串,不多介绍了,不太喜欢那种格式,觉得可读性差,也易出错,想了解的可以去主页上看。
MYSQL CURD
首先创建一个测试数据库nodesample,在数据库中建一个userinfo表
CREATE DATABASE IF NOT EXISTS nodesample CHARACTER SET UTF8;USE nodesample;SET FOREIGN_KEY_CHECKS=0;DROP TABLE IF EXISTS `userinfo`;CREATE TABLE `userinfo` ( `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `UserName` varchar(64) NOT NULL COMMENT '用户名', `UserPass` varchar(64) NOT NULL COMMENT '用户密码', PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';
1.向UserInfo表中插入一条数据
var mysql = require('mysql'); var connection = mysql.createConnection({ host : '192.168.0.200', user : 'root', password : 'abcd', port: '3306', database: 'nodesample', }); connection.connect();var userAddSql = 'INSERT INTO userinfo(Id,UserName,UserPass) VALUES(0,?,?)';var userAddSql_Params = ['Wilson', 'abcd'];//增connection.query(userAddSql,userAddSql_Params,function (err, result) { if(err){ console.log('[INSERT ERROR] - ',err.message); return; } console.log('--------------------------INSERT----------------------------'); //console.log('INSERT ID:',result.insertId); console.log('INSERT ID:',result); console.log('-----------------------------------------------------------------\n\n'); });connection.end();
从执行结果可以看出,result中包含一些有用的信息,affectedRows(受影响的行数) insertId(插入的主键ID)等等。。。
有受影响的行数和插入数据的ID,就可以很方便进行后续的一些操作(比如判断是否成功或者继续根据插入数据的ID进行其它操作)
下面,我去数据库服务器中使用Navicate for MySQL工具查询一下UserInfo表
插入成功!
2.更新1操作中插入的数据信息
var mysql = require('mysql'); var connection = mysql.createConnection({ host : '192.168.0.200', user : 'root', password : 'abcd', port: '3306', database: 'nodesample', }); connection.connect();var userModSql = 'UPDATE userinfo SET UserName = ?,UserPass = ? WHERE Id = ?';var userModSql_Params = ['钟慰', '5678',1];//改connection.query(userModSql,userModSql_Params,function (err, result) { if(err){ console.log('[UPDATE ERROR] - ',err.message); return; } console.log('--------------------------UPDATE----------------------------'); console.log('UPDATE affectedRows',result.affectedRows); console.log('-----------------------------------------------------------------\n\n');});connection.end();
从返回受影响行数来看,更新应该是正确的,下面我们用一个SELECT查询示例去验证更新是否成功!
3.查询
var mysql = require('mysql'); var connection = mysql.createConnection({ host : '192.168.0.200', user : 'root', password : 'abcd', port: '3306', database: 'nodesample', }); connection.connect();var userGetSql = 'SELECT * FROM userinfo';//查connection.query(userGetSql,function (err, result) { if(err){ console.log('[SELECT ERROR] - ',err.message); return; } console.log('--------------------------SELECT----------------------------'); console.log(result); console.log('-----------------------------------------------------------------\n\n'); });connection.end();
从查询出来的结果可以看出,result返回了一个JSON格式的数据,同时表示第二步中更新是成功!
4.删除
var mysql = require('mysql'); var connection = mysql.createConnection({ host : '192.168.0.200', user : 'root', password : 'abcd', port: '3306', database: 'nodesample', }); connection.connect();var userDelSql = 'DELETE FROM userinfo';//删connection.query(userDelSql,function (err, result) { if(err){ console.log('[DELETE ERROR] - ',err.message); return; } console.log('--------------------------DELETE----------------------------'); console.log('DELETE affectedRows',result.affectedRows); console.log('-----------------------------------------------------------------\n\n'); });connection.end();
去数据库服务器里查询看一下
查询userinfo表中已没有数据,删除成功!
到此Node.js结合MySQL的增、删、改、查操作应会部完成了!
Nodejs 调用带out参数的存储过程,并得到out参数返回值
Nodejs调用存储过程没什么特别,只是调用带out参数存储过程并得到out参数返回值可能有些人比较疑惑,下面用个示例来介绍一下
先创建一个带out参数的存储过程 P_UserInfo
-- ------------------------------ Procedure structure for `P_UserInfo`-- ----------------------------DROP PROCEDURE IF EXISTS `P_UserInfo`;DELIMITER ;;CREATE DEFINER=`root`@`localhost` PROCEDURE `P_UserInfo`(IN ExtId INT,IN ExtUserName VARCHAR(64),IN ExtUserPass VARCHAR(64),OUT ExtReturnVal INT)TOP: BEGINDECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET ExtReturnVal = 0; -- Failed END;START TRANSACTION; INSERT INTO userinfo(Id,UserName,UserPass) VALUES(ExtId,ExtUserName,ExtUserPass); SET ExtReturnVal = 1; SELECT ExtReturnVal; COMMIT;END;;DELIMITER ;
下面,来写个示例来调用
var mysql = require('mysql'); var connection = mysql.createConnection({ host : '192.168.0.200', user : 'root', password : 'abcd', port: '3306', database: 'nodesample' }); connection.connect();var userProc = 'call P_UserInfo(?,?,?,@ExtReturnVal);';var userProc_Params = [0,'Wilson Z','abcd'];//调用存储过程connection.query(userProc,userProc_Params,function (err, retsult) { if(err){ console.log('[EXEC PROC ERROR] - ',err.message); return; } console.log('--------------------------PROC----------------------------'); console.log(retsult); console.log(retsult[0][0].ExtReturnVal); console.log('-----------------------------------------------------------------\n\n'); });connection.end();
去数据为中看一下
在表中正确插入此数据,而且正确的得到了out参数的值,细心的可能会发现我存储过程中在SET ExtReturnVal = 1表示成功后,多了一句SELECT ExtReturnVal;
这样就可以通地查询得到out参数的值了!(有别的方法可以分享一下^_^!)
结束数据库连接两种方法和区别
前面的示例中我在结尾处都会调用一个connection.end()方法,这个方法connection.connect()对应,一个开始,一个结束!
结束连接其实有两种方法end(),destory();
end()
end()方法在queries都结束后执行,end()方法接收一个回调函数,queries执行出错,仍然后结束连接,错误会返回给回调函数err参数,可以在回调函数中处理!
destory()
比较暴力,没有回调函数,即刻执行,不管queries是否完成!
连接池Pooling connections
1.连接池的创建,使用createPool方法,options和createConntion一致,可以监听connection事件。
var mysql = require('mysql');//创建连接池var pool = mysql.createPool({ host : '192.168.0.200', user : 'root', password : 'abcd'});//监听connection事件pool.on('connection', function(connection) { connection.query('SET SESSION auto_increment_increment=1'); });
连接池可以直接使用,也可以共享一个连接或管理多个连接(引用官方示例)
//直接使用pool.query('SELECT 1 + 1 AS solution', function(err, rows, fields) { if (err) throw err; console.log('The solution is: ', rows[0].solution);});//共享pool.getConnection(function(err, connection) { // connected! (unless `err` is set)});
2.其它连接池配置选项
waitForConnections
当连接池没有连接或超出最大限制时,设置为true且会把连接放入队列,设置为false会返回error
connectionLimit
连接数限制,默认:10
queueLimit
最大连接请求队列限制,设置为0表示不限制,默认:0
3.释放
调用connection.release()方法,会把连接放回连接池,等待其它使用者使用!
4.使用示例
var mysql = require('mysql');var pool = mysql.createPool({ host : '192.168.0.200', user : 'root', password : 'abcd', port: '3306', database: 'nodesample' });pool.getConnection(function(err, connection) { connection.query( 'SELECT * FROM userinfo;', function(err, result) { console.log(result); connection.release(); }); connection.query( 'SELECT * FROM userinfo;', function(err, result) { console.log(result); connection.release(); });});
在实际开发过程中,应该还是使用连接池的方式比较好!
断线重连
数据库可以因为各种原因导致连接不上,这种就必须有重连接机制!
主要判断errorcode:PROTOCOL_CONNECTION_LOST
1.首先去数据库服务器停止MySQL服务
2.运行断线重连代码
代码中在error事件中,判断返回errorcode是否为:PROTOCOL_CONNECTION_LOST ,如果是用setTimeout定时2秒重连!
从执行结果可以看出,大约2-3秒会输出重连输出信息,不断在尝试重新连接!
3.去数据为服务器,开启mysql服务器,再看看执行结果
当数据库服务器mysql服务重新启动后,执行结果输出连接成功,不再输出断线重连日志^_^!
var mysql = require('mysql');var db_config = { host : '192.168.0.200', user : 'root', password : 'abcd', port: '3306', database: 'nodesample' };var connection;function handleDisconnect() { connection = mysql.createConnection(db_config); connection.connect(function(err) { if(err) { console.log("进行断线重连:" + new Date()); setTimeout(handleDisconnect, 2000); //2秒重连一次 return; } console.log("连接成功"); }); connection.on('error', function(err) { console.log('db error', err); if(err.code === 'PROTOCOL_CONNECTION_LOST') { handleDisconnect(); } else { throw err; } });}handleDisconnect();
其它...
1. escape()
防止SQL注入,可以使用pool.escape()和connect.escape(),不多说了,自已可以试试,我提供一个示例
var mysql = require('mysql');var pool = mysql.createPool({ host: '192.168.0.200', user: 'root', password:'abcd', port:'3306', database:'nodesample'});pool.getConnection(function(err,connection){ connection.query('SELECT * FROM userinfo WHERE id = ' + '5 OR ID = 6',function(err,result){ //console.log(err); console.log(result); connection.release(); }); connection.query('SELECT * FROM userinfo WHERE id = ' + pool.escape('5 OR ID = 6') ,function(err,result){ //console.log(err); console.log(result); connection.release(); });})
结果可以看出,第1个query拼接条件可以被执行,而通过escape方法转义后的忽略了后面的拼接的部分!
大家可以看到我前面用的?占位的方式,简单的试了一下,好处并没有这种危险,这里就不提供示例了,在我上面提供的代码上改一下就可以试出来^_^!
2.mysql.escapeId(identifier)
如果不能信任由用户提示的SQL标识符(数据库名,列名,表名),可以使用此方法,官方提供有示例(最常见的是通过列名来排序什么的...)
3.mysql.format
准备查询,该函数会选择合适的转义方法转义参数
....
还有一些安全相关的方法可以自行查看一下官方说明
本次也未讲到连接池集群,以后有时间再把这次漏掉的一些补一篇当提高篇吧,现在基础的和MySQL交互应该问题不大了^_^!