MySQL 集成

数据库连接池在应用启动时创建并复用。示例使用支持 Promise 的 mysql2，参数占位符避免将输入直接拼接进 SQL。

pnpm add mysql2

import mysql from 'mysql2/promise'

const db = mysql.createPool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT ?? 3306),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
})

查询

const [users] = await db.execute('SELECT id, username FROM users WHERE id = ?', [userId])

查询结果为行数组。按需选择字段，避免 SELECT * 将密码、令牌等敏感列返回给上层。

写入

const [result] = await db.execute('INSERT INTO users (username, password_hash) VALUES (?, ?)', [username, passwordHash])

const userId = result.insertId

更新和删除同样使用占位符：

const [result] = await db.execute('UPDATE users SET username = ? WHERE id = ?', [username, userId])

if (result.affectedRows === 0) {
  // 没有匹配记录
}

多条必须同时成功或失败的写操作应使用事务，并通过同一个连接执行。

const connection = await db.getConnection()

try {
  await connection.beginTransaction()
  await connection.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId])
  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()
}