首页 > 代码库 > nodejs 访问mysql

nodejs 访问mysql

安装

$ npm install mysql

简介

这个一个mysql的nodejs版本的驱动,是用JavaScript来编写的。不需要编译

这儿有个例子来示范如何使用:

var mysql      = require(‘mysql‘);
var connection = mysql.createConnection({
  host     : ‘localhost‘,
  user     : ‘me‘,
  password : ‘secret‘,
  database : ‘my_db‘
});

connection.connect();

connection.query(‘SELECT 1 + 1 AS solution‘, function(err, rows, fields) {
  if (err) throw err;

  console.log(‘The solution is: ‘, rows[0].solution);
});

connection.end();

 从上面的例子,你可以学到:

 1.connection每一个方法的调用都是被排队的,而且被顺序执行的

   2.用 connection 的end 方法来关闭一个connection。 此方法会在结束前确保那些遗留的query被执行。之后才会发送一个quit packet 给mysql server 端

建立连接

推荐的建立连接的方法如下:

var mysql      = require(‘mysql‘);
var connection = mysql.createConnection({
  host     : ‘example.org‘,
  user     : ‘bob‘,
  password : ‘secret‘
});

connection.connect(function(err) {
  if (err) {
    console.error(‘error connecting: ‘ + err.stack);
    return;
  }

  console.log(‘connected as id ‘ + connection.threadId);
});

 另外,connection的打开也可以被一个query方法隐式的打开

var mysql      = require(‘mysql‘);
var connection = mysql.createConnection(...);

connection.query(‘SELECT 1‘, function(err, rows) {
  // connected! (unless `err` is set)
});

任何类型的连接错误(握手或者网络)都是致命的。绝大多数错误是Error 对象的实例,另外error 有2个典型的属性

  1.err.code :Mysql server error (ER_ACCESS_DENIED_ERROR),获取一个nodejs error ECONNREFUSED ,获取一个网络error PROTOCOL_CONNECTION_LOST

  2.err.fatal : boolean类型,这个值指示这个error是否终止一个connection连接。假如这个error不是一个mysql protocol的错误,这个值应该是 not be defined

 致命的error(fatal)是要被传播到所有的回调函数。如下:

var connection = require(‘mysql‘).createConnection({
  port: 84943, // WRONG PORT
});

connection.connect(function(err) {
  console.log(err.code); // ‘ECONNREFUSED‘
  console.log(err.fatal); // true
});

connection.query(‘SELECT 1‘, function(err) {
  console.log(err.code); // ‘ECONNREFUSED‘
  console.log(err.fatal); // true
});

 正常情况下的error 仅仅被委托到他属于的回调函数。如下:

connection.query(‘USE name_of_db_that_does_not_exist‘, function(err, rows) {
  console.log(err.code); // ‘ER_BAD_DB_ERROR‘
});

connection.query(‘SELECT 1‘, function(err, rows) {
  console.log(err); // null
  console.log(rows.length); // 1
});

 最后,如果一个致命的错误,或者一个正常的错误,没有任何回调函数来处理,那么connection 对象的error 事件将被 emit。

connection.on(‘error‘, function(err) {
  console.log(err.code); // ‘ER_BAD_DB_ERROR‘
});

connection.query(‘USE name_of_db_that_does_not_exist‘);

 注意:‘error‘ events,在node是很特别的,假如一个error发生,而且没有任何函数来处理他,那么堆栈信息将会被打印,进程将被kill。

连接参数

当建立一个连接,你可以下面的参数

  • host:主机的名字,(默认:localhost)
  • port:主机端口号(默认3306)
  • localAddress:主机的ip地址(TCP连接,可选)
  • socketPath:主机是unix 的tcp连接地址,如果设置了host 和port,这个值被忽略
  • user:mysql 授权的用户
  • password:mysql授权的用户密码
  • database:数据库名称
  • multipleStatements:多个查询,(select 1,select 2. )。 处于安全考虑,默认false
  • charset:连接的字符编码,(默认UTF8_GENERAL_CI)
  • timeZone:时区用来存储本地日期,(默认local)
  • connectionTimeOut:连接超时 毫秒,(默认10000)
  • stringifyObjects:是否把对象字符串化(默认false)
  • typeCast:决定是否一个字段的值应该被转化成一个原生的JavaScript类型的值(默认true)
  • queryFormat:自定义的query函数 
connection.config.queryFormat = function (query, values) {
  if (!values) return query;
  return query.replace(/\:(\w+)/g, function (txt, key) {
    if (values.hasOwnProperty(key)) {
      return this.escape(values[key]);
    }
    return txt;
  }.bind(this));
};

connection.query("UPDATE posts SET title = :title", { title: "Hello MySQL" });
  • supportBigNumbers:当处理大的数据的时候应该开启这个选项(默认false),比如在数据库类型中的bitint 或者decimal
  • bigNumberStrings:同时启用bigNumberStrings和supportBigNumbers 将强制大数据结构(Bigint 或者decimal)以JavaScript中的String Objects 返回。(默认值false)。如果supportBigNumbers禁止,此选项将被忽略。如果supportBigNumber开启,此选项关闭,那么如果数字在 -2^53, +2^53 区间,那么返回Number Object 否则返回String Object。
  • dateStrings:强制数据库中的(TIMESTAMP, DATETIME, DATE)转化成字符串否则返回JavaScript Date类型(默认false)
  • debug:是否在控制台打印协议的信息(默认 false)
  • trace:在错误发生的时候打印堆栈信息,(默认true)
  • multipleStatements:待补充(默认false)
  • flag
  • ssl

另外除了以对象的形式传送这些信息,也可以使用字符串形式,如下:

var connection = mysql.createConnection(‘mysql://user:pass@host/db?debug=true&charset=BIG5_CHINESE_CI&timezone=-0700‘);

 

终止连接

终止连接有两种方式,比较优雅的方式是调用end方法。

connection.end(function(err) {
  // The connection is terminated now
});

假如在end的时候发生了致命的错误,err对象会在回调函数中启用,但是connection都会被终止。

另外一种方式是destory 方法,这将立即终端socket连接,destory 也没有任何的事件和回调函数。

 

连接池

一个一个的创建和管理连接比较费事,mysql模块提供了连接池。

var mysql = require(‘mysql‘);
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : ‘example.org‘,
  user            : ‘bob‘,
  password        : ‘secret‘,
  database        : ‘my_db‘
});

pool.query(‘SELECT 1 + 1 AS solution‘, function(err, rows, fields) {
  if (err) throw err;

  console.log(‘The solution is: ‘, rows[0].solution);
});

连接池比分享单个连接和管理多个连接更加的简单

var mysql = require(‘mysql‘);
var pool  = mysql.createPool({
  host     : ‘example.org‘,
  user     : ‘bob‘,
  password : ‘secret‘,
  database : ‘my_db‘
});

pool.getConnection(function(err, connection) {
  // connected! (unless `err` is set)
});

当用一个connection完成操作时,仅仅需要调用connection.release()方法。connection 将会回到连接池中,准备下次连接

var mysql = require(‘mysql‘);
var pool  = mysql.createPool(...);

pool.getConnection(function(err, connection) {
  // Use the connection
  connection.query( ‘SELECT something FROM sometable‘, function(err, rows) {
    // And done with the connection.
    connection.release();

    // Don‘t use the connection here, it has been returned to the pool.
  });
});

假如你想关闭这个连接和从连接池中移除这个连接,请调用destroy方法,连接池将在下次调用的时候创建新的连接。

连接池创建连接是懒加载的,假如你配置了100个连接上限,而你仅仅只用到了5个,那么只有5个连接会被创建。连接池每次从队列的顶部拿连接,release 之后的连接放在底部。

连接池参数

与创建连接时的参数相同,不过有一些额外的:

  • acquireTimeout:默认10000毫秒。一个连接捕获的超时时长,这跟connectionTimeout不同,因为获得一个池连接并不总是涉及到连接.
  • waitForConnections:默认为true。 假如true。在连接池没有连接可用或者连接已经达到上限的时候,连接池将立即返回并携带error参数。 假如false,连接池将排队等待连接可用。
  • connectionLimit:默认10个。
  • queueLimit:连接请求的最大上线数,如果超过这个数,将返回error。如果设置成0,则表示无限制,默认0.

连接池事件

 

  1.connetion:连接池将emit 一个connection event,当一个新的连接被创建。

    

pool.on(‘connection‘, function (connection) {
  connection.query(‘SET SESSION auto_increment_increment=1‘)
});

      2.enqueue:连接池将emit 一个enqueue 事件,当一个connection 入栈

 

pool.on(‘enqueue‘, function () {
  console.log(‘Waiting for available connection slot‘);
});

 

关闭连接池中所有的连接

当连接池结束使用,或者shutdown server 时候

pool.end(function (err) {
  // all connections in the pool have ended
});

这个回调函数,将在所有的query 执行之后被调用。 end 函数一旦被调用,pool.getConnetcion 将不在被执行

连接池集群

todo....

切换用户和改变当前的连接状态

mysql 提供一个改变用户和其他连接属性的命令,且不用shut down 当前的socket

connection.changeUser({user : ‘john‘}, function(err) {
  if (err) throw err;
});

参数:

  • user
  • password
  • charset
  • database

查询

最基本的方式来创建一个查询时调用.query方法(connection,pool等)

1.简易的

connection.query(‘SELECT * FROM `books` WHERE `author` = "David"‘, function (error, results, fields) {
  // error will be an Error if one occurred during the query
  // results will contain the results of the query
  // fields will contain information about the returned results fields (if any)
});

2..query(sqlString, values, callback)

connection.query(‘SELECT * FROM `books` WHERE `author` = ?‘, [‘David‘], function (error, results, fields) {
  // error will be an Error if one occurred during the query
  // results will contain the results of the query
  // fields will contain information about the returned results fields (if any)
});

3..query(options, callback)

 

connection.query({
  sql: ‘SELECT * FROM `books` WHERE `author` = ?‘,
  timeout: 40000, // 40s
  values: [‘David‘]
}, function (error, results, fields) {
  // error will be an Error if one occurred during the query
  // results will contain the results of the query
  // fields will contain information about the returned results fields (if any)
});

 

编码查询的参数

为了避免sql的注入攻击,应该为任何一个用户输入的值进行编码,你可以用mysql.escape(). connection.escape() pool.escape().

var userId = ‘some user provided value‘;
var sql    = ‘SELECT * FROM users WHERE id = ‘ + connection.escape(userId);
connection.query(sql, function(err, results) {
  // ...
});

另外你可以用 ? 字符来替换你所提供的参数

connection.query(‘SELECT * FROM users WHERE id = ?‘, [userId], function(err, results) {
  // ...
});
connection.query(‘UPDATE users SET foo = ?, bar = ?, baz = ? WHERE id = ?‘, [‘a‘, ‘b‘, ‘c‘, userId], function(err, results) {
  // ...
});

不仅仅是 ? 替换。如下有各种情况也会发生编码:

  • 数字类型不受影响
  • Booleans 被 转化成 true/false
  • 日期类型被转化成YYYY-mm-dd HH:ii:ss
  • 字节类型,被转化成16进制字符串,eg 0fa5
  • 数组被转化成list,[‘a‘, ‘b‘] 转成  ‘a‘, ‘b‘
  • 多重数组被转成多重list,[[‘a‘, ‘b‘], [‘c‘, ‘d‘]] 转成 (‘a‘, ‘b‘), (‘c‘, ‘d‘)
  • 对象被转化成 key=value 的形式,假如属性值是fuction 就跳过,假如属性值是object 就 调用 toString()方法
  • undefined/null 转成 null
  • NAN/infinity Mysql不支持,如果插入会引发mysql 报错

可以有这样优雅的实现

var post  = {id: 1, title: ‘Hello MySQL‘};
var query = connection.query(‘INSERT INTO posts SET ?‘, post, function(err, result) {
  // Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = ‘Hello MySQL‘

 

编码查询标识

假如你不信任一个查询标识(database,table,column).因为标识可能来自于用户。你应该编码这些标识,用mysql.escapeId(),connection.escapeId(),pool.escapeId().

var sorter = ‘date‘;
var sql    = ‘SELECT * FROM posts ORDER BY ‘ + connection.escapeId(sorter);
connection.query(sql, function(err, results) {
  // ...
});
var sorter = ‘date‘;
var sql    = ‘SELECT * FROM posts ORDER BY + connection.escapeId(‘posts.+ sorter);
// -> SELECT * FROM posts ORDER BY `posts`.`date`

假如想编码 . 这个字符,把第二个参数设置成true。

var sorter = ‘date.2‘;
var sql    = ‘SELECT * FROM posts ORDER BY ‘ + connection.escapeId(sorter, true);

另外可以用 ?? 字符来替换标识,

var userId = 1;
var columns = [‘username‘, ‘email‘];
var query = connection.query(‘SELECT ?? FROM ?? WHERE id = ?‘, [columns, ‘users‘, userId], function(err, results) {
  // ...
});

console.log(query.sql); // SELECT `username`, `email` FROM `users` WHERE id = 1

预查询

var sql = "SELECT * FROM ?? WHERE ?? = ?";
var inserts = [‘users‘, ‘id‘, userId];
sql = mysql.format(sql, inserts);

 

自定义查询格式化

connection.config.queryFormat = function (query, values) {
  if (!values) return query;
  return query.replace(/\:(\w+)/g, function (txt, key) {
    if (values.hasOwnProperty(key)) {
      return this.escape(values[key]);
    }
    return txt;
  }.bind(this));
};

connection.query("UPDATE posts SET title = :title", { title: "Hello MySQL" });

 

获取刚插入行的ID

假如你正在插入一个表,且这个表有个自增长的ID,你能取到这个ID,如下:

connection.query(‘INSERT INTO posts SET ?‘, {title: ‘test‘}, function(err, result) {
  if (err) throw err;

  console.log(result.insertId);
});

获取受影响的行数

insert, update or delete 

connection.query(‘DELETE FROM posts WHERE title = "wrong"‘, function (err, result) {
  if (err) throw err;

  console.log(‘deleted ‘ + result.affectedRows + ‘ rows‘);
})

获取改变的行数

update语句,他不统计那些没有改变值的行

connection.query(‘UPDATE posts SET ...‘, function (err, result) {
  if (err) throw err;

  console.log(‘changed ‘ + result.changedRows + ‘ rows‘);
})

流式查询

大数据量时,要分包处理

var query = connection.query(‘SELECT * FROM posts‘);
query
  .on(‘error‘, function(err) {
    // Handle error, an ‘end‘ event will be emitted after this as well
  })
  .on(‘fields‘, function(fields) {
    // the field packets for the rows to follow
  })
  .on(‘result‘, function(row) {
    // Pausing the connnection is useful if your processing involves I/O
    connection.pause();

    processRow(row, function() {
      connection.resume();
    });
  })
  .on(‘end‘, function() {
    // all rows have been received
  });

 注意:

  • pause() 方法是关闭流的阀门。
  • 不要为这种流式的查询提供回调函数
  • 不要pause 时间过长,否则将遇到error,(The server close the connection)。这个时间有mysql 服务的 net_write_timeout setting 决定

多条数据查询

默认是关闭的,如果要开启这个功能,需要在connection 选项中开启 multipleStatements: true

一旦开启,可以这么查询:

connection.query(‘SELECT 1; SELECT 2‘, function(err, results) {
  if (err) throw err;

  // `results` is an array with one element for every statement in the query:
  console.log(results[0]); // [{1: 1}]
  console.log(results[1]); // [{2: 2}]
});

 流式查询

var query = connection.query(‘SELECT 1; SELECT 2‘);

query
  .on(‘fields‘, function(fields, index) {
    // the fields for the result rows that follow
  })
  .on(‘result‘, function(row, index) {
    // index refers to the statement this result belongs to (starts at 0)
  });

假如报错了,err.index 属性将告诉你哪个sql语句出错了。mysql 将不会执行下面的语句。

流式的多语句查询是实验性的。

 

Join 查询

当遇到多表连接的join查询,针对column名相同的情况这么处理

var options = {sql: ‘...‘, nestTables: true};
connection.query(options, function(err, results) {
  /* results will be an array like this now:
  [{
    table1: {
      fieldA: ‘...‘,
      fieldB: ‘...‘,
    },
    table2: {
      fieldA: ‘...‘,
      fieldB: ‘...‘,
    },
  }, ...]
  */
});
var options = {sql: ‘...‘, nestTables: ‘_‘};
connection.query(options, function(err, results) {
  /* results will be an array like this now:
  [{
    table1_fieldA: ‘...‘,
    table1_fieldB: ‘...‘,
    table2_fieldA: ‘...‘,
    table2_fieldB: ‘...‘,
  }, ...]
  */
});

事务

connection.beginTransaction(function(err) {
  if (err) { throw err; }
  connection.query(‘INSERT INTO posts SET title=?‘, title, function(err, result) {
    if (err) {
      return connection.rollback(function() {
        throw err;
      });
    }

    var log = ‘Post ‘ + result.insertId + ‘ added‘;

    connection.query(‘INSERT INTO log SET data=http://www.mamicode.com/?‘, log, function(err, result) {>

ping

一个ping包通过connection 发送给服务器

connection.ping(function (err) {
  if (err) throw err;
  console.log(‘Server responded to ping‘);
})

 

mysql To JavaScript 类型转化

NUMBER:

  • TINYINT
  • SMALLINT
  • INT
  • MEDIUMINT
  • YEAR
  • FLOAT
  • DOUBLE

Date

  • TIMESTAMP
  • DATE
  • DATETIME

Buffer

  • TINYBLOB
  • MEDIUMBLOB
  • LONGBLOB
  • BLOB
  • BINARY
  • VARBINARY
  • BIT (last byte will be filled with 0 bits as necessary)

String 

  • CHAR
  • VARCHAR
  • TINYTEXT
  • MEDIUMTEXT
  • LONGTEXT
  • TEXT
  • ENUM
  • SET

自定义类型转化

connection.query({
  sql: ‘...‘,
  typeCast: function (field, next) {
    if (field.type == ‘TINY‘ && field.length == 1) {
      return (field.string() == ‘1‘); // 1 = true, 0 = false
    }
    return next();
  }
});

 

 

nodejs 访问mysql