Skip to main content

使用 MySQL2 和 TypeScript

¥Using MySQL2 with TypeScript

安装​

¥Installation

npm install --save mysql2
npm install --save-dev @types/node

@types/node 确保 TypeScript 与 MySQL2 使用的 Node.js 模块（net、events、stream、tls 等）之间的正确交互。

¥The @types/node ensure the proper interaction between TypeScript and the Node.js modules used by MySQL2 (net, events, stream, tls, etc.).

信息

需要 TypeScript >=4.5.2。

¥Requires TypeScript >=4.5.2.

用法​

¥Usage

你可以通过两种方式导入 MySQL2：

¥You can import MySQL2 in two ways:

  • 通过在 tsconfig.json 中将 esModuleInterop 选项设置为 true

    ¥By setting the esModuleInterop option to true in tsconfig.json

import mysql from 'mysql2';
import mysql from 'mysql2/promise';

  • 通过在 tsconfig.json 中将 esModuleInterop 选项设置为 false

    ¥By setting the esModuleInterop option to false in tsconfig.json

import * as mysql from 'mysql2';
import * as mysql from 'mysql2/promise';

连接​

¥Connection

import mysql, { ConnectionOptions } from 'mysql2';

const access: ConnectionOptions = {
  user: 'test',
  database: 'test',
};

const conn = mysql.createConnection(access);

池连接​

¥Pool Connection

import mysql, { PoolOptions } from 'mysql2';

const access: PoolOptions = {
  user: 'test',
  database: 'test',
};

const conn = mysql.createPool(access);

查询和执行​

¥Query and Execute

一个简单的查询​

¥A simple query

conn.query('SELECT 1 + 1 AS `test`;', (_err, rows) => {
  /**

   * @rows: [ { test: 2 } ]
   */
});

conn.execute('SELECT 1 + 1 AS `test`;', (_err, rows) => {
  /**

   * @rows: [ { test: 2 } ]
   */
});

rows 输出将是这些可能的类型：

¥The rows output will be these possible types:

  • RowDataPacket[]

  • RowDataPacket[][]

  • ResultSetHeader

  • ResultSetHeader[]

  • ProcedureCallPacket

在此示例中，你需要手动检查输出类型

¥In this example, you need to manually check the output types

类型规范​

¥Type Specification

RowDataPacket[]​

2Stable

包含返回行的数组，例如：

¥An array with the returned rows, for example:

import mysql, { RowDataPacket } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
});

// SELECT
conn.query<RowDataPacket[]>('SELECT 1 + 1 AS `test`;', (_err, rows) => {
  console.log(rows);
  /**

   * @rows: [ { test: 2 } ]
   */
});

// SHOW
conn.query<RowDataPacket[]>('SHOW TABLES FROM `test`;', (_err, rows) => {
  console.log(rows);
  /**

   * @rows: [ { Tables_in_test: 'test' } ]
   */
});

使用 rowsAsArray 选项作为 true：

¥Using rowsAsArray option as true:

import mysql, { RowDataPacket } from 'mysql2';

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

// SELECT
conn.query<RowDataPacket[]>(
  'SELECT 1 + 1 AS test, 2 + 2 AS test;',
  (_err, rows) => {
    console.log(rows);
    /**

     * @rows: [ [ 2, 4 ] ]
     */
  }
);

// SHOW
conn.query<RowDataPacket[]>('SHOW TABLES FROM `test`;', (_err, rows) => {
  console.log(rows);
  /**

   * @rows: [ [ 'test' ] ]
   */
});

RowDataPacket[][]​

2Stable

使用 multipleStatements 选项作为 true 进行多个查询：

¥Using multipleStatements option as true with multiple queries:

import mysql, { RowDataPacket } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
  multipleStatements: true,
});

const sql = `
  SELECT 1 + 1 AS test;
  SELECT 2 + 2 AS test;
`;

conn.query<RowDataPacket[][]>(sql, (_err, rows) => {
  console.log(rows);
  /**

   * @rows: [ [ { test: 2 } ], [ { test: 4 } ] ]
   */
});

ResultSetHeader​

2Stable
History
VersionChanges
v3.5.1
OkPacket is deprecated and might be removed in the future major release.
Please use ResultSetHeader instead.
changedRows option is deprecated and might be removed in the future major release.
Please use affectedRows instead.

对于 INSERT、UPDATE、DELETE、TRUNCATE 等：

¥For INSERT, UPDATE, DELETE, TRUNCATE, etc.:

import mysql, { ResultSetHeader } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
});

const sql = `
  SET @1 = 1;
`;

conn.query<ResultSetHeader>(sql, (_err, result) => {
  console.log(result);
  /**

   * @result: ResultSetHeader {
      fieldCount: 0,
      affectedRows: 0,
      insertId: 0,
      info: '',
      serverStatus: 2,
      warningStatus: 0,
      changedRows: 0
    }
   */
});

ResultSetHeader[]​

2Stable
History
VersionChanges
v3.5.1
Introduce ResultSetHeader[]

对于使用 multipleStatements 作为 true 时的多个 INSERT、UPDATE、DELETE、TRUNCATE 等：

¥For multiples INSERT, UPDATE, DELETE, TRUNCATE, etc. when using multipleStatements as true:

import mysql, { ResultSetHeader } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
  multipleStatements: true,
});

const sql = `
  SET @1 = 1;
  SET @2 = 2;
`;

conn.query<ResultSetHeader[]>(sql, (_err, results) => {
  console.log(results);
  /**

   * @results: [
      ResultSetHeader {
        fieldCount: 0,
        affectedRows: 0,
        insertId: 0,
        info: '',
        serverStatus: 10,
        warningStatus: 0,
        changedRows: 0
      },
      ResultSetHeader {
        fieldCount: 0,
        affectedRows: 0,
        insertId: 0,
        info: '',
        serverStatus: 2,
        warningStatus: 0,
        changedRows: 0
      }
    ]
   */
});

ProcedureCallPacket​

2Stable
History
VersionChanges
v3.5.1
Introduce ProcedureCallPacket
提示

通过使用 INSERT、UPDATE 等执行调用过程，返回结果将是 ProcedureCallPacket<ResultSetHeader>（即使你执行多个查询并将 multipleStatements 设置为 true）：

¥By performing a Call Procedure using INSERT, UPDATE, etc., the return will be a ProcedureCallPacket<ResultSetHeader> (even if you perform multiples queries and set multipleStatements to true):

import mysql, { ProcedureCallPacket, ResultSetHeader } from 'mysql2';

const conn = mysql.createConnection({
  user: 'test',
  database: 'test',
});

/** ResultSetHeader */
conn.query('DROP PROCEDURE IF EXISTS myProcedure');

/** ResultSetHeader */
conn.query(`
    CREATE PROCEDURE myProcedure()
    BEGIN
      SET @1 = 1;
      SET @2 = 2;
    END
  `);

/** ProcedureCallPacket */
const sql = 'CALL myProcedure()';

conn.query<ProcedureCallPacket<ResultSetHeader>>(sql, (_err, result) => {
  console.log(result);
  /**

   * @result: ResultSetHeader {
      fieldCount: 0,
      affectedRows: 0,
      insertId: 0,
      info: '',
      serverStatus: 2,
      warningStatus: 0,
      changedRows: 0
    }
   */
});

对于 CREATE PROCEDURE 和 DROP PROCEDURE，这些返回将是默认的 ResultSetHeader。

¥For CREATE PROCEDURE and DROP PROCEDURE, these returns will be the default ResultSetHeader.

通过在过程调用中使用 SELECT 和 SHOW 查询，它将结果分组为：

¥By using SELECT and SHOW queries in a Procedure Call, it groups the results as:

/** ProcedureCallPacket<RowDataPacket[]> */
[RowDataPacket[], ResultSetHeader]

对于 ProcedureCallPacket<RowDataPacket[]>，请参阅以下示例。

¥For ProcedureCallPacket<RowDataPacket[]>, please see the following examples.

OkPacket​

0Deprecated

OkPacket is deprecated and might be removed in the future major release.
Please use ResultSetHeader instead.

示例​

¥Examples

你还可以查看一些使用 MySQL2 和 TypeScript 的代码示例，以了解高级概念：

¥You can also check some code examples using MySQL2 and TypeScript to understand advanced concepts: