Nodejs学习笔记(四)--- 与MySQL交互(felixge/node-mysql)

简介和安装

  Node.js与MySQL交互操作有很多库,具体可以在 https://www.npmjs.org/search?q=mysql  查看。

  我选择了felixge/node-mysql,用的人比较多,先随大溜看看它的使用,暂时没有太过纠结于各库之间的执行性能问题,对其它库有研究的筒子也可以分享一下性能要求较高时的选择^_^! 

  地址:https://github.com/felixge/node-mysql

https://www.npmjs.org/package/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模块

//创建一个connection
var connection = mysql.createConnection({     
  host     : '192.168.0.200',       //主机
  user     : 'root',               //MySQL认证用户名
  password : 'abcd',        //MySQL认证用户密码
  port: '3306',                   //端口号
}); 
//创建一个connection
connection.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);  
});  
//关闭connection
connection.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' ;与安全相关https://github.com/felixge/node-mysql/issues/501

typeCast:是否将列值转化为本地JavaScript类型值 (默认:true)

queryFormat:自定义query语句格式化方法 https://github.com/felixge/node-mysql#custom-format

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:用于修改连接标志,更多详情:https://github.com/felixge/node-mysql#connection-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: BEGIN
DECLARE 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 ;

  下面,来写个示例来调用

调用存储过程得到out参数示例源码

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(),不多说了,自已可以试试,我提供一个示例

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交互应该问题不大了^_^!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏编程之路

羊皮书APP(Android版)开发系列(二十二)10分钟秒懂单例模式

9630
来自专栏我的博客

PHP Mysql函数汇总表

mysql_affected_rows — 取得前一次 MySQL 操作所影响的记录行数mysql_change_user —  改变活动连接中登录的用户mys...

39650
来自专栏小怪聊职场

MySQL(一)|性能分析方法、SQL性能优化和MySQL内部配置优化

772100
来自专栏好好学java的技术栈

玩转springboot:整合mybatis实例

这篇文章讲解一下springboot整合mybatis,其实,springboot整合mybatis和springmvc整合mybatis并没有什么太大的区别,...

15530
来自专栏aoho求索

MySQL探秘(二):SQL语句执行过程详解

 昔日庖丁解牛,未见全牛,所赖者是其对牛内部骨架结构的了解,对于MySQL亦是如此,只有更加全面地了解SQL语句执行的每个过程,才能更好的进行SQL的设计和优化...

27510
来自专栏李家的小酒馆

数据库事务和索引

事务的性质:   原子性:同一个事务中的所有操作要不然全部成功要不然全部失败   一致性:一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态,,也...

20200
来自专栏python成长之路

mysql在ubuntu中的操作笔记(详)

17440
来自专栏淡定的博客

sql注入入门学习(数字型)(连载中)

在MySQL中,表名存放在information_schema数据库下tables表table_name字段中、查表名我们主要用到的是TABLES表

34940
来自专栏Java帮帮-微信公众号-技术文章全总结

​【数据库】MySQL进阶三、游标简易教程

【数据库】MySQL进阶三、游标简易教程 mysql游标简易教程 从mysql V5.5开始,进行了一次大的改变,就是将InnoDB作为默认的存...

52970
来自专栏C/C++基础

ERROR 1062 (23000) at line 1: Duplicate entry '1332883220' for key 'group_key'

我有一个数据表,记录一个QQ号加好友的活跃天数、加好友次数、加好友的toUin数等信息。数据表的建表语句如下:

17540

扫码关注云+社区

领取腾讯云代金券