Node.js 操作 MySQL 最佳实践:连接池、参数化与事务

Node 后端连 MySQL,看起来简单 —— 装个驱动、写条 SQL、拿到结果。但真正放到生产,连接泄露、SQL 注入、事务忘提交这些坑会一个个找上门。这篇梳理一套能直接上生产的实践。

用 mysql2,别用 mysql

先说选型。老牌的 mysql 包已经多年低维护,现在的事实标准是 mysql2:它 API 兼容、性能更好,而且原生支持 Promise 和预处理语句(prepared statement)。

pnpm add mysql2

引入时直接用它的 Promise 版本,告别回调:

import mysql from 'mysql2/promise'

核心:用连接池,不要每次新建连接

这是最重要的一条。绝对不要每来一个请求就 createConnection,用完再关 —— 建立 TCP 连接 + 鉴权握手的开销很大,高并发下会瞬间拖垮数据库。

正确做法是进程启动时建一个连接池(pool),后续所有查询从池里借连接,用完自动还回去:

// db.js —— 全局单例连接池
import mysql from 'mysql2/promise'

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  port: 3306,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true, // 池满时排队等待,而非直接报错
  connectionLimit: 10,      // 池子大小,根据数据库承载能力调
  charset: 'utf8mb4',       // 支持完整 UTF-8(含 emoji)
})

export default pool

之后整个应用复用这一个 pool。pool.query() 会自动从池里取连接、执行、归还,你不用手动管理:

import pool from './db.js'

const [rows] = await pool.query('SELECT * FROM posts LIMIT 10')
console.log(rows)

注意 mysql2/promise 的返回值是个数组 [rows, fields],所以要用 const [rows] = ... 解构出第一项。这是新手常忘的细节。

安全红线:永远用参数化查询

下面这种字符串拼接 SQL,是绝对禁止的,它会直接导致 SQL 注入:

// ❌ 致命错误:用户传 id = "1 OR 1=1; DROP TABLE posts;--" 就完蛋了
const sql = `SELECT * FROM posts WHERE id = ${req.query.id}`
const [rows] = await pool.query(sql)

正确写法是用 ? 占位符,把参数单独传进去。驱动会负责转义,从根上杜绝注入:

// ✅ 参数化查询:参数永远当数据处理,不会被当成 SQL 执行
const [rows] = await pool.query(
  'SELECT * FROM posts WHERE id = ? AND status = ?',
  [req.query.id, 'published'],
)

记住这条铁律:任何来自用户的值,都必须走 ? 占位符,绝不拼进 SQL 字符串。这没有例外。

query 与 execute 的区别

mysql2 提供两个执行方法:

query():每次把 SQL 发给服务器解析执行
execute():使用预处理语句,SQL 模板会被服务器缓存,同一条 SQL 反复执行时更快,也更安全

对于会被频繁执行的固定 SQL,优先用 execute:

// 预处理语句:重复执行同一模板时性能更好
const [rows] = await pool.execute(
  'SELECT * FROM posts WHERE category_id = ?',
  [categoryId],
)

两者都支持 ? 占位符,日常用哪个都防注入。需要极致性能、SQL 重复度高,选 execute。

事务:要么全成功,要么全回滚

涉及多张表、多步写入的操作(比如"下单减库存 + 写订单 + 记流水"),必须放进事务,保证原子性。事务的关键是:必须从池里取一个独立连接(因为事务状态是绑在单个连接上的),并在结束时务必释放:

async function transferPost(fromId, toId, postId) {
  // 1. 从池里借一个专用连接
  const conn = await pool.getConnection()
  try {
    await conn.beginTransaction()

    // 2. 多步操作走同一个 conn
    await conn.execute('UPDATE users SET post_count = post_count - 1 WHERE id = ?', [fromId])
    await conn.execute('UPDATE users SET post_count = post_count + 1 WHERE id = ?', [toId])
    await conn.execute('UPDATE posts SET author_id = ? WHERE id = ?', [toId, postId])

    // 3. 全部成功才提交
    await conn.commit()
  } catch (err) {
    // 4. 任何一步出错,全部回滚
    await conn.rollback()
    throw err
  } finally {
    // 5. 关键:无论成败,都要把连接还给池子
    conn.release()
  }
}

最容易出事的是第 5 步:忘了 conn.release()。借出去的连接一直不还,池子很快被掏空,新查询全部卡死等待。所以 release() 一定要放在 finally 里,确保任何路径都会执行。

几个生产里会咬人的坑

1. 连接泄露:除了事务忘 release,还有一种是 getConnection 后中途 return 或抛错没走到 release。养成"getConnection 必配 try/finally"的肌肉记忆。

2. 时区问题:Node 和 MySQL 时区不一致时,存取的时间会莫名其妙差几个小时。可以在连接配置里显式指定 timezone,或统一用 UTC 存储、展示层再转。

3. 大结果集打爆内存:SELECT 几十万行一次性读进内存会 OOM。要导出大量数据,用流式查询逐行处理:

const conn = await pool.getConnection()
const stream = conn.connection.query('SELECT * FROM huge_table').stream()
for await (const row of stream) {
  // 一行一行处理,内存恒定
  await handle(row)
}
conn.release()

4. 批量插入别用循环:循环里执行一万次 INSERT 会很慢。用一条 SQL 批量插:

// ✅ 一条 SQL 插多行,比循环快几个数量级
const values = posts.map((p) => [p.title, p.slug])
await pool.query('INSERT INTO posts (title, slug) VALUES ?', [values])

5. 敏感信息别进日志:数据库密码、用户 token 这类字段,记日志前务必脱敏,别把它们随 SQL 参数一起打到日志里。

一份最小可用清单

落到实处,数据访问层守住这几条就稳了:

用 mysql2/promise,全局一个连接池,不要反复建连接
所有用户输入走 ? 占位符,绝不拼接 SQL
频繁执行的固定 SQL 用 execute(预处理)
多步写入用事务,getConnection + try/catch/finally,release 放 finally
字符集用 utf8mb4,时区显式配置
大结果集用流式查询,批量写入用单条多值 SQL
敏感字段记日志前脱敏

小结

Node 操作 MySQL 的"最佳实践",归根结底就是绕开几个高频坑:用连接池(别反复建连接)、参数化查询(防注入)、事务配 finally release(防连接泄露)。把这三件事做对,你的数据访问层就已经达到生产标准了。剩下的时区、流式、批量,都是锦上添花的优化。