数据库集成：MySQL

MySQL 是最流行的开源关系型数据库，与 Node.js 结合可以构建强大的数据驱动应用。本章将介绍如何使用 mysql2 库在 Node.js 中连接和操作 MySQL 数据库，包括连接管理、CRUD 操作、预处理语句、事务处理以及性能优化技巧。

1. MySQL 与 Node.js 简介

MySQL 使用 SQL 语言进行数据管理，具有稳定性高、性能好、社区活跃等优点。在 Node.js 生态中，有多种 MySQL 客户端库，其中最常用的是 mysql 和 mysql2。推荐使用 mysql2，因为它支持 Promise、预处理语句的占位符、更快的性能，并且与 mysql API 兼容。

2. 环境准备

在开始之前，请确保你的系统已安装 MySQL 数据库并启动服务。可以从 MySQL 官网 下载安装。同时，准备一个数据库和用户用于测试。

创建项目目录并初始化 package.json：

mkdir node-mysql
cd node-mysql
npm init -y

3. 安装 mysql2

安装 mysql2 库：

npm install mysql2

同时建议安装 dotenv 管理环境变量：

npm install dotenv

4. 连接 MySQL

创建一个 .env 文件存放数据库连接信息：

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=yourpassword
DB_DATABASE=testdb
DB_PORT=3306

然后创建 db.js 模块来建立连接。mysql2 提供了两种连接方式：单连接和连接池。生产环境强烈建议使用连接池。

4.1 创建连接池

const mysql = require('mysql2');
require('dotenv').config();

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
  port: process.env.DB_PORT,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 将 pool 转换为使用 Promise 的版本
const promisePool = pool.promise();

module.exports = promisePool;

参数说明：

• connectionLimit：连接池中最大连接数
• waitForConnections：当无可用连接时是否等待
• queueLimit：等待队列的最大长度，0 表示不限制

4.2 测试连接

可以在主应用文件中测试连接：

const db = require('./db');

async function testConnection() {
  try {
    const [rows] = await db.query('SELECT 1 + 1 AS result');
    console.log('数据库连接成功，测试结果:', rows[0].result);
  } catch (err) {
    console.error('数据库连接失败:', err.message);
  }
}

testConnection();

5. 执行 SQL 查询

mysql2 的 Promise API 提供了 query() 和 execute() 方法。推荐使用 execute() 进行预处理语句，防止 SQL 注入。

5.1 简单查询

const db = require('./db');

async function getUsers() {
  try {
    const [rows, fields] = await db.query('SELECT * FROM users');
    console.log(rows); // 查询结果数组
  } catch (err) {
    console.error(err);
  }
}

5.2 参数化查询（预处理语句）

使用 ? 占位符，将参数作为数组传入：

async function getUserById(id) {
  try {
    const [rows] = await db.execute('SELECT * FROM users WHERE id = ?', [id]);
    return rows[0];
  } catch (err) {
    console.error(err);
  }
}

对于多个参数：

async function getUsersByAgeRange(minAge, maxAge) {
  const [rows] = await db.execute(
    'SELECT * FROM users WHERE age BETWEEN ? AND ?',
    [minAge, maxAge]
  );
  return rows;
}

5.3 插入、更新、删除

这些操作也使用 execute，返回的结果对象包含 affectedRows、insertId 等属性。

async function createUser(name, email) {
  const [result] = await db.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    [name, email]
  );
  console.log('插入的 ID:', result.insertId);
  return result;
}

async function updateUser(id, name) {
  const [result] = await db.execute(
    'UPDATE users SET name = ? WHERE id = ?',
    [name, id]
  );
  console.log('影响的行数:', result.affectedRows);
}

async function deleteUser(id) {
  const [result] = await db.execute('DELETE FROM users WHERE id = ?', [id]);
  console.log('删除的行数:', result.affectedRows);
}

6. 使用连接池的最佳实践

• 在整个应用生命周期中复用同一个连接池实例，不要频繁创建和销毁。
• 确保每次查询后连接会自动释放回池中（使用 Promise API 会自动处理）。
• 根据应用负载调整 connectionLimit。
• 处理连接池关闭（例如应用退出时）：

process.on('SIGINT', async () => {
  await db.end();
  console.log('连接池已关闭');
  process.exit(0);
});

7. 事务处理

当需要多个操作原子性执行时，使用事务。mysql2 通过连接对象支持事务。因为连接池管理连接，需要从池中获取一个连接来执行事务。

const db = require('./db');

async function transferMoney(fromId, toId, amount) {
  // 从池中获取连接
  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();
    console.log('转账成功');
  } catch (err) {
    await connection.rollback();
    console.error('转账失败，已回滚', err);
  } finally {
    connection.release(); // 释放连接回池
  }
}

8. 防止 SQL 注入

SQL 注入是严重的安全漏洞。使用 execute() 和占位符可以完全避免注入。严禁通过字符串拼接 SQL 语句。

不安全的做法：

// 危险！不要这样做
const unsafeQuery = `SELECT * FROM users WHERE name = '${userInput}'`;

安全的做法：

const safeQuery = await db.execute('SELECT * FROM users WHERE name = ?', [userInput]);

9. 使用环境变量管理配置

通过 dotenv 将数据库连接信息存储在 .env 文件中，并添加到 .gitignore 避免敏感信息泄露。

.env
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=yourpassword
DB_DATABASE=mydb

10. 完整示例：用户管理 API

下面是一个使用 Express + mysql2 构建的简单用户管理 REST API。

项目结构：

project/
├── .env
├── app.js
├── db.js
└── routes/
    └── users.js

db.js

const mysql = require('mysql2');
require('dotenv').config();

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

module.exports = pool.promise();

routes/users.js

const express = require('express');
const router = express.Router();
const db = require('../db');

// 获取所有用户
router.get('/', async (req, res) => {
  try {
    const [rows] = await db.query('SELECT * FROM users');
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// 获取单个用户
router.get('/:id', async (req, res) => {
  try {
    const [rows] = await db.execute('SELECT * FROM users WHERE id = ?', [req.params.id]);
    if (rows.length === 0) {
      return res.status(404).json({ error: '用户不存在' });
    }
    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// 创建用户
router.post('/', async (req, res) => {
  const { name, email } = req.body;
  try {
    const [result] = await db.execute(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      [name, email]
    );
    res.status(201).json({ id: result.insertId, name, email });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

// 更新用户
router.put('/:id', async (req, res) => {
  const { name, email } = req.body;
  try {
    const [result] = await db.execute(
      'UPDATE users SET name = ?, email = ? WHERE id = ?',
      [name, email, req.params.id]
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: '用户不存在' });
    }
    res.json({ message: '更新成功' });
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

// 删除用户
router.delete('/:id', async (req, res) => {
  try {
    const [result] = await db.execute('DELETE FROM users WHERE id = ?', [req.params.id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: '用户不存在' });
    }
    res.json({ message: '删除成功' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;

app.js

const express = require('express');
const userRoutes = require('./routes/users');

const app = express();
app.use(express.json());

app.use('/users', userRoutes);

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => console.log(`Server running on port ${PORT}`));

11. 错误处理与日志

始终捕获数据库操作的错误，并适当处理（如返回 500 状态码）。开发时可以启用 mysql2 的调试日志：

// 在创建连接池时添加
const pool = mysql.createPool({
  // ... 其他配置
  debug: true // 生产环境请关闭
});

12. 性能优化

• 使用连接池：避免频繁创建连接。
• 批量插入：使用单条 INSERT 语句插入多行，减少数据库交互。
• 合理设计索引：根据查询条件创建索引。
• 避免 SELECT *：只查询需要的字段。
• 使用 LIMIT：防止返回大量数据。

13. 常见问题

• 连接超时：检查网络、防火墙设置，或者调整 MySQL 的 wait_timeout。
• 连接数过多：调整连接池大小，确保用完释放连接。
• 字符集问题：可以在连接配置中设置 charset: 'utf8mb4' 以支持表情符号。