Skip to main content

MySQL2

专注于性能的 Node.js MySQL 客户端。支持准备好的语句、非 utf8 编码、二进制日志协议、压缩、ssl 等等。

¥MySQL client for Node.js with focus on performance. Supports prepared statements, non-utf8 encodings, binary log protocol, compression, ssl much more.

安装​

¥Installation

MySQL2 不受原生绑定限制，可以毫无问题地安装在 Linux、Mac OS 或 Windows 上。

¥MySQL2 is free from native bindings and can be installed on Linux, Mac OS or Windows without any issues.

npm install --save mysql2

第一个查询​

¥First Query

要探索更多查询示例，请访问示例部分 简单查询 和 准备好的语句。

¥To explore more queries examples, please visit the example sections Simple Queries and Prepared Statements.

// Get the client
import mysql from 'mysql2/promise';

// Create the connection to database
const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test',
});

// A simple SELECT query
try {
  const [results, fields] = await connection.query(
    'SELECT * FROM `table` WHERE `name` = "Page" AND `age` > 45'
  );

  console.log(results); // results contains rows returned by server
  console.log(fields); // fields contains extra meta data about results, if available
} catch (err) {
  console.log(err);
}

// Using placeholders
try {
  const [results] = await connection.query(
    'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
    ['Page', 45]
  );

  console.log(results);
} catch (err) {
  console.log(err);
}

使用准备好的语句​

¥Using Prepared Statements

使用 MySQL2，你还可以获得准备好的语句。使用准备好的语句，MySQL 不必每次都为相同的查询准备计划，这会提高性能。如果你不知道它们为什么重要，请查看以下讨论：

¥With MySQL2 you also get the prepared statements. With prepared statements MySQL doesn't have to prepare plan for same query every time, this results in better performance. If you don't know why they are important, please check these discussions:

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.

要探索更多准备好的语句和占位符示例，请访问示例部分 准备好的语句。

¥To explore more Prepared Statements and Placeholders examples, please visit the example section Prepared Statements.

import mysql from 'mysql2/promise';

try {
  // create the connection to database
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    database: 'test',
  });

  // execute will internally call prepare and query
  const [results, fields] = await connection.execute(
    'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
    ['Rick C-137', 53]
  );

  console.log(results); // results contains rows returned by server
  console.log(fields); // fields contains extra meta data about results, if available
} catch (err) {
  console.log(err);
}
提示

如果你再次执行相同的语句，它将从 LRU 缓存中挑选出来，这将节省查询准备时间并提供更好的性能。

¥If you execute same statement again, it will be picked from a LRU cache which will save query preparation time and give better performance.

使用连接池​

¥Using Connection Pools

连接池通过重用以前的连接来帮助减少连接到 MySQL 服务器所花费的时间，当你使用完它们后，让它们保持打开状态而不是关闭。

¥Connection pools help reduce the time spent connecting to the MySQL server by reusing a previous connection, leaving them open instead of closing when you are done with them.

这可以改善查询的延迟，因为你可以避免建立新连接带来的所有开销。

¥This improves the latency of queries as you avoid all of the overhead that comes with establishing a new connection.

要探索更多连接池示例，请访问示例部分 createPool。

¥To explore more Connection Pools examples, please visit the example section createPool.

import mysql from 'mysql2/promise';

// Create the connection pool. The pool-specific settings are the defaults
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  waitForConnections: true,
  connectionLimit: 10,
  maxIdle: 10, // max idle connections, the default value is the same as `connectionLimit`
  idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
});
注意

池不会预先创建所有连接，而是根据需要创建它们，直到达到连接限制。

¥The pool does not create all connections upfront but creates them on demand until the connection limit is reached.

你可以以与连接相同的方式使用池（使用 pool.query() 和 pool.execute()）：

¥You can use the pool in the same way as connections (using pool.query() and pool.execute()):

try {
  // For pool initialization, see above
  const [rows, fields] = await pool.query('SELECT `field` FROM `table`');
  // Connection is automatically released when query resolves
} catch (err) {
  console.log(err);
}

或者，也可以手动从池中获取连接并稍后返回：

¥Alternatively, there is also the possibility of manually acquiring a connection from the pool and returning it later:

// For pool initialization, see above
const conn = await pool.getConnection();

// Do something with the connection
await conn.query(/* ... */);

// Don't forget to release the connection when finished!
pool.releaseConnection(conn);

  • 此外，直接使用 connection 对象释放连接：

    ¥Additionally, directly release the connection using the connection object:

conn.release();

使用 Promise Wrapper​

¥Using Promise Wrapper

MySQL2 还支持 Promise API。它与 ES7 async await 配合得很好。

¥MySQL2 also support Promise API. Which works very well with ES7 async await.

import mysql from 'mysql2/promise';

async function main() {
  // create the connection
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    database: 'test',
  });

  // query database
  const [rows, fields] = await connection.execute(
    'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
    ['Morty', 14]
  );
}

MySQL2 使用范围内可用的默认 Promise 对象。但你可以选择要使用哪种 Promise 实现。

¥MySQL2 use default Promise object available in scope. But you can choose which Promise implementation you want to use.

// get the client
import mysql from 'mysql2/promise';

// get the promise implementation, we will use bluebird
import bluebird from 'bluebird';

// create the connection, specify bluebird as Promise
const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test',
  Promise: bluebird,
});

// query database
const [rows, fields] = await connection.execute(
  'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
  ['Morty', 14]
);

MySQL2 还在 Pools 上公开了一个 .promise() 函数，因此你可以从同一个池中创建 promise/非 promise 连接。

¥MySQL2 also exposes a .promise() function on Pools, so you can create a promise/non-promise connections from the same pool.

import mysql from 'mysql2';

async function main() {
  // create the pool
  const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    database: 'test',
  });

  // now get a Promise wrapped instance of that pool
  const promisePool = pool.promise();

  // query database using promises
  const [rows, fields] = await promisePool.query('SELECT 1');
}

MySQL2 在 Connections 上公开了一个 .promise() 函数，将现有的非 promise 连接提供给 "upgrade" 以使用 promise。

¥MySQL2 exposes a .promise() function on Connections, to "upgrade" an existing non-promise connection to use promise.

const mysql = require('mysql2');

// create the connection
const conn = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test',
});

conn
  .promise()
  .query('SELECT 1')
  .then(([rows, fields]) => {
    console.log(rows);
  })
  .catch(console.log)
  .then(() => conn.end());

数组结果​

¥Array Results

如果你有两列同名，你可能希望以数组而不是对象的形式获取结果，以防止它们发生冲突。这是与 Node MySQL 库的偏差。

¥If you have two columns with the same name, you might want to get results as an array rather than an object to prevent them from clashing. This is a deviation from the Node MySQL library.

例如：SELECT 1 AS foo, 2 AS foo``。

¥For example: SELECT 1 AS `foo`, 2 AS `foo`.

你可以在连接级别（适用于所有查询）或查询级别（仅适用于该特定查询）启用此设置。

¥You can enable this setting at either the connection level (applies to all queries), or at the query level (applies only to that specific query).

连接级别​

¥Connection Level

const conn = await mysql.createConnection({
  host: 'localhost',
  database: 'test',
  user: 'root',
  rowsAsArray: true,
});

查询级别​

¥Query Level

try {
  const [results, fields] = await conn.query({
    sql: 'SELECT 1 AS `foo`, 2 AS `foo`',
    rowsAsArray: true,
  });

  console.log(results); // in this query, results will be an array of arrays rather than an array of objects
  console.log(fields); // fields are unchanged
} catch (err) {
  console.log(err);
}
获取帮助

需要帮助吗？在 Stack Overflow 或 GitHub 上提问。如果你遇到问题，请联系 在 GitHub 上归档。

¥Need help? Ask your question on Stack Overflow or GitHub. If you've encountered an issue, please file it on GitHub.