Back to Details

api-database-mysql

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

MySQL Patterns (mysql2)

MySQL 模式（mysql2）

Quick Guide: Use mysql2/promise for all new code -- it provides async/await support over the mysql2 callback API. Always use 
createPool()
(never 
createConnection()
in production) with 
execute()
for parameterized queries (prepared statements, LRU-cached). Type query results with 
RowDataPacket
generics for SELECTs and 
ResultSetHeader
for INSERT/UPDATE/DELETE. For transactions, acquire a dedicated connection with 
pool.getConnection()
, wrap in try/finally to guarantee 
connection.release()
. Never interpolate user input into SQL strings -- always use 
?
placeholders. Handle 
ER_DUP_ENTRY
and 
ER_LOCK_DEADLOCK
explicitly in catch blocks.
<critical_requirements>
快速指南： 所有新代码请使用 mysql2/promise——它在mysql2回调API之上提供了async/await支持。生产环境中始终使用 
createPool()
（绝不要使用
createConnection()
），配合
execute()
执行参数化查询（预编译语句，LRU缓存）。SELECT查询使用
RowDataPacket
泛型为结果添加类型，INSERT/UPDATE/DELETE使用
ResultSetHeader
。处理事务时，通过
pool.getConnection()
获取专用连接，用try/finally包裹以确保
connection.release()
。绝不要将用户输入直接插入SQL字符串——始终使用
?
占位符。在catch块中显式处理
ER_DUP_ENTRY
和
ER_LOCK_DEADLOCK
错误。
<critical_requirements>

CRITICAL: Before Using This Skill

重要提示：使用本技能前须知

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, 
import type
, named constants)
(You MUST use 
execute()
with 
?
placeholders for ALL queries containing user input -- NEVER interpolate values into SQL strings with template literals or string concatenation)
(You MUST use 
pool.getConnection()
for transactions and release the connection in a 
finally
block -- pool convenience methods (
pool.execute()
) use a different connection per call and cannot maintain transaction state)
(You MUST always import from 
mysql2/promise
for async/await code -- the base 
mysql2
module returns callback-based objects that do not support 
await
)
(You MUST handle the pool 
error
event -- unhandled connection errors crash the Node.js process)
</critical_requirements>
所有代码必须遵循CLAUDE.md中的项目约定（短横线命名、命名导出、导入顺序、
import type
、命名常量）
（所有包含用户输入的查询必须使用
execute()
和
?
占位符——绝不要用模板字符串或字符串拼接将值插入SQL字符串）
（事务必须使用
pool.getConnection()
获取连接，并在
finally
块中释放连接——池便捷方法（
pool.execute()
）每次调用使用不同连接，无法维护事务状态）
（异步代码必须始终从
mysql2/promise
导入——基础
mysql2
模块返回基于回调的对象，不支持
await
）
（必须处理池的
error
事件——未处理的连接错误会导致Node.js进程崩溃）
</critical_requirements>

Examples

示例

  • Core Patterns -- Pool setup, typed queries, prepared statements, connection lifecycle
  • Transactions -- Manual transactions, savepoints, deadlock retry, nested operations
  • Streaming & Batch -- Streaming large result sets, batch inserts, multiple statements
  • Error Handling -- MySQL error codes, connection errors, retry strategies, graceful degradation
  • Configuration -- SSL/TLS, named placeholders, pool tuning, monitoring events
Additional resources:
  • reference.md -- Type cheat sheet, pool options, error codes, production checklist
Auto-detection: MySQL, mysql2, mysql2/promise, createPool, createConnection, RowDataPacket, ResultSetHeader, execute, prepared statement, pool.getConnection, beginTransaction, commit, rollback, ER_DUP_ENTRY, ER_LOCK_DEADLOCK, connectionLimit, SHOW TABLES, mysqldump, InnoDB, MariaDB
When to use:
  • Direct SQL queries against MySQL or MariaDB databases
  • Connection pool management for server applications
  • Transactions requiring atomicity across multiple queries
  • Streaming large result sets without loading all rows into memory
  • Typed query results with TypeScript generics
  • Batch inserts or multi-statement operations
Key patterns covered:
  • Pool creation with 
    mysql2/promise
    and proper configuration
  • Prepared statements via 
    execute()
    with 
    ?
    placeholders
  • TypeScript generics with 
    RowDataPacket
    and 
    ResultSetHeader
  • Transaction lifecycle: 
    getConnection
    -> 
    beginTransaction
    -> 
    commit
    /
    rollback
    -> 
    release
  • Streaming with 
    connection.query().stream()
    on the non-promise API
  • Error handling for 
    ER_DUP_ENTRY
    , 
    ER_LOCK_DEADLOCK
    , connection failures
  • Pool events (
    acquire
    , 
    release
    , 
    enqueue
    ) for monitoring
  • SSL/TLS and named placeholders configuration
When NOT to use:
  • When your project already uses an ORM or query builder for MySQL -- use that tool's skill instead
  • For in-memory caching or key-value storage (use a dedicated caching solution)
  • For document databases or graph queries (wrong database type)
  • For one-off CLI scripts where a single connection suffices and pool overhead is unnecessary
<philosophy>
  • 核心模式 —— 池配置、类型化查询、预编译语句、连接生命周期
  • 事务 —— 手动事务、保存点、死锁重试、嵌套操作
  • 流式处理与批量操作 —— 流式处理大型结果集、批量插入、多语句执行
  • 错误处理 —— MySQL错误码、连接错误、重试策略、优雅降级
  • 配置 —— SSL/TLS、命名占位符、池调优、监控事件
额外资源：
  • reference.md —— 类型速查表、池选项、错误码、生产环境检查清单
自动检测关键词： MySQL, mysql2, mysql2/promise, createPool, createConnection, RowDataPacket, ResultSetHeader, execute, prepared statement, pool.getConnection, beginTransaction, commit, rollback, ER_DUP_ENTRY, ER_LOCK_DEADLOCK, connectionLimit, SHOW TABLES, mysqldump, InnoDB, MariaDB
适用场景：
  • 直接对MySQL或MariaDB数据库执行SQL查询
  • 服务器应用的连接池管理
  • 需要跨多个查询保证原子性的事务
  • 流式处理大型结果集，无需将所有行加载到内存
  • 使用TypeScript泛型实现类型化查询结果
  • 批量插入或多语句操作
涵盖的关键模式：
  • 使用
    mysql2/promise
    创建连接池并进行合理配置
  • 通过
    execute()
    和
    ?
    占位符实现预编译语句
  • 结合TypeScript泛型与
    RowDataPacket
    、
    ResultSetHeader
  • 事务生命周期：
    getConnection
    -> 
    beginTransaction
    -> 
    commit
    /
    rollback
    -> 
    release
  • 在非Promise API上使用
    connection.query().stream()
    实现流式处理
  • 处理
    ER_DUP_ENTRY
    、
    ER_LOCK_DEADLOCK
    、连接失败等错误
  • 池事件（
    acquire
    、
    release
    、
    enqueue
    ）监控
  • SSL/TLS和命名占位符配置
不适用场景：
  • 项目已使用ORM或查询构建器操作MySQL——请使用对应工具的技能
  • 内存缓存或键值存储场景（使用专用缓存解决方案）
  • 文档数据库或图查询场景（数据库类型不匹配）
  • 一次性CLI脚本，单连接足够且池开销不必要的场景
<philosophy>

Philosophy

设计理念

mysql2 is a low-level MySQL driver -- it sends SQL to MySQL and returns typed results. It does not generate SQL, manage migrations, or handle schema changes.
Core principles:
  1. Pools, not connections -- Production applications should always use 
    createPool()
    . Pools manage connection lifecycle, handle reconnection, and prevent connection exhaustion. 
    createConnection()
    is only appropriate for one-off scripts.
  2. Prepared statements always -- 
    execute()
    sends parameterized queries to MySQL's prepared statement protocol. The driver caches prepared statements in an LRU cache, so repeated queries skip the preparation step. Never use 
    query()
    with string interpolation.
  3. Type your results -- MySQL2's TypeScript generics (
    RowDataPacket
    , 
    ResultSetHeader
    ) eliminate 
    any
    from query results. Define interfaces extending 
    RowDataPacket
    for each table shape.
  4. Transactions need dedicated connections -- Pool convenience methods (
    pool.execute()
    , 
    pool.query()
    ) may use different connections for each call. Transactions require 
    pool.getConnection()
    to pin a single connection, with 
    connection.release()
    in a 
    finally
    block.
  5. Fail explicitly -- MySQL errors carry structured 
    code
    fields (
    ER_DUP_ENTRY
    , 
    ER_LOCK_DEADLOCK
    ). Check 
    error.code
    in catch blocks rather than parsing message strings.
</philosophy>
<patterns>
mysql2是一个底层MySQL驱动——它将SQL发送到MySQL并返回类型化结果。它不生成SQL、不管理迁移、不处理模式变更。
核心原则：
  1. 使用连接池，而非单连接——生产应用应始终使用
    createPool()
    。连接池管理连接生命周期、处理重连、防止连接耗尽。
    createConnection()
    仅适用于一次性脚本。
  2. 始终使用预编译语句——
    execute()
    将参数化查询发送到MySQL的预编译语句协议。驱动在LRU缓存中缓存预编译语句，重复查询可跳过准备步骤。绝不要使用
    query()
    进行字符串插值。
  3. 为结果添加类型——MySQL2的TypeScript泛型（
    RowDataPacket
    、
    ResultSetHeader
    ）消除查询结果中的
    any
    类型。为每个表结构定义继承
    RowDataPacket
    的接口。
  4. 事务需要专用连接——池便捷方法（
    pool.execute()
    、
    pool.query()
    ）每次调用可能使用不同连接。事务需要
    pool.getConnection()
    固定单个连接，并在
    finally
    块中调用
    connection.release()
    。
  5. 显式处理失败——MySQL错误包含结构化的
    code
    字段（
    ER_DUP_ENTRY
    、
    ER_LOCK_DEADLOCK
    ）。在catch块中检查
    error.code
    ，而非解析错误消息字符串。
</philosophy>
<patterns>

Core Patterns

核心模式

Pattern 1: Pool Setup with mysql2/promise

模式1：使用mysql2/promise配置连接池

Create a connection pool with environment-based configuration and error handling. See examples/core.md for the complete setup pattern.
typescript
// Good Example - Production pool setup
import mysql from "mysql2/promise";
import type { Pool } from "mysql2/promise";

const DEFAULT_CONNECTION_LIMIT = 10;
const DEFAULT_IDLE_TIMEOUT_MS = 60_000;

function createDatabasePool(): Pool {
  const url = process.env.DATABASE_URL;
  if (!url) {
    throw new Error("DATABASE_URL environment variable is required");
  }

  return mysql.createPool({
    uri: url,
    waitForConnections: true,
    connectionLimit: DEFAULT_CONNECTION_LIMIT,
    maxIdle: DEFAULT_CONNECTION_LIMIT,
    idleTimeout: DEFAULT_IDLE_TIMEOUT_MS,
    enableKeepAlive: true,
    keepAliveInitialDelay: 0,
  });
}

export { createDatabasePool };
Why good: Environment variable validation, named constants for limits, 
waitForConnections: true
queues requests instead of throwing, 
enableKeepAlive
prevents stale connections
typescript
// Bad Example - Hardcoded single connection
import mysql from "mysql2/promise";

const connection = await mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password123",
  database: "mydb",
});
// Hardcoded credentials, single connection exhausts under load, no pool
Why bad: Hardcoded credentials leak in version control, single connection cannot handle concurrent requests, no automatic reconnection
基于环境变量配置创建连接池并处理错误。完整配置模式请查看examples/core.md。
typescript
// Good Example - Production pool setup
import mysql from "mysql2/promise";
import type { Pool } from "mysql2/promise";

const DEFAULT_CONNECTION_LIMIT = 10;
const DEFAULT_IDLE_TIMEOUT_MS = 60_000;

function createDatabasePool(): Pool {
  const url = process.env.DATABASE_URL;
  if (!url) {
    throw new Error("DATABASE_URL environment variable is required");
  }

  return mysql.createPool({
    uri: url,
    waitForConnections: true,
    connectionLimit: DEFAULT_CONNECTION_LIMIT,
    maxIdle: DEFAULT_CONNECTION_LIMIT,
    idleTimeout: DEFAULT_IDLE_TIMEOUT_MS,
    enableKeepAlive: true,
    keepAliveInitialDelay: 0,
  });
}

export { createDatabasePool };
优点： 环境变量验证、使用命名常量设置限制、
waitForConnections: true
会排队请求而非抛出错误、
enableKeepAlive
防止连接过期
typescript
// Bad Example - Hardcoded single connection
import mysql from "mysql2/promise";

const connection = await mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password123",
  database: "mydb",
});
// Hardcoded credentials, single connection exhausts under load, no pool
缺点： 硬编码凭证会在版本控制中泄露、单连接无法处理并发请求、无自动重连机制

Pattern 2: Typed Queries with Generics

模式2：使用泛型实现类型化查询

Use 
RowDataPacket
for SELECTs and 
ResultSetHeader
for mutations. See examples/core.md for all type patterns.
typescript
// Good Example - Typed SELECT and INSERT
import type { Pool, RowDataPacket, ResultSetHeader } from "mysql2/promise";

interface UserRow extends RowDataPacket {
  id: number;
  email: string;
  name: string;
  created_at: Date;
}

async function getUserById(
  pool: Pool,
  userId: number,
): Promise<UserRow | null> {
  const [rows] = await pool.execute<UserRow[]>(
    "SELECT id, email, name, created_at FROM users WHERE id = ?",
    [userId],
  );
  return rows[0] ?? null;
}

async function createUser(
  pool: Pool,
  email: string,
  name: string,
): Promise<number> {
  const [result] = await pool.execute<ResultSetHeader>(
    "INSERT INTO users (email, name) VALUES (?, ?)",
    [email, name],
  );
  return result.insertId;
}
Why good: Interface extends 
RowDataPacket
for type safety, 
execute()
uses prepared statements, destructured 
[rows]
skips field metadata, null check for missing rows
typescript
// Bad Example - Untyped query with interpolation
const [rows] = await pool.query(`SELECT * FROM users WHERE id = ${userId}`);
// SQL injection vulnerability, untyped results, query() skips prepared statements
Why bad: SQL injection via string interpolation, 
any
-typed results, 
query()
does not use prepared statement protocol
SELECT查询使用
RowDataPacket
，变更操作使用
ResultSetHeader
。所有类型模式请查看examples/core.md。
typescript
// Good Example - Typed SELECT and INSERT
import type { Pool, RowDataPacket, ResultSetHeader } from "mysql2/promise";

interface UserRow extends RowDataPacket {
  id: number;
  email: string;
  name: string;
  created_at: Date;
}

async function getUserById(
  pool: Pool,
  userId: number,
): Promise<UserRow | null> {
  const [rows] = await pool.execute<UserRow[]>(
    "SELECT id, email, name, created_at FROM users WHERE id = ?",
    [userId],
  );
  return rows[0] ?? null;
}

async function createUser(
  pool: Pool,
  email: string,
  name: string,
): Promise<number> {
  const [result] = await pool.execute<ResultSetHeader>(
    "INSERT INTO users (email, name) VALUES (?, ?)",
    [email, name],
  );
  return result.insertId;
}
优点： 接口继承
RowDataPacket
保证类型安全、
execute()
使用预编译语句、解构
[rows]
跳过字段元数据、对缺失行进行空值检查
typescript
// Bad Example - Untyped query with interpolation
const [rows] = await pool.query(`SELECT * FROM users WHERE id = ${userId}`);
// SQL injection vulnerability, untyped results, query() skips prepared statements
缺点： 字符串插值存在SQL注入风险、结果无类型、
query()
不使用预编译语句协议

Pattern 3: Transaction with Dedicated Connection

模式3：使用专用连接处理事务

Transactions require a single connection from the pool. See examples/transactions.md for savepoints, deadlock retry, and nested operations.
typescript
// Good Example - Transfer with transaction
async function transferFunds(
  pool: Pool,
  fromId: number,
  toId: number,
  amount: number,
): Promise<void> {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    await connection.execute(
      "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?",
      [amount, fromId, amount],
    );
    await connection.execute(
      "UPDATE accounts SET balance = balance + ? WHERE id = ?",
      [amount, toId],
    );

    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}
Why good: 
getConnection()
pins one connection, 
finally
guarantees release even on error, 
rollback()
in catch prevents partial commits, balance check in SQL prevents overdraft
事务需要从池中获取单个连接。保存点、死锁重试和嵌套操作请查看examples/transactions.md。
typescript
// Good Example - Transfer with transaction
async function transferFunds(
  pool: Pool,
  fromId: number,
  toId: number,
  amount: number,
): Promise<void> {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    await connection.execute(
      "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?",
      [amount, fromId, amount],
    );
    await connection.execute(
      "UPDATE accounts SET balance = balance + ? WHERE id = ?",
      [amount, toId],
    );

    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}
优点： 
getConnection()
固定单个连接、
finally
块保证即使出错也会释放连接、catch块中的
rollback()
防止部分提交、SQL中的余额检查防止透支

Pattern 4: Streaming Large Result Sets

模式4：流式处理大型结果集

Use the callback-based API for streaming -- the promise API does not support 
.stream()
. See examples/streaming.md for backpressure handling and transform streams.
typescript
// Good Example - Stream rows without loading all into memory
import mysql from "mysql2";

function streamUsers(
  pool: ReturnType<typeof mysql.createPool>,
): NodeJS.ReadableStream {
  return pool
    .query("SELECT * FROM users WHERE active = 1")
    .stream({ highWaterMark: 100 });
}
Why good: 
highWaterMark
controls buffer size, rows are emitted one at a time via Node.js stream interface, constant memory usage regardless of result set size
When to use: Result sets with 10K+ rows, ETL pipelines, CSV exports, data migrations
使用基于回调的API实现流式处理——Promise API不支持
.stream()
。背压处理和转换流请查看examples/streaming.md。
typescript
// Good Example - Stream rows without loading all into memory
import mysql from "mysql2";

function streamUsers(
  pool: ReturnType<typeof mysql.createPool>,
): NodeJS.ReadableStream {
  return pool
    .query("SELECT * FROM users WHERE active = 1")
    .stream({ highWaterMark: 100 });
}
优点： 
highWaterMark
控制缓冲区大小、通过Node.js流接口逐行输出、无论结果集大小内存使用保持恒定
适用场景： 结果集包含10K+行、ETL管道、CSV导出、数据迁移

Pattern 5: Error Code Handling

模式5：错误码处理

MySQL errors carry structured 
code
and 
errno
fields. See examples/error-handling.md for retry strategies and connection error handling.
typescript
// Good Example - Structured error handling
import type { Pool, ResultSetHeader } from "mysql2/promise";

const MYSQL_ER_DUP_ENTRY = "ER_DUP_ENTRY";

interface MysqlError extends Error {
  code: string;
  errno: number;
  sqlState: string;
  sqlMessage: string;
}

function isMysqlError(error: unknown): error is MysqlError {
  return error instanceof Error && "code" in error && "errno" in error;
}

async function createUserSafe(
  pool: Pool,
  email: string,
  name: string,
): Promise<{ insertId: number } | { duplicate: true }> {
  try {
    const [result] = await pool.execute<ResultSetHeader>(
      "INSERT INTO users (email, name) VALUES (?, ?)",
      [email, name],
    );
    return { insertId: result.insertId };
  } catch (error) {
    if (isMysqlError(error) && error.code === MYSQL_ER_DUP_ENTRY) {
      return { duplicate: true };
    }
    throw error;
  }
}
Why good: Type guard for MySQL errors, named constant for error code, returns discriminated union instead of throwing on expected errors, re-throws unexpected errors
</patterns>
<performance>
MySQL错误包含结构化的
code
和
errno
字段。重试策略和连接错误处理请查看examples/error-handling.md。
typescript
// Good Example - Structured error handling
import type { Pool, ResultSetHeader } from "mysql2/promise";

const MYSQL_ER_DUP_ENTRY = "ER_DUP_ENTRY";

interface MysqlError extends Error {
  code: string;
  errno: number;
  sqlState: string;
  sqlMessage: string;
}

function isMysqlError(error: unknown): error is MysqlError {
  return error instanceof Error && "code" in error && "errno" in error;
}

async function createUserSafe(
  pool: Pool,
  email: string,
  name: string,
): Promise<{ insertId: number } | { duplicate: true }> {
  try {
    const [result] = await pool.execute<ResultSetHeader>(
      "INSERT INTO users (email, name) VALUES (?, ?)",
      [email, name],
    );
    return { insertId: result.insertId };
  } catch (error) {
    if (isMysqlError(error) && error.code === MYSQL_ER_DUP_ENTRY) {
      return { duplicate: true };
    }
    throw error;
  }
}
优点： MySQL错误类型守卫、错误码使用命名常量、返回区分联合类型而非在预期错误时抛出、重新抛出非预期错误
</patterns>
<performance>

Performance Optimization

性能优化

execute() vs query()

execute() vs query()

execute()
uses MySQL's binary prepared statement protocol with an LRU cache. The first call prepares the statement; subsequent calls with the same SQL reuse the cached preparation, skipping the parse step. Use 
execute()
for all parameterized queries.
query()
sends the full SQL text each time. Use 
query()
only for dynamic SQL where the statement text itself changes (e.g., dynamic column lists), or when streaming (
.stream()
is not available on the promise API's execute).
execute()
使用MySQL的二进制预编译语句协议并带有LRU缓存。首次调用会准备语句；后续相同SQL的调用会复用缓存的准备结果，跳过解析步骤。所有参数化查询请使用
execute()
。
query()
每次发送完整SQL文本。仅当SQL语句本身动态变化（如动态列列表）或需要流式处理（Promise API的execute不支持
.stream()
）时使用
query()
。

Pool Sizing

连接池大小

connectionLimit = (number of CPU cores * 2) + number of disk spindles
For cloud databases, start with 
connectionLimit: 10
and increase under load testing. The MySQL server's 
max_connections
must accommodate all application instances' pools combined.
connectionLimit = (CPU核心数 * 2) + 磁盘主轴数
对于云数据库，初始设置
connectionLimit: 10
，并在负载测试中调整。MySQL服务器的
max_connections
必须能容纳所有应用实例连接池的总和。

enableKeepAlive

enableKeepAlive

Set 
enableKeepAlive: true
to prevent firewalls and load balancers from dropping idle connections. Without this, connections that idle for longer than the intermediary's timeout are silently dropped, causing 
ECONNRESET
errors on the next query.
设置
enableKeepAlive: true
以防止防火墙和负载均衡器断开空闲连接。如果不设置，空闲时间超过中间件超时的连接会被静默断开，导致下一次查询出现
ECONNRESET
错误。

Batch Inserts

批量插入

For inserting many rows, use a single 
INSERT ... VALUES (...), (...), (...)
statement instead of individual inserts -- see examples/streaming.md.
</performance>
<decision_framework>
插入大量行时，使用单个
INSERT ... VALUES (...), (...), (...)
语句而非单独插入——请查看examples/streaming.md。
</performance>
<decision_framework>

Decision Framework

决策框架

Pool vs Connection

连接池 vs 单连接

What am I building?
-- Production server handling concurrent requests? -> createPool()
-- One-off CLI script or migration? -> createConnection() is acceptable
-- Serverless function (Lambda, Vercel)? -> createPool() with connectionLimit: 1
我正在构建什么？
-- 处理并发请求的生产服务器？ -> createPool()
-- 一次性CLI脚本或迁移？ -> createConnection()是可接受的
-- 无服务器函数（Lambda、Vercel）？ -> createPool()并设置connectionLimit: 1

execute() vs query()

execute() vs query()

Does the SQL have user-provided parameters?
-- YES -> execute() with ? placeholders (ALWAYS)
-- NO, but same SQL runs repeatedly? -> execute() (benefits from LRU cache)
-- NO, SQL text itself is dynamic? -> query() (cannot prepare dynamic SQL)
-- Need to stream results? -> query().stream() on the callback API
SQL是否包含用户提供的参数？
-- 是 -> 使用execute()和?占位符（必须）
-- 否，但相同SQL会重复执行？ -> execute()（受益于LRU缓存）
-- 否，SQL文本本身是动态的？ -> query()（无法预编译动态SQL）
-- 需要流式处理结果？ -> 在回调API上使用query().stream()

Pool method vs getConnection()

池方法 vs getConnection()

Is this a single query?
-- YES -> pool.execute() or pool.query() (auto-acquires and releases)
-- NO, multiple queries needing same connection? -> pool.getConnection()
-- Transaction? -> pool.getConnection() (REQUIRED)
是否是单个查询？
-- 是 -> pool.execute()或pool.query()（自动获取和释放连接）
-- 否，多个查询需要同一连接？ -> pool.getConnection()
-- 是否是事务？ -> pool.getConnection()（必须）

Error Handling Strategy

错误处理策略

What MySQL error did I get?
-- ER_DUP_ENTRY (1062) -> Handle as business logic (return conflict, not throw)
-- ER_LOCK_DEADLOCK (1213) -> Retry the entire transaction (MySQL rolled it back)
-- ER_LOCK_WAIT_TIMEOUT (1205) -> Retry or fail with timeout message
-- ECONNREFUSED / PROTOCOL_CONNECTION_LOST -> Connection issue, pool will reconnect
-- ER_ACCESS_DENIED_ERROR (1045) -> Configuration error, fail fast
</decision_framework>
<red_flags>
遇到了什么MySQL错误？
-- ER_DUP_ENTRY (1062) -> 作为业务逻辑处理（返回冲突，不抛出）
-- ER_LOCK_DEADLOCK (1213) -> 重试整个事务（MySQL已回滚）
-- ER_LOCK_WAIT_TIMEOUT (1205) -> 重试或返回超时错误
-- ECONNREFUSED / PROTOCOL_CONNECTION_LOST -> 连接问题，池会自动重连
-- ER_ACCESS_DENIED_ERROR (1045) -> 配置错误，快速失败
</decision_framework>
<red_flags>

RED FLAGS

警示信号

High Priority Issues:
  • Interpolating user input into SQL strings (
    \
    SELECT * FROM users WHERE id = ${id}`
    ) -- SQL injection vulnerability, always use
    ?
    placeholders with
    execute()`
  • Using 
    pool.execute()
    or 
    pool.query()
    for transactions -- each call may use a different connection, breaking transaction isolation; use 
    pool.getConnection()
  • Importing from 
    mysql2
    instead of 
    mysql2/promise
    for async/await code -- the base module returns callback-based objects, 
    await
    will not work as expected
  • Not releasing connections acquired with 
    pool.getConnection()
    -- connection leak exhausts the pool; always release in a 
    finally
    block
Medium Priority Issues:
  • Using 
    query()
    instead of 
    execute()
    for parameterized queries -- misses prepared statement caching and binary protocol efficiency
  • Missing pool 
    error
    event handler -- unhandled connection errors crash the Node.js process
  • Setting 
    connectionLimit
    too high -- each MySQL connection uses ~10 MB of server memory; 10-20 is usually sufficient
  • Not setting 
    enableKeepAlive: true
    -- idle connections get dropped by firewalls/load balancers causing 
    ECONNRESET
Common Mistakes:
  • Expecting 
    pool.end()
    to wait for active queries -- it immediately destroys all connections; drain queries first
  • Using 
    rows.length
    to check if an UPDATE affected rows -- use 
    result.affectedRows
    from 
    ResultSetHeader
    instead
  • Assuming 
    insertId
    is always the auto-increment value -- for 
    INSERT ... ON DUPLICATE KEY UPDATE
    , 
    insertId
    is 
    0
    if the existing row was updated, not inserted
  • Calling 
    connection.release()
    after 
    connection.destroy()
    -- destroy removes the connection from the pool entirely; release returns it
  • Treating 
    null
    and 
    undefined
    as interchangeable in parameter arrays -- mysql2 converts 
    null
    to SQL 
    NULL
    but 
    undefined
    causes a protocol error
Gotchas & Edge Cases:
  • DECIMAL
    and 
    BIGINT
    columns are returned as strings by default to avoid JavaScript floating-point precision loss -- parse explicitly if you need numbers
  • DATE
    columns return JavaScript 
    Date
    objects, but 
    DATETIME
    precision beyond milliseconds is truncated -- MySQL supports microsecond precision, JavaScript 
    Date
    does not
  • execute()
    with named placeholders requires 
    namedPlaceholders: true
    on the pool/connection config -- the default is unnamed 
    ?
    only
  • multipleStatements: true
    is a security risk -- it enables SQL injection via 
    ;
    in user input if combined with 
    query()
    ; only enable when needed and never with user-provided SQL
  • Pool 
    waitForConnections: false
    throws immediately when all connections are in use instead of queuing -- the default 
    true
    is almost always what you want
  • ResultSetHeader.warningStatus
    indicates server warnings -- check it after DDL operations (the deprecated 
    OkPacket
    type had a separate 
    warningCount
    field; 
    ResultSetHeader
    has always used 
    warningStatus
    )
</red_flags>
<critical_reminders>
高优先级问题：
  • 将用户输入插入SQL字符串（
    \
    SELECT * FROM users WHERE id = ${id}`
    ）——存在SQL注入漏洞，始终使用
    execute()
    和
    ?`占位符
  • 使用
    pool.execute()
    或
    pool.query()
    处理事务——每次调用可能使用不同连接，破坏事务隔离；请使用
    pool.getConnection()
  • 异步代码从
    mysql2
    而非
    mysql2/promise
    导入——基础模块返回基于回调的对象，
    await
    无法按预期工作
  • 未释放通过
    pool.getConnection()
    获取的连接——连接泄漏会耗尽池；始终在
    finally
    块中释放
中优先级问题：
  • 使用
    query()
    而非
    execute()
    执行参数化查询——错过预编译语句缓存和二进制协议效率
  • 未处理池的
    error
    事件——未处理的连接错误会导致Node.js进程崩溃
  • connectionLimit
    设置过高——每个MySQL连接占用约10MB服务器内存；通常10-20足够
  • 未设置
    enableKeepAlive: true
    ——空闲连接会被防火墙/负载均衡器断开，导致
    ECONNRESET
    错误
常见错误：
  • 期望
    pool.end()
    等待活跃查询完成——它会立即销毁所有连接；先完成查询再调用
  • 使用
    rows.length
    检查UPDATE是否影响行——请使用
    ResultSetHeader
    的
    result.affectedRows
  • 假设
    insertId
    始终是自增的值——对于
    INSERT ... ON DUPLICATE KEY UPDATE
    ，如果更新现有行而非插入新行，
    insertId
    为
    0
  • 在
    connection.destroy()
    后调用
    connection.release()
    ——destroy会将连接从池中完全移除；release会将连接返回池
  • 在参数数组中把
    null
    和
    undefined
    视为可互换——mysql2将
    null
    转换为SQL 
    NULL
    ，但
    undefined
    会导致协议错误
陷阱与边缘情况：
  • 默认情况下
    DECIMAL
    和
    BIGINT
    列返回字符串，避免JavaScript浮点数精度丢失——如果需要数字请显式解析
  • DATE
    列返回JavaScript 
    Date
    对象，但
    DATETIME
    的毫秒级以上精度会被截断——MySQL支持微秒精度，JavaScript 
    Date
    不支持
  • 使用命名占位符的
    execute()
    需要在池/连接配置中设置
    namedPlaceholders: true
    ——默认仅支持匿名
    ?
  • multipleStatements: true
    存在安全风险——如果结合
    query()
    使用，用户输入中的
    ;
    会导致SQL注入；仅在必要时启用，且绝不要与用户提供的SQL一起使用
  • 池的
    waitForConnections: false
    会在所有连接被占用时立即抛出错误而非排队——默认的
    true
    几乎总是正确选择
  • ResultSetHeader.warningStatus
    表示服务器警告——DDL操作后请检查它（已弃用的
    OkPacket
    类型有单独的
    warningCount
    字段；
    ResultSetHeader
    始终使用
    warningStatus
    ）
</red_flags>
<critical_reminders>

CRITICAL REMINDERS

重要提醒

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, 
import type
, named constants)
(You MUST use 
execute()
with 
?
placeholders for ALL queries containing user input -- NEVER interpolate values into SQL strings with template literals or string concatenation)
(You MUST use 
pool.getConnection()
for transactions and release the connection in a 
finally
block -- pool convenience methods (
pool.execute()
) use a different connection per call and cannot maintain transaction state)
(You MUST always import from 
mysql2/promise
for async/await code -- the base 
mysql2
module returns callback-based objects that do not support 
await
)
(You MUST handle the pool 
error
event -- unhandled connection errors crash the Node.js process)
Failure to follow these rules will cause SQL injection vulnerabilities, transaction corruption, connection pool exhaustion, and application crashes.
</critical_reminders>
所有代码必须遵循CLAUDE.md中的项目约定（短横线命名、命名导出、导入顺序、
import type
、命名常量）
（所有包含用户输入的查询必须使用
execute()
和
?
占位符——绝不要用模板字符串或字符串拼接将值插入SQL字符串）
（事务必须使用
pool.getConnection()
获取连接，并在
finally
块中释放连接——池便捷方法（
pool.execute()
）每次调用使用不同连接，无法维护事务状态）
（异步代码必须始终从
mysql2/promise
导入——基础
mysql2
模块返回基于回调的对象，不支持
await
）
（必须处理池的
error
事件——未处理的连接错误会导致Node.js进程崩溃）
不遵守这些规则会导致SQL注入漏洞、事务损坏、连接池耗尽和应用崩溃。
</critical_reminders>