在 Next.js 项目中使用 MySQL2 连接并查询 MySQL 数据为什么选择 MySQL2？ MySQL2 是

为什么选择 MySQL2？

MySQL2 是 mysql 库的增强版，提供了更好的性能和对 Promise 的支持。以下是 mysql2 的几个优点：

Promise 支持：可以轻松与现代异步模式（async/await）结合使用。
高性能：相比于老的 mysql 库，MySQL2 提供了更好的查询性能和优化。
连接池：内置支持连接池管理，避免了每次查询都需要重新建立连接的开销。
与原生 MySQL API 兼容：与 MySQL 的原生 API 完全兼容，可以很方便地执行 SQL 语句。

环境准备

确保你已经安装了 Node.js 和 pnpm（或其他包管理器如 npm 或 yarn）。在开始之前，你需要在项目中安装 mysql2 依赖。

pnpm add mysql2

接下来我们逐步配置并实现数据库交互。

配置数据库连接池

为了提高性能，我们推荐使用连接池来管理数据库连接。通过连接池，应用程序可以在多个请求间重用连接，减少了每次执行 SQL 时创建和销毁连接的开销。

在 Next.js 项目中，可以创建一个单独的文件来管理数据库连接。这里我们将连接池的配置放在 lib/mysql.js 中：

// lib/mysql.js
import mysql from 'mysql2/promise';

// 创建连接池
const pool = mysql.createPool({
  host: 'localhost',     // 数据库主机名
  user: 'your-username', // 数据库用户名
  password: 'your-password', // 数据库密码
  database: 'your-database-name', // 数据库名称
  waitForConnections: true, // 等待可用连接
  connectionLimit: 10,  // 最大连接数
  queueLimit: 0         // 连接请求队列最大数量
});

export default pool;

这里的 mysql.createPool 函数创建了一个连接池，配置了数据库的连接信息，并设置了 connectionLimit 以限制同时运行的最大连接数。

构建 API 路由来查询数据

Next.js 的 API 路由允许你在 pages/api 目录下创建服务端 API。我们将通过一个简单的 API 路由从 MySQL 数据库中查询用户表的数据。

在 pages/api/users.js 中创建如下 API：

// pages/api/users.js
import pool from '../../lib/mysql';

export default async function handler(req, res) {
  try {
    // 执行 SQL 查询
    const [rows] = await pool.query('SELECT * FROM users');
    res.status(200).json(rows);  // 返回查询结果
  } catch (error) {
    console.error('Database query error:', error);
    res.status(500).json({ message: 'Internal Server Error' });
  }
}

在这个 API 路由中，我们使用 pool.query 来执行 SQL 查询，并通过 res.json 方法将查询结果返回给前端。pool.query 返回一个数组，其中第一个元素是查询结果（即数据行）。如果查询失败，则返回 500 错误，并记录错误日志。

API 路由的执行逻辑

这个 API 路由的执行流程如下：

客户端发出 GET 请求到 /api/users。
handler 函数被触发，连接池通过 pool.query 查询数据库。
查询成功时，结果通过 JSON 格式返回给客户端。
查询失败时，返回 HTTP 500 错误。

动态查询和参数化查询

为了避免 SQL 注入攻击，推荐使用参数化查询。参数化查询能够有效地防止恶意用户通过拼接 SQL 注入恶意代码。假如你希望通过 id 参数来动态查询单个用户的信息，可以修改 API 路由如下：

// pages/api/user.js
import pool from '../../lib/mysql';

export default async function handler(req, res) {
  const { id } = req.query; // 获取 URL 参数

  if (!id) {
    return res.status(400).json({ message: 'User ID is required' });
  }

  try {
    // 使用参数化查询，避免 SQL 注入
    const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [id]);

    if (rows.length === 0) {
      return res.status(404).json({ message: 'User not found' });
    }

    res.status(200).json(rows[0]);
  } catch (error) {
    console.error('Database query error:', error);
    res.status(500).json({ message: 'Internal Server Error' });
  }
}

在这里，我们通过 req.query 获取 URL 参数（例如 /api/user?id=1），并使用 ? 占位符执行参数化查询，将 id 安全地传递给 SQL 查询。这种方式大大提高了安全性，避免了直接拼接 SQL 可能带来的注入风险。

错误处理与连接管理

在实际项目中，良好的错误处理是必不可少的。虽然 mysql2 本身已经处理了很多常见的错误，但你仍然可以在 API 中针对特定情况返回适当的 HTTP 状态码。例如：

如果用户提供了无效的查询参数，可以返回 400 错误（Bad Request）。
如果数据库中未找到记录，可以返回 404 错误（Not Found）。
如果数据库连接失败或查询出现问题，可以返回 500 错误（Internal Server Error）。

扩展与优化

连接池优化：根据应用的规模和流量，你可以调整连接池的 connectionLimit 和 queueLimit 参数，以优化数据库连接管理。对高并发的应用来说，合理的连接池设置可以有效提升性能。
事务管理：如果你的业务逻辑涉及多步数据库操作，例如更新多个表，可以使用 pool.getConnection 来获取一个专用的连接，并通过 connection.beginTransaction 来开启事务。

// 示例：事务管理
const connection = await pool.getConnection();
await connection.beginTransaction();

try {
  await connection.query('UPDATE accounts SET balance = balance - 100 WHERE id = ?', [fromAccountId]);
  await connection.query('UPDATE accounts SET balance = balance + 100 WHERE id = ?', [toAccountId]);
  
  await connection.commit();  // 提交事务
} catch (error) {
  await connection.rollback(); // 事务回滚
  throw error;
} finally {
  connection.release();  // 释放连接
}

缓存：为了减少频繁的数据库查询请求，可以引入缓存机制（如 Redis），将频繁访问的数据缓存在内存中，减少数据库负载。