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=?', log, function(err, result) { if (err) { return connection.rollback(function() { throw err; }); } connection.commit(function(err) { if (err) { return connection.rollback(function() { throw err; }); } console.log('success!'); }); }); }); });
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(); } });