Skip to main content

额外功能

¥Extra Features

命名占位符​

¥Named placeholders

你可以通过设置 namedPlaceholders 配置值或查询/执行时间选项将命名占位符用于参数。命名占位符在客户端上转换为未命名的 ?（mysql 协议不支持命名参数）。如果你以相同的名称多次引用参数，它将被多次发送到服务器。仍可通过将值作为数组而不是对象提供来使用未命名的占位符。

¥You can use named placeholders for parameters by setting namedPlaceholders config value or query/execute time option. Named placeholders are converted to unnamed ? on the client (mysql protocol does not support named parameters). If you reference parameter multiple times under the same name it is sent to server multiple times. Unnamed placeholders can still be used by providing the values as an array instead of an object.

connection.config.namedPlaceholders = true;
connection.execute('select :x + :y as z', { x: 1, y: 2 }, (err, rows) => {
  // statement prepared as "select ? + ? as z" and executed with [1,2] values
  // rows returned: [ { z: 3 } ]
});

connection.execute('select :x + :x as z', { x: 1 }, (err, rows) => {
  // select ? + ? as z, execute with [1, 1]
});

connection.query('select :x + :x as z', { x: 1 }, (err, rows) => {
  // query select 1 + 1 as z
});

// unnamed placeholders are still valid if the values are provided in an array
connection.query('select ? + ? as z', [1, 1], (err, rows) => {
  // query select 1 + 1 as z
});

以列数组的形式接收行，而不是以列名作为键的哈希：​

¥Receiving rows as array of columns instead of hash with column name as key:

const options = { sql: 'select A,B,C,D from foo', rowsAsArray: true };
connection.query(options, (err, results) => {
  /* results will be an array of arrays like this now:
  [[
     'field A value',
     'field B value',
     'field C value',
     'field D value',
  ], ...]
  */
});

使用 '加载文件' 和本地流发送表格数据：​

¥Sending tabular data with 'load infile' and local stream:

除了发送本地 fs 文件之外，你还可以使用 infileStreamFactory 查询选项发送任何流。如果设置，它必须是一个返回可读流的函数。它从查询中获取文件路径作为参数。

¥In addition to sending local fs files you can send any stream using infileStreamFactory query option. If set, it has to be a function that return a readable stream. It gets file path from query as a parameter.

注意：从 2.0 版开始，infileStreamFactory 是 LOAD DATA LOCAL INFILE 的必需参数。服务器的响应表明它想要访问本地文件，并且没有提供 infileStreamFactory 选项，查询以错误结束。

¥Note: starting from version 2.0 infileStreamFactory is required parameter for LOAD DATA LOCAL INFILE. Response from server indicates that it wants access to a local file and no infileStreamFactory option is provided the query ends with error.

// local file
connection.query(
  'LOAD DATA LOCAL INFILE "/tmp/data.csv" INTO TABLE test FIELDS TERMINATED BY ? (id, title)',
  onInserted1
);
// local stream
const sql =
  'LOAD DATA LOCAL INFILE "mystream" INTO TABLE test FIELDS TERMINATED BY ? (id, title)';
connection.query(
  {
    sql: sql,
    infileStreamFactory: function (path) {
      return getStream();
    },
  },
  onInserted2
);

infileStreamFactory 选项也可以在连接级别设置：

¥The infileStreamFactory option may also be set at a connection-level:

const fs = require('fs');
const mysql = require('mysql2');

const connection = mysql.createConnection({
  user: 'test',
  database: 'test',
  infileStreamFactory: (path) => {
    // Validate file path
    const validPaths = ['/tmp/data.csv'];
    if (!validPaths.includes(path)) {
      throw new Error(
        `invalid file path: ${path}: expected to be one of ${validPaths.join(
          ','
        )}`
      );
    }
    return fs.createReadStream(path);
  },
});

connection.query(
  'LOAD DATA LOCAL INFILE "/tmp/data.csv" INTO TABLE test',
  onInserted
);

使用自定义流连接：​

¥Connecting using custom stream:

const net = require('net');
const mysql = require('mysql2');
const shape = require('shaper');
const connection = mysql.createConnection({
  user: 'test',
  database: 'test',
  stream: net.connect('/tmp/mysql.sock').pipe(shape(10)), // emulate 10 bytes/sec link
});
connection.query('SELECT 1+1 as test1', console.log);

stream 也可以是一个函数。在这种情况下，函数结果必须是双工流，并且它用于连接传输。如果你使用自定义传输连接池，则需要这样做，因为新的池连接需要新的流。示例 通过 socks5 代理连接：

¥stream also can be a function. In that case function result has to be duplex stream, and it is used for connection transport. This is required if you connect pool using custom transport as new pooled connection needs new stream. Example connecting over socks5 proxy:

const mysql = require('mysql2');
const SocksConnection = require('socksjs');
const pool = mysql.createPool({
  database: 'test',
  user: 'foo',
  password: 'bar',
  stream: function (cb) {
    const newStream = new SocksConnection(
      { host: 'remote.host', port: 3306 },
      { host: 'localhost', port: 1080 }
    );
    cb(null, newStream);
  },
});

除了密码 createConnection()、createPool() 和 changeUser() 之外，还接受 passwordSha1 选项。这在实现代理时很有用，因为纯文本密码可能不可用。

¥In addition to password createConnection(), createPool() and changeUser() accept passwordSha1 option. This is useful when implementing proxies as plaintext password might be not available.