准备好的语句

¥Prepared Statements

自动创建、缓存并通过连接重新使用

¥Automatic creation, cached and re-used by connection

与 connection.query() 类似。

¥Similar to connection.query().

connection.execute('select 1 + ? + ? as result', [5, 6], (err, rows) => {
  // rows: [ { result: 12 } ]
  // internally 'select 1 + ? + ? as result' is prepared first. On subsequent calls cached statement is re-used
});

// close cached statement for 'select 1 + ? + ? as result'. noop if not in cache
connection.unprepare('select 1 + ? + ? as result');

请注意，connection.execute() 将缓存准备好的语句以获得更好的性能，完成后使用 connection.unprepare() 删除缓存。

¥Note that connection.execute() will cache the prepared statement for better performance, remove the cache with connection.unprepare() when you're done.

手动准备/执行

¥Manual prepare / execute

手动准备的语句不附带 LRU 缓存，应该使用 statement.close() 而不是 connection.unprepare() 关闭。

¥Manually prepared statements doesn't comes with LRU cache and SHOULD be closed using statement.close() instead of connection.unprepare().

connection.prepare('select ? + ? as tests', (err, statement) => {
  // statement.parameters - array of column definitions, length === number of params, here 2
  // statement.columns - array of result column definitions. Can be empty if result schema is dynamic / not known
  // statement.id
  // statement.query

  statement.execute([1, 2], (err, rows, columns) => {
    // -> [ { tests: 3 } ]
  });

  // don't use connection.unprepare(), it won't work!
  // note that there is no callback here. There is no statement close ack at protocol level.
  statement.close();
});

请注意，在连接重置（changeUser() 或断开连接）后，不应使用语句。语句范围是连接，你需要为每个新连接准备语句才能使用它。

¥Note that you should not use statement after connection reset (changeUser() or disconnect). Statement scope is connection, you need to prepare statement for each new connection in order to use it.

配置

¥Configuration

maxPreparedStatements：我们将缓存的语句保存在 lru-cache 中。默认大小为 16000，但你可以使用此选项覆盖它。从缓存中删除的任何语句都将是 closed。

¥maxPreparedStatements : We keep the cached statements in a lru-cache. Default size is 16000 but you can use this option to override it. Any statements that are dropped from cache will be closed.

绑定参数的序列化

¥Serialization of bind parameters

传递给 execute 的绑定参数值被序列化为 JS -> MySQL：

¥The bind parameter values passed to execute are serialized JS -> MySQL as:

• null -> NULL

• number -> DOUBLE

• boolean -> TINY（0 表示假，1 表示真）

  ¥boolean -> TINY (0 for false, 1 for true)

• object -> 取决于原型：

  ¥object -> depending on prototype:

  • Date -> DATETIME

  • JSON 类对象 - JSON

    ¥JSON like object - JSON

  • Buffer -> VAR_STRING

• 其他 -> VAR_STRING

  ¥Other -> VAR_STRING

传入 undefined 或 function 将导致错误。

¥Passing in undefined or a function will result in an error.

准备好的语句助手

¥Prepared Statements Helper

MySQL2 提供 execute 助手，它将准备和查询语句。你还可以使用 prepare/unprepare 方法手动准备/取消准备语句。

¥MySQL2 provides execute helper which will prepare and query the statement. You can also manually prepare / unprepare statement with prepare / unprepare methods.

connection.execute(
  'select ?+1 as qqq, ? as rrr, ? as yyy',
  [1, null, 3],
  (err, rows, fields) => {
    console.log(err, rows, fields);
    connection.execute(
      'select ?+1 as qqq, ? as rrr, ? as yyy',
      [3, null, 3],
      (err, rows, fields) => {
        console.log(err, rows, fields);
        connection.unprepare('select ?+1 as qqq, ? as rrr, ? as yyy');
        connection.execute(
          'select ?+1 as qqq, ? as rrr, ? as yyy',
          [3, null, 3],
          (err, rows, fields) => {
            console.log(err, rows, fields);
          }
        );
      }
    );
  }
);

示例

¥Examples

有关 Prepared Statements 示例，请参阅 此处。

¥For Prepared Statements examples, please see here.