当前位置: 首页 > 工具软件 > node-mysql > 使用案例 >

Node-MySQL 官方文档

东明德
2023-12-01

mysql

目录#

安装#

node-mysql是一个Node.js模块。你可以通过npm下载

在安装之前,你需要下载并安装Node.js。Node.js版本要求:0.6或更高版本。

通过npm install来安装mysql:

$ 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贡献者页面

另外,我还要感谢以下人员:

赞助商#

以下公司对本项目提供了资金支持,让我可以花更多时间在本项目上(按捐款时间排序):

社区#

如果你想讨论此模块或提出有关此模块的问题,请使用以下方法之一:

  • 邮件列表: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套接字的路径。如果要使用该选项,hostport会被忽略。
  • 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:开启supportBigNumbersbigNumberStrings会把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.endpool.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:如果设置为truePoolCluster连接失败时将尝试重连。 (默认为: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()对象方法(这些对象如ConnectionPoolPoolNamespace实例)。

使用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)
  }
);

如果查询仅包含一个占位符(?),且该值不是nullundefined或数组,那可以将其作为第二个参数直接传递给.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等于abar等于bbaz等于cid等于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;
  // ...
});

自定义类型转换

你还可以传入函数对类型转换进行自定义处理。你会得到一些列信息,例如数据库,表和名称,以及类型和长度。

该函数接收两个参数fieldnext,通过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要求:

  1. 详细描述你的pull request。应包括“内容”和“原因”。
  2. 测试应尽可能地通过。详见运行测试部分。安全起见, GitHub也会自动运行测试。
  3. 更改部分应包含测试。新功能有对应的测试,错误修复也应该有对应的测试(如果代码不做任何变化,测试不通过,但应用错误修复的代码后,该测试通过。)运行npm run test-cov来生成coverage/文件夹,文件夹里是包含代码覆盖率的HTML页面,通过这些HTML页面,你可以更好地了解所添加的内容是否被纳入测试范围。
  4. 如果你的pull request是一项新功能,你应该在Readme.md里详细说明。
  5. 为了保证代码样式一致,请运行npm run lint,并尽可能修复不符合lint规则的地方。

运行测试#

测试套件分为两个部分:单元测试和集成测试。单元测试可以在任何计算机上运行,而集成测试则需要设置MySQL服务器实例。

运行单元测试

$ FILTER=unit npm test

运行集成测试

设置环境变量MYSQL_DATABASEMYSQL_HOSTMYSQL_PORTMYSQL_USERMYSQL_PASSWORDMYSQL_SOCKET可以代替MYSQL_HOSTMYSQL_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以外的其他编码
 类似资料: