mysql
目录#
- 安装
- 介绍
- 贡献者
- 赞助商
- 社区
- 建立连接
- 连接选项
- SSL选项
- 终止连接
- 连接池
- 连接池选项
- 连接池事件
- 关闭连接池中的所有连接
- 连接池集群
- 连接池集群选项
- 切换用户并更改连接状态
- 服务器断开
- 执行查询
- 转义查询值
- 转义查询标识符
- 准备查询
- 自定义格式
- 获取插入行的ID
- 获取受影响的行数
- 获取已更改的行数
- 获取连接ID
- 并行执行查询
- 流式查询
- 流式处理、管道传输查询结果
- 多条语句查询
- 存储过程
- 重复列名的join操作
- 事务
- Ping
- 超时
- 错误处理
- 安全处理异常
- 类型转换
- 连接标志
- 调试和报告问题
- 安全问题
- 贡献
- 运行测试
- 计划
安装#
node-mysql是一个Node.js模块。你可以通过npm下载。
在安装之前,你需要下载并安装Node.js。Node.js版本要求:0.6或更高版本。
$ npm install mysql
有关0.9.x版本之前的内容,请访问v0.9分支。
如果你想从Github安装最新版本,看看bug是否已经修复,你可以执行以下命令:
$ npm install mysqljs/mysql
介绍#
node-mysql是mysql的node.js驱动程序。它用JavaScript编写,不需要编译,并且100%获得MIT许可。
示例如下:
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 (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results[0].solution);
});
connection.end();
从此示例中,你可以学习以下内容:
- connection触发的每个方法都按顺序排队执行。
- 通过调用
end()
来关闭连接。并且确保在退出mysql之前,所有剩余查询都将执行完毕。
贡献者#
感谢为该模块贡献代码的人员,请参阅GitHub贡献者页面。
另外,我还要感谢以下人员:
- Andrey Hristov(Oracle)-帮助我解决协议问题。
- Ulf Wendel(Oracle)-帮助我解决协议问题。
赞助商#
以下公司对本项目提供了资金支持,让我可以花更多时间在本项目上(按捐款时间排序):
- Transloadit(我的创业公司,提供文件上传和视频编码服务,请查看)
- Joyent
- pinkbike.com
- Holiday Extras(他们正在招聘)
- Newscope(他们正在招聘)
社区#
如果你想讨论此模块或提出有关此模块的问题,请使用以下方法之一:
- 邮件列表:https://groups.google.com/forum/#!forum/node-mysql
- IRC频道:#node.js(在freenode.net上,我会注意任何消息,包括
mysql
相关的消息)
建立连接#
推荐用以下方法来创建连接:
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);
});
当然,触发查询也能隐式创建连接:
var mysql = require('mysql');
var connection = mysql.createConnection(...);
connection.query('SELECT 1', function (error, results, fields) {
if (error) throw error;
// connected!
});
这两种方法都可以,具体选哪一种取决于你想用哪种方式来处理错误。任何类型的连接错误(handshake或网络错误)均被视为致命错误,请参见错误处理部分以获取更多信息。
连接选项#
建立连接时,可以设置以下选项:
host
:你要连接的数据库的主机名。 (默认:localhost
)port
:要连接的端口号。 (默认:3306
)localAddress
:用于TCP连接的源IP地址。 (可选的)socketPath
:要连接的Unix套接字的路径。如果要使用该选项,host
和port
会被忽略。user
:将进行身份验证的MySQL用户。password
:该MySQL用户的密码。database
:用于此连接的数据库名称(可选)。charset
:用于连接的字符集。这在MySQL的SQL层中称为“Collation”(例如utf8_general_ci
)。如果指定了SQL层字符集(例如utf8mb4
),该字符集的默认排序规则会被应用。 (默认:'UTF8_GENERAL_CI'
)timezone
:MySQL服务器上配置的时区。会将日期和时间转换为JavaScriptDate
对象,反之亦然。这可以是'local'
,'Z'
,或表格中的偏移量+HH:MM
或-HH:MM
。 (默认:'local'
)connectTimeout
: 初次连接到 MySQL 服务器允许的超时毫秒数。 (默认:10000
)stringifyObjects
:将对象字符串化,而不是转换为值。见问题#501。 (默认:false
)insecureAuth
:允许旧的(不安全的)身份验证方法连接到MYSQL。 (默认:false
)typeCast
:是否应将列值转换为原生JavaScript类型。 (默认:true
)queryFormat
:自定义查询格式的函数。详见自定义格式。supportBigNumbers
:当处理数据库中的大数(BIGINT和DECIMAL列)时,应启用此选项(默认值:false
)。bigNumberStrings
:开启supportBigNumbers
和bigNumberStrings
会把BIGINT和DECIMAL类型的值转换为JavaScript String对象返回(该选项的默认值:false
)。开启supportBigNumbers
但关闭bigNumberStrings
会将查询结果转换为String对象或Number对象。如果查询值不在[-2^53, +2^53]范围,无法使用JavaScript Number对象准确表示该值(http://ecma262-5.com/ELS5HTML.htm#Section8.5),查询结果就会被转换为String对象。 否则它们将作为Number对象返回。如果忽略该选项,supportBigNumbers
将被关闭。dateStrings
:强制将日期类型(TIMESTAMP,DATETIME,DATE)以字符串形式返回,而不是转换为JavaScript Date对象。可配置为true
/false
或数组(数组元素为类型字符串)。 (默认:false
)debug
:将协议详细信息打印到stdout。可配置为true
/false
或数组(数组元素为数据包类型名)。 (默认:false
)trace
:生成Error
的堆栈跟踪信息,让你能跟踪从该库被调用的地方到错误出现位置的完整路径(“完整的堆栈跟踪信息”)。同时大多数函数调用的性能都会受到轻微影响。 (默认:true
)multipleStatements
:是否允许每个查询由多个mysql语句构成。需要注意的是,它可能会扩大受到 SQL 注入攻击的范围。 (默认:false
)flags
:使用默认连接标志以外的连接标志列表。也可以将默认值列入黑名单。详见连接标志。ssl
:具有ssl参数的对象或包含ssl配置文件名称的字符串。详见SSL选项。
除了将这些选项作为对象传递外,你还可以使用url字符串。例如:
var connection = mysql.createConnection('mysql://user:pass@host/db?debug=true&charset=BIG5_CHINESE_CI&timezone=-0700');
注意:首先会将查询值解析为JSON,如果解析失败,才会假定查询值为纯文本字符串。
SSL选项
ssl
选项接受字符串或对象。如果是字符串,ssl的值就是预定义的SSL配置文件之一。包括以下配置文件:
"Amazon RDS"
:此配置文件用于连接到Amazon RDS服务器,并且包含来自https://rds.amazonaws.com/doc/rds-ssl-ca-cert.pem和https://s3.amazonaws.com/rds-的证书downloads / rds-combined-ca-bundle.pem
要连接到其他服务器,你需要提供一个对象。对象格式和tls.createSecureContext一样。请注意,参数应使用证书的字符串表示,而不是证书的文件名。示例如下:
var connection = mysql.createConnection({
host : 'localhost',
ssl : {
ca : fs.readFileSync(__dirname + '/mysql-ca.crt')
}
});
不提供CA证书也可以连接到MySQL服务器。但我不建议你这样做。
var connection = mysql.createConnection({
host : 'localhost',
ssl : {
// DO NOT DO THIS
// set up your ca correctly to trust the connection
rejectUnauthorized: false
}
});
终止连接#
结束连接有两种方法。你可以调用end()
:
connection.end(function(err) {
// The connection is terminated now
});
这样可以确保已经在队列里的查询在发送COM_QUIT
数据包到MySQL服务器前被执行。如果发送COM_QUIT
数据包时,抛出了fatal error,那err
参数将被传到回调里,连接会被终止。
终止连接的另一种方法是调用destroy()
方法。这将导致底层的套接字立即终止。另外destroy()
不会触发任何事件或回调。
connection.destroy();
和end()
不同,destroy()
方法不接收回调参数。
连接池#
如果你不想分开创建和单独管理每个连接,可以通过mysql.createPool(config)
来使用内置的连接池。阅读有关连接池的更多信息。
创建一个连接池并直接使用它:
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 (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results[0].solution);
});
上面的示例代码实际上是:pool.getConnection()
->connection.query()
->connection.release()
代码流的一个便捷写法。对于接下来的请求,可以用pool.getConnection()
来共享连接状态。而如果你分别调用pool.query()
两次,使用的是两个不同的连接,请求也是并发进行的。示例代码:
var mysql = require('mysql');
var pool = mysql.createPool(...);
pool.getConnection(function(err, connection) {
if (err) throw err; // not connected!
// Use the connection
connection.query('SELECT something FROM sometable', function (error, results, fields) {
// When done with the connection, release it.
connection.release();
// Handle error after the release.
if (error) throw error;
// Don't use the connection here, it has been returned to the pool.
});
});
如果你想关闭连接并将其从连接池中删除,请使用connection.destroy()
。下次需要时,会创建一个新连接。
跟数据库的连接是按需创建的。如果你将连接池配置为最多允许100个连接,但只使用了5个,那只有5个连接会被创建。连接也是循环使用的。从池的顶部取出,使用完,再放到池的底部。
如果连接池拿到的是已经使用过的连接,会向服务器发送ping数据报,检查连接是否正常。
连接池选项#
连接池的选项包含了connection的选项。创建新连接时,这些选项会被传给connection的构造函数。除了connection的选项外,连接池还接受一些额外的选项:
acquireTimeout
:连接池超时毫秒数。这与connectTimeout
还不太一样,因为获取连接池并不一定会建立连接。如果连接请求已排队,则该请求在队列中等待的时间不计入该超时。 (默认:10000
)waitForConnections
:指定连接池在没有可用连接、连接数已达到限制时的操作。如果设置为true
,连接池会要求连接请求排队,并在有可用连接时触发连接请求。如果设置为false
,错误将立即传入回调里。 (默认为true
)connectionLimit
:一次允许创建的最大连接数。 (默认:10
)queueLimit
:在getConnection
返回错误前,连接池允许排队的最大连接请求数,如果设置为0
,队列里的连接请求数没有限制。 (默认:0
)
连接池事件#
acquire
获取连接后,连接池会触发acquire
事件。触发时间是在全部连接获取后,触发处理连接的回调前。
pool.on('acquire', function (connection) {
console.log('Connection %d acquired', connection.threadId);
});
connection
当一个新的连接在连接池里创建时,会触发connection
事件。你可以通过监听connection
事件来设置session相关的变量。
pool.on('connection', function (connection) {
connection.query('SET SESSION auto_increment_increment=1')
});
enqueue
如果回调已进入队列,等待可用连接,连接池会触发enqueue
事件。
pool.on('enqueue', function () {
console.log('Waiting for available connection slot');
});
release
连接释放时,连接池会触发release
事件。触发时间在所有连接都释放后。触发后,刚释放的连接会被看作是可用连接。
pool.on('release', function (connection) {
console.log('Connection %d released', connection.threadId);
});
关闭连接池中的所有连接#
使用完连接池后,你必须手动关闭所有连接,否则在MySQL服务器关闭连接之前,Node.js事件循环会一直运行。要关闭连接池中的所有连接,请调用连接池的end
方法:
pool.end(function (err) {
// all connections in the pool have ended
});
该end
方法接收一个可选的回调参数。所有连接都关闭后会触发这个回调。
一旦调用了pool.end
,pool.getConnection
和其他操作将无法执行。你需要在所有连接都释放后再调用pool.end
。如果你使用的是pool.query
,而不是pool.getConnection
→ connection.query
→ connection.release
,则需要等到请求结束后再调用pool.end。
pool.end
会对连接池内的每个连接调用connection.end
方法。该方法会将QUIT
加进连接队列,并设置一个flag防止pool.getConnection
创建新的连接。所有正在进行的查询将继续进行,但是新的操作将不会执行。
连接池集群(PoolCluster)#
连接池集群提供多种主机连接方式。 (分组、重试和选择器)
// create
var poolCluster = mysql.createPoolCluster();
// add configurations (the config is a pool config object)
poolCluster.add(config); // add configuration with automatic name
poolCluster.add('MASTER', masterConfig); // add a named configuration
poolCluster.add('SLAVE1', slave1Config);
poolCluster.add('SLAVE2', slave2Config);
// remove configurations
poolCluster.remove('SLAVE2'); // By nodeId
poolCluster.remove('SLAVE*'); // By target group : SLAVE1-2
// Target Group : ALL(anonymous, MASTER, SLAVE1-2), Selector : round-robin(default)
poolCluster.getConnection(function (err, connection) {});
// Target Group : MASTER, Selector : round-robin
poolCluster.getConnection('MASTER', function (err, connection) {});
// Target Group : SLAVE1-2, Selector : order
// If can't connect to SLAVE1, return SLAVE2. (remove SLAVE1 in the cluster)
poolCluster.on('remove', function (nodeId) {
console.log('REMOVED NODE : ' + nodeId); // nodeId = SLAVE1
});
// A pattern can be passed with * as wildcard
poolCluster.getConnection('SLAVE*', 'ORDER', function (err, connection) {});
// The pattern can also be a regular expression
poolCluster.getConnection(/^SLAVE[12]$/, function (err, connection) {});
// of namespace : of(pattern, selector)
poolCluster.of('*').getConnection(function (err, connection) {});
var pool = poolCluster.of('SLAVE*', 'RANDOM');
pool.getConnection(function (err, connection) {});
pool.getConnection(function (err, connection) {});
pool.query(function (error, results, fields) {});
// close all connections
poolCluster.end(function (err) {
// all connections in the pool cluster have ended
});
连接池集群连接选项
canRetry
:如果设置为true
,PoolCluster
连接失败时将尝试重连。 (默认为:true
)removeNodeErrorCount
:如果连接失败,节点的errorCount
的值会增加1。当errorCount
大于removeNodeErrorCount
时,PoolCluster
的一个节点会被删除。 (默认为:5
)restoreNodeTimeout
:如果连接失败,请指定该次连接与下一次连接的间隔时间(毫秒数)。如果设置为0
,该节点将被删除,并且永远不会被重复使用。 (默认为:0
)defaultSelector
:默认选择器。 (默认:RR
)RR
:交替选择一个。 (Round-Robin轮询)RANDOM
:通过随机函数选择节点。ORDER
:选择第一个可用节点。
var clusterConfig = {
removeNodeErrorCount: 1, // Remove the node immediately when connection fails.
defaultSelector: 'ORDER'
};
var poolCluster = mysql.createPoolCluster(clusterConfig);
切换用户并更改连接状态#
MySQL提供了changeUser命令,让你无需关闭底层套接字,就能切换用户、改变连接配置:
connection.changeUser({user : 'john'}, function(err) {
if (err) throw err;
});
可用选项包括:
user
:新用户的名称(默认为旧用户名)。password
:新用户的密码(默认为旧用户的密码)。charset
:新字符集(默认为旧字符集)。database
:新数据库(默认为旧数据库)。
它也会带来副作用:changeUser会重置任何连接状态(包括变量、事务等)。
如果在切换用户过程中,出现错误, 该错误将会被看作致命的连接错误(fatal connection error)。
断开服务器#
由于网络问题、服务器超时、服务器重新启动或崩溃,你可能会与MySQL服务器断开连接。所有这些都被认为是致命错误:err.code = 'PROTOCOL_CONNECTION_LOST'
。详见错误处理部分。
常规做法是建立新连接来重连。设计上,旧连接一旦被终止,旧连接对象就无法重新连接。
如果你使用的是连接池,断开的连接将会从连接池中移除,释放空间以便调用getConnection来创建新连接。
执行查询#
执行查询的最基本方法是调用.query()
对象方法(这些对象如Connection
,Pool
或PoolNamespace
实例)。
使用query()
最简单的方法是.query(sqlString, callback)
,第一个参数是SQL字符串,第二个参数是回调:
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)
});
第二种方法是.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)
});
第三种方法是.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)
});
注意,如果占位符值是作为参数而不是作为options选项,第二种和第三种方法可以结合使用。values
参数将会覆盖values
选项。
connection.query({
sql: 'SELECT * FROM `books` WHERE `author` = ?',
timeout: 40000, // 40s
},
['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)
}
);
如果查询仅包含一个占位符(?
),且该值不是null
,undefined
或数组,那可以将其作为第二个参数直接传递给.query
:
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)
}
);
转义查询值#
请注意:仅在SQL模式NO BACKSLASH ESCAPES(MySQL 服务器的默认状态)被禁用时,转义方法才起作用。
为了避免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 (error, results, fields) {
if (error) throw error;
// ...
});
或者,你也可以使用?
字符作为占位符,代表你会对传进来的数据进行转义:
connection.query('SELECT * FROM users WHERE id = ?', [userId], function (error, results, fields) {
if (error) throw error;
// ...
});
占位符的顺序,和传进来的值的顺序保持一致。例如,在以下查询中foo
等于a
,bar
等于b
,baz
等于c
,id
等于userId
:
connection.query('UPDATE users SET foo = ?, bar = ?, baz = ? WHERE id = ?', ['a', 'b', 'c', userId], function (error, results, fields) {
if (error) throw error;
// ...
});
这看起来与MySQL中的预处理语句相似,但是实际上内部只使用了相同的connection.escape()
。
注意:与预处理语句不同的是,?
就算出现在注释或字符串里,也会被替换成对应的值。
不同类型的值会按不同的规则进行转义,规则如下:
- 数字保持不变
- 布尔值会被转换为
true
/false
- 日期对象转换为
'YYYY-mm-dd HH:ii:ss'
字符串 - Buffer对象会被转换为十六进制字符串,例如
X'0fa5'
- 字符串会被安全转义
- 数组会被转换为列表,例如
['a', 'b']
会被转换为'a', 'b'
- 嵌套数组会被转换为分组列表(用于批量插入),例如
[['a', 'b'], ['c', 'd']]
会被转换为('a', 'b'), ('c', 'd')
- 具有
toSqlString
方法的对象会调用.toSqlString()
,并将返回的值作为初始SQL。 - 对象上的可枚举属性会被转换为
key = 'val'
键值对。如果属性值是一个函数,该属性会被忽略。如果属性值是对象,会对其调用toString()并将返回的值作为val。 undefined
/null
会转换为NULL
NaN
/Infinity
保持不变。 MySQL目前并不支持。如果你强行插入这类值,将触发MySQL错误。
使用转义,你可以让代码变得更精简:
var post = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function (error, results, fields) {
if (error) throw error;
// Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'
toSqlString
方法能让你构造带函数的复杂查询语句:
var CURRENT_TIMESTAMP = { toSqlString: function() { return 'CURRENT_TIMESTAMP()'; } };
var sql = mysql.format('UPDATE posts SET modified = ? WHERE id = ?', [CURRENT_TIMESTAMP, 42]);
console.log(sql); // UPDATE posts SET modified = CURRENT_TIMESTAMP() WHERE id = 42
要构造带toSqlString
方法的对象,你可以使用mysql.raw()
。创建的对象就算使用?
占位符也不会被更改。如果你将函数用作动态值传入,这一点很有用:
注意:传给mysql.raw()
的字符串会忽略所有转义函数,因此在传递未经验证的输入时要小心。
var CURRENT_TIMESTAMP = mysql.raw('CURRENT_TIMESTAMP()');
var sql = mysql.format('UPDATE posts SET modified = ? WHERE id = ?', [CURRENT_TIMESTAMP, 42]);
console.log(sql); // UPDATE posts SET modified = CURRENT_TIMESTAMP() WHERE id = 42
如果你需要自行转义查询,可以直接使用转义函数:
var query = "SELECT * FROM posts WHERE title=" + mysql.escape("Hello MySQL");
console.log(query); // SELECT * FROM posts WHERE title='Hello MySQL'
转义查询标识符#
如果你不信任用户传过来的SQL标识符(数据库/表/列名),那可以使用mysql.escapeId(identifier)
,connection.escapeId(identifier)
或pool.escapeId(identifier)
。比如:
var sorter = 'date';
var sql = 'SELECT * FROM posts ORDER BY ' + connection.escapeId(sorter);
connection.query(sql, function (error, results, fields) {
if (error) throw error;
// ...
});
支持添加合格的标识符。用户传来的标识符和添加的标识符都会被转义。
var sorter = 'date';
var sql = 'SELECT * FROM posts ORDER BY ' + connection.escapeId('posts.' + sorter);
// -> SELECT * FROM posts ORDER BY `posts`.`date`
如果你不想将.
作为合格的标识符,你可以将第二个参数设置为true
,将字符串当作文本标识符(literal identifier)来处理:
var sorter = 'date.2';
var sql = 'SELECT * FROM posts ORDER BY ' + connection.escapeId(sorter, true);
// -> SELECT * FROM posts ORDER BY `date.2`
你也可以使用??
字符作为占位符来表示你要转义的标识符,示例如下:
var userId = 1;
var columns = ['username', 'email'];
var query = connection.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId], function (error, results, fields) {
if (error) throw error;
// ...
});
console.log(query.sql); // SELECT `username`, `email` FROM `users` WHERE id = 1
请注意,?? 语法是实验性的,后续可能会有改动。
如果你向.escape()
或.query()
传入对象,可以使用.escapeId()
来避免SQL注入对象属性。
准备查询语句
你可以使用mysql.format来构造具有多个插入点的查询语句,并对ID和值进行适当转义。一个简单的例子如下:
var sql = "SELECT * FROM ?? WHERE ?? = ?";
var inserts = ['users', 'id', userId];
sql = mysql.format(sql, inserts);
之后,你将获得一个合法的已经被转义过的查询语句,你可以将它发送到数据库。如果你想要在查询实际发送到数据库之前,准备好查询语句,上面这个方法对你会很有帮助。mysql.format背后实现是SqlString.format,所以你还可以选择性地(但不是必需地)传入序列化的对象和时区。这种方法能让你用自定义的方式将对象转换为字符串,以及自定义特定位置/已知时区的日期。
自定义格式
如果你想要自定义查询语句里的转义规则,可以配置连接选项。可以通过获取内置对象来使用内置的.escape()
或其他连接函数。
示例如下:
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:
connection.query('INSERT INTO posts SET ?', {title: 'test'}, function (error, results, fields) {
if (error) throw error;
console.log(results.insertId);
});
如果你处理的数据很大(超过JavaScript数字精度限制),可以开启supportBigNumbers
选项。这样,便能以字符串形式读取插入ID,否则,将会抛出错误。
从数据库中获取大数字时,也需要此选项,否则由于精度限制,你想获得的值将被四舍五入为几百或几千。
获取受影响的行数(affected rows)#
你可以从插入,更新或删除语句中获取受影响的行数。
connection.query('DELETE FROM posts WHERE title = "wrong"', function (error, results, fields) {
if (error) throw error;
console.log('deleted ' + results.affectedRows + ' rows');
})
获取已更改的行数#
你可以从更新语句中获取已更改的行数。
“changedRows”与“ affectedRows”的不同之处在于,它不把值未更新的行纳入计算范围。
connection.query('UPDATE posts SET ...', function (error, results, fields) {
if (error) throw error;
console.log('changed ' + results.changedRows + ' rows');
})
获取连接ID#
你可以使用以下命令获取指定连接的MySQL连接ID(“线程ID”)threadId
属性。
connection.connect(function(err) {
if (err) throw err;
console.log('connected as id ' + connection.threadId);
});
并行执行查询#
MySQL协议是顺序的,这意味着你需要多个连接才能并行执行查询。简单点,你可以为接收的每个HTTP请求分别创建一个连接,并用池来管理连接。
流式查询#
如果你想批量选择很多行,并在获取到每行数据时,分别对它们进行处理。那你可以这样做:
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()
/resume()
底层依赖于套接字和解析器。'result'
事件一定不会被触发(如果你调用了pause()
)。- 流式处理行时,你不能向
query()
方法提供回调。 - 如果插入和更新操作成功,新旧两行的
'result'
事件都会被触发。 - 值得注意的是,你最好不要暂停太长时间,否则会抛出错误
Error: Connection lost: The server closed the connection.
。MySQL服务器上的netwritetimeout setting选项可以让你配置该时间限制。
另外,你可能想知道为什么当前无法流式处理单个行的所有列,当前它们会被整个地存入缓冲区。如果你确实有这方面使用场景,请告诉我,同时也欢迎贡献代码。
流式处理、管道传输查询结果
查询对象提供了.stream([options])
方法让可读流将查询事件装在对象里。根据下游的拥塞情况和highWaterMark
选项,该查询流能和下游轻易pipe,并提供自动暂停/恢复机制。stream的objectMode
选项值设置为true
并且无法更改(如果你需要字节流,则需要使用转换流,例如转换成对象流)。
例如,将查询结果传递到另一个流中很简单(该流的最大缓冲区为5个对象):
connection.query('SELECT * FROM posts')
.stream({highWaterMark: 5})
.pipe(...);
多条语句查询#
出于安全原因(查询语句里如果有值未正确转义,会导致SQL注入攻击),默认不启用多条语句查询功能。如果想使用这个特性,可在创建连接时,开启这个选项:
var connection = mysql.createConnection({multipleStatements: true});
开启后,你可以用执行其他查询语句一样的方式执行多条语句查询:
connection.query('SELECT 1; SELECT 2', function (error, results, fields) {
if (error) throw error;
// `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)
});
如果查询过程中某条语句出错,抛出的Error对象里的err.index
属性会指明具体出错语句。错误发生时,MySQL会停止执行剩余的语句。
请注意,流式处理多条语句查询是实验性的功能。期待你的使用反馈。
存储过程#
和其他mysql驱动程序一样,你可以在查询时调用存储过程。如果存储过程有多个结果,展现形式会和多个语句查询返回的结果一样。
重复列名的 Join 操作#
执行join操作时,返回的可能是有重复列名的结果集。
默认情况下,node-mysql 将按照 MySQL 接收到的列的顺序覆盖名字冲突的列。这可能会导致某些值虽然接收到了,但不可用。
或者,你也可以配置嵌套的table,将列名放在嵌套table下,如下所示:
var options = {sql: '...', nestTables: true};
connection.query(options, function (error, results, fields) {
if (error) throw error;
/* results will be an array like this now:
[{
table1: {
fieldA: '...',
fieldB: '...',
},
table2: {
fieldA: '...',
fieldB: '...',
},
}, ...]
*/
});
你也可以使用分隔字符来合并结果。
var options = {sql: '...', nestTables: '_'};
connection.query(options, function (error, results, fields) {
if (error) throw error;
/* 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 (error, results, fields) {
if (error) {
return connection.rollback(function() {
throw error;
});
}
var log = 'Post ' + results.insertId + ' added';
connection.query('INSERT INTO log SET data=?', log, function (error, results, fields) {
if (error) {
return connection.rollback(function() {
throw error;
});
}
connection.commit(function(err) {
if (err) {
return connection.rollback(function() {
throw err;
});
}
console.log('success!');
});
});
});
});
请注意,beginTransaction(),commit()和rollback()会分别执行START TRANSACTION,COMMIT和ROLLBACK命令。另外,MySQL中的许多命令可能会导致隐式提交,请浏览MySQL文档。
Ping#
通过connection.ping
方法可以发送ping数据包。此方法将向服务器发送ping数据包,并且当服务器响应时,将触发回调。如果发生错误,带有错误参数的回调会被触发。
connection.ping(function (err) {
if (err) throw err;
console.log('Server responded to ping');
})
超时#
每个操作都可配置超时选项。这使你可以为操作指定适当的超时。需要注意的是,这里所说的超时不是MySQL协议的一部分,而是客户端自己的超时机制。这意味着超时发生时,连接会被终止,进一步的操作无法执行。
// Kill query after 60s
connection.query({sql: 'SELECT COUNT(*) AS count FROM big_table', timeout: 60000}, function (error, results, fields) {
if (error && error.code === 'PROTOCOL_SEQUENCE_TIMEOUT') {
throw new Error('too long to count table rows!');
}
if (error) {
throw error;
}
console.log(results[0].count + ' rows');
});
错误处理#
想要编写可靠的应用程序,你最好仔细阅读这个部分。
node-mysql创建的大多数错误实例都是JavaScript的Error对象。此外,它们通常具有两个额外属性:
err.code
:字符串。如果是MySQL服务器错误(例如'ER_ACCESS_DENIED_ERROR'
),那该字符串就包括MySQL错误标志。如果是Node.js错误(例如'ECONNREFUSED'
)或内部错误代码(例如'PROTOCOL_CONNECTION_LOST'
),则该字符串会包含Node.js错误码。err.errno
:数字。包含MySQL服务器错误数字。只来自MySQL服务器错误。err.fatal
:布尔值。表明此错误是否会终止连接。如果错误不是MySQL本身导致的,此属性不会被定义。err.sql
:字符串。包含查询失败的完整SQL。这对于使用更高级别的接口(例如,生成查询的ORM)很有用。err.sqlState
:字符串,包含五个字符的SQLSTATE值。仅来自MySQL服务器错误。err.sqlMessage
:字符串,包含描述错误的消息字符串。仅来自MySQL服务器错误。
致命错误(fatal error)会传播到所有待处理的回调。在以下示例中,连接到无效端口会触发致命错误,且该错误对象会传播到两个待处理的回调中:
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 (error, results, fields) {
console.log(error.code); // 'ECONNREFUSED'
console.log(error.fatal); // true
});
但是,普通错误仅会传到对应回调里。在下面的示例中,只有第一个回调会收到错误,第二个查询按预期工作:
connection.query('USE name_of_db_that_does_not_exist', function (error, results, fields) {
console.log(error.code); // 'ER_BAD_DB_ERROR'
});
connection.query('SELECT 1', function (error, results, fields) {
console.log(error); // null
console.log(results.length); // 1
});
最后,需要注意的是,如果发生致命错误并且没有待处理的回调,或者发生了不属于该回调的普通错误,则该错误将变为连接对象上的'error'
事件。示例如下:
connection.on('error', function(err) {
console.log(err.code); // 'ER_BAD_DB_ERROR'
});
connection.query('USE name_of_db_that_does_not_exist');
注意:'error'
事件在 Node.js 中很特殊。如果它们没有对应的监听对象,错误发生地的堆栈信息会被打印出来,进程也将被杀死。
tl;dr: node-mysql 不希望你忽略掉错误。你应该为错误提供回调。如果你实在不想处理错误,可以参考下面的做法:
// I am Chuck Norris:
connection.on('error', function() {});
安全处理异常#
node-mysql 能安全处理异常。这意味着即使你某个回调函数抛出“ uncaughtException”这样的错误或域捕获的错误,程序依然会正常运行。
类型转换#
为了方便你使用,默认情况下,node-mysql会将数值的mysql类型转换为JavaScript类型。映射关系如下:
Number
- TINYINT
- SMALLINT
- INT
- MEDIUMINT
- YEAR
- FLOAT
- DOUBLE
Date
- TIMESTAMP
- DATE
- DATETIME
Buffer
- TINYBLOB
- MEDIUMBLOB
- LONGBLOB
- BLOB
- BINARY
- VARBINARY
- BIT(最后一个字节将根据需要用0位填充)
String
注意:二进制字符集中的文本是以Buffer
返回的,而不是以字符串返回的。
- CHAR
- VARCHAR
- TINYTEXT
- MEDIUMTEXT
- LONGTEXT
- TEXT
- ENUM
- SET
- DECIMAL (可能超过浮点精度)
- BIGINT(可能超过浮点精度)
- TIME(可能会映射为Date,但是对应的日期值是什么呢?)
- GEOMETRY(我没用过,如果你想用,请联系我)
我们不建议关掉类型转换选项,但是你如果实在想关掉,可以在以单一连接上进行此操作:
var connection = require('mysql').createConnection({typeCast: false});
也可以在查询层关掉这个选项:
var options = {sql: '...', typeCast: false};
var query = connection.query(options, function (error, results, fields) {
if (error) throw error;
// ...
});
自定义类型转换
你还可以传入函数对类型转换进行自定义处理。你会得到一些列信息,例如数据库,表和名称,以及类型和长度。
该函数接收两个参数field
和next
,通过field
对象触发解析器函数,并为特定字段返回值。
field
参数是Field
对象,包含需要解析的字段的数据。以下是Field
对象的属性:
db
- 字段所属的数据库名。table
- 字段所属的表名。name
- 字段名。type
- 字段类型(大写)。length
- 数据库指明的字段长度。
next
参数是一个function
。该函数会返回指定字段的默认转换类型。
field
对象有这些方法来帮助你获取字段相关的数据:
.string()
-将字段解析为字符串。.buffer()
-将字段解析为Buffer
。.geometry()
-将字段解析为几何值。
MySQL协议是基于文本的协议。这就是说,传输过程中,所有字段类型都用字符串表示。这就是为什么field
对象的方法都是字符串相关的。根据类型(如INT
),类型转换应将字符串字段转换为其他JavaScript类型(例如number
)。
下面这个示例会将TINYINT(1)
转换为布尔值:
connection = mysql.createConnection({
typeCast: function (field, next) {
if (field.type === 'TINY' && field.length === 1) {
return (field.string() === '1'); // 1 = true, 0 = false
} else {
return next();
}
}
});
注意:在你自定义的类型转换回调函数里,要想触发解析器函数,你必须调用 field 的三个方法之一。而且,这三个方法都只能被调用一次。
连接标志#
如果你想要更改默认的连接标志,可以使用连接选项flags
。传入带有逗号分隔的字符串就可以添加连接标志。如果你不希望使用默认标志,请在标志前加上减号。要添加不在默认列表中的标志,只需编写标志名称,或在其前面加上加号(不区分大小写)。
请注意,有些标志虽然可用,但如果node-mysql不支持,你也不能使用(例如:压缩)。
例子
将默认连接标志中的FOUND_ROWS标志列入黑名单。示例如下
var connection = mysql.createConnection("mysql://localhost/test?flags=-FOUND_ROWS");
默认标志
默认情况下,连接标志如下:
CONNECT_WITH_DB
-连接时指定数据库。FOUND_ROWS
-发送找到的行而不是受影响的行(affectedRows
)。IGNORE_SIGPIPE
-遗留标志;没有作用。IGNORE_SPACE
-让解析器忽略查询语句里(
前的空格。LOCAL_FILES
- 可以使用LOAD DATA LOCAL
。LONG_FLAG
LONG_PASSWORD
-使用旧密码验证的改进版本。MULTI_RESULTS
-可以处理COM_QUERY的多个结果集。ODBC
遗留标志;没有作用。PROTOCOL_41
-使用4.1协议。PS_MULTI_RESULTS
-可以处理 COM STMT EXECUTE的多个结果集RESERVED
-4.1协议的旧标志。SECURE_CONNECTION
-支持本地4.1身份验证。TRANSACTIONS
-事务状态标志。
此外,如果将multipleStatements
设定为true
,下面的标志也会被发送:
MULTI_STATEMENTS
-每个查询可发送多个语句。
其他可用标志
还有其他标志可用。它们可能起作用也可能不起作用,但是你仍然可以指定。
COMPRESS
INTERACTIVE
NO_SCHEMA
PLUGIN_AUTH
REMEMBER_OPTIONS
SSL
SSL_VERIFY_SERVER_CERT
调试和报告问题#
可以开启debug
选项来调试:
var connection = mysql.createConnection({debug: true});
这将在标准输出上打印所有输入和返回的数据包。如果你只想调试数据包类型,可以将类型装到数组里传入,:
var connection = mysql.createConnection({debug: ['ComQueryPacket', 'RowDataPacket']});
这样,就能只针对查询和数据包进行调试。
如果 Debug 没有帮助,请随时打开 GitHub Issue。一个好的 GitHub Issue 将包括:
- 重现问题所需的最少代码量(如果可能)
- 你可以收集尽可能多的调试输出和有关你的环境的信息(mysql版本,节点版本,操作系统等)。
安全问题#
安全问题最好不要通过GitHub或其他公共论坛。你应该保密该安全问题,我们评估该问题后,如果这确实是个安全问题,会修复并计划发布日期,如果这不是安全相关问题。(在这种情况下,它可以发布在公共论坛上,例如GitHub issue)。
主要的私人论坛是电子邮件,既可以通过电子邮件发送模块的作者,也可以打开GitHub issue,仅询问应向谁解决安全问题,而无需透露问题或问题类型。
理想的报告应明确指出什么是安全问题以及如何加以利用,理想情况下,还应附有概念证明(“ PoC”),以使合作者可以针对和解决潜在的问题。
贡献#
该项目欢迎社区的贡献。你可以使用GitHub pull request。如果你不知道怎么创建GitHub pull request,请参阅GitHub文档“创建pull request”。
pull request要求:
- 详细描述你的pull request。应包括“内容”和“原因”。
- 测试应尽可能地通过。详见运行测试部分。安全起见, GitHub也会自动运行测试。
- 更改部分应包含测试。新功能有对应的测试,错误修复也应该有对应的测试(如果代码不做任何变化,测试不通过,但应用错误修复的代码后,该测试通过。)运行
npm run test-cov
来生成coverage/
文件夹,文件夹里是包含代码覆盖率的HTML页面,通过这些HTML页面,你可以更好地了解所添加的内容是否被纳入测试范围。 - 如果你的pull request是一项新功能,你应该在
Readme.md
里详细说明。 - 为了保证代码样式一致,请运行
npm run lint
,并尽可能修复不符合lint规则的地方。
运行测试#
测试套件分为两个部分:单元测试和集成测试。单元测试可以在任何计算机上运行,而集成测试则需要设置MySQL服务器实例。
运行单元测试
$ FILTER=unit npm test
运行集成测试
设置环境变量MYSQL_DATABASE
,MYSQL_HOST
,MYSQL_PORT
,MYSQL_USER
和MYSQL_PASSWORD
。MYSQL_SOCKET
可以代替MYSQL_HOST
和MYSQL_PORT
,这样,直接与 UNIX 套接字连接。运行npm test
。
例如,如果你安装了mysql, 并在localhost:3306上运行,且你没有为root
用户设置密码,运行:
$ mysql -u root -e "CREATE DATABASE IF NOT EXISTS node_mysql_test"
$ MYSQL_HOST=localhost MYSQL_PORT=3306 MYSQL_DATABASE=node_mysql_test MYSQL_USER=root MYSQL_PASSWORD= FILTER=integration npm test
Todo#
- 准备好的语句
- 支持UTF-8 / ASCII以外的其他编码