Node.js 连接 MySQL 数据库实现增删改查操作

在现代 Node.js 服务端开发中，与关系型数据库 MySQL 交互的核心路径是使用支持异步 Promise 的高性能驱动模块（如 mysql2），配置具备自动重连与资源复用能力的连接池，并通过预编译参数化 SQL 语句安全地执行增加（Create）、查询（Read）、更新（Update）和删除（Delete）四项核心操作。本文将围绕此路径完整演示从环境准备、表结构设计、模块封装到具体业务逻辑实现的全部过程。

一、连接机制解析：单连接与连接池的选择

初学者在编写数据库连接代码时，常直接使用 mysql.createConnection() 创建单个连接对象。这种方式看似简单直观，但在实际运行中存在严重缺陷。MySQL 服务端对单个空闲连接设有超时阈值（通常由 wait_timeout 参数控制），当连接长时间无查询请求时会被服务端单方面切断，导致后续查询抛出 PROTOCOL_CONNECTION_LOST 异常；此外，单连接无法并发处理多条 SQL 请求，在并发请求下请求会被串行堵塞，严重影响应用吞吐。

在生产环境中，推荐且通用的方案是使用连接池（Connection Pool）。连接池会在启动时预先维护一组物理 TCP 连接，并在需要执行 SQL 时动态借出连接、用完后归还池中，避免了频繁建立和断开 TCP 握手的资源开销。连接池还会自动处理空闲心跳与异常断开重连，当并发请求超过连接池上限时，多余的请求会自动进入排队队列等待释放，从而保护数据库不受高并发瞬间冲击。

二、操作步骤与环境准备

为了实现可落地的 Node.js 与 MySQL 交互代码，建议按照规范的五步工程流程进行实施：从安装驱动、初始化数据表，到封装连接池单例模块，再到编写参数化业务逻辑与异常处理。

1. 初始化项目与安装依赖

在终端中创建项目目录并安装官方推荐的 mysql2 驱动。相比过往旧版本的 mysql 模块，mysql2 提供了更快的解析性能、原生 Promise API 支持以及服务端预编译语句支持。

# 初始化 Node.js 项目
mkdir node-mysql-crud && cd node-mysql-crud
npm init -y

# 安装 mysql2 模块
npm install mysql2

2. 创建演示数据表

在 MySQL 中创建一张名为 users 的数据表，包含自增主键、用户名、邮箱、年龄以及记录创建时间。使用 utf8mb4 字符集以完整支持中文与特殊表情符号：

CREATE DATABASE IF NOT EXISTS `test_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `test_db`;

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL COMMENT '用户名',
  `email` VARCHAR(100) NOT NULL UNIQUE COMMENT '电子邮箱',
  `age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 封装连接池单例模块 (db.js)

建立独立的文件 db.js 负责管理连接池。通过引入 mysql2/promise，所有的数据库操作方法都将返回原生的 Promise，便于配合 async/await 语法使用。

// db.js
const mysql = require('mysql2/promise');

// 创建连接池
const pool = mysql.createPool({
  host: '127.0.0.1',        // 数据库主机地址
  port: 3306,               // 数据库端口
  user: 'root',             // 数据库用户名
  password: 'your_password', // 数据库密码
  database: 'test_db',      // 连接的目标数据库名称
  waitForConnections: true, // 连接数耗尽时是否等待排队
  connectionLimit: 10,      // 连接池最大连接数
  queueLimit: 0,            // 排队队列最大长度（0 表示无限制）
  timezone: '+08:00'        // 时区设置为东八区
});

module.exports = pool;

三、核心安全基石：参数化查询与防 SQL 注入

在实现增删改查之前，必须确立编写 SQL 的安全原则。许多初学者习惯使用 JavaScript 模板字符串拼接 SQL 语句，例如：

// 错误且极其危险的写法：字符串直接拼接
const sql = `SELECT * FROM users WHERE username = '${reqUsername}'`;

如果外部输入的 reqUsername 包含恶意字符（如 admin' OR '1'='1），原本用于比较的字符串闭合后，会将后续内容直接升级为 SQL 逻辑条件，导致未授权数据被全部读取。

在 mysql2 中，应当统一使用 pool.execute() 并配合占位符 ?。execute() 会在 MySQL 协议层触发服务端的预编译语句（Prepared Statement）：首先将 SQL 语法骨架发送给数据库完成词法与语法分析，随后将外部参数作为纯文本值传入。此时无论参数中包含何种特殊字符，数据库都只会将其作为普通字符串字面量对待，彻底杜绝了 SQL 注入漏洞。

四、增删改查（CRUD）操作的完整实现

通过 pool.execute() 执行 SQL 时，返回的结果是一个数组元组：const [result, fields] = await pool.execute(...)。对于数据变更操作（INSERT/UPDATE/DELETE），result 是一个包含执行状态的对象（ResultSetHeader）；对于查询操作（SELECT），result 是包含数据行对象的数组。

1. 增 (Create) - 插入新数据

向数据表写入一条新记录，并获取自增主键 ID 和受影响行数。

/**
 * 新增用户
 * @param {string} username 用户名
 * @param {string} email 电子邮箱
 * @param {number} age 年龄
 * @returns {Promise<number>} 返回新插入记录的自增 ID
 */
async function createUser(username, email, age) {
  const sql = 'INSERT INTO `users` (`username`, `email`, `age`) VALUES (?, ?, ?)';
  const [result] = await pool.execute(sql, [username, email, age]);

  // result 关键属性:
  // - insertId: 本次插入产生的自增主键 ID
  // - affectedRows: 受影响的行数，插入成功时为 1
  console.log(`用户创建成功，自增 ID: ${result.insertId}，受影响行数: ${result.affectedRows}`);
  return result.insertId;
}

2. 查 (Read) - 查询数据

查询包含两类典型场景：按主键查找单条记录，以及按条件分页或批量检索多条记录。

/**
 * 根据 ID 查询单条用户记录
 * @param {number} id 用户 ID
 * @returns {Promise<object|null>} 存在时返回对象，不存在时返回 null
 */
async function getUserById(id) {
  const sql = 'SELECT `id`, `username`, `email`, `age`, `created_at` FROM `users` WHERE `id` = ? LIMIT 1';
  const [rows] = await pool.execute(sql, [id]);

  // rows 是一个包含行数据的数组
  if (rows.length === 0) {
    return null;
  }
  return rows[0];
}

/**
 * 分页获取用户列表
 * @param {number} page 页码（从 1 开始）
 * @param {number} pageSize 每页条数
 */
async function listUsers(page = 1, pageSize = 10) {
  const offset = (page - 1) * pageSize;
  // 注意：LIMIT 和 OFFSET 在特定 MySQL 版本中不能作为字符串参数绑定，可直接传入整数或通过模板严格转换
  const sql = 'SELECT `id`, `username`, `email`, `age`, `created_at` FROM `users` ORDER BY `id` DESC LIMIT ? OFFSET ?';
  const [rows] = await pool.execute(sql, [String(pageSize), String(offset)]);
  return rows;
}

3. 改 (Update) - 修改已有数据

更新已有记录。在处理更新返回状态时，需注意区分 affectedRows 与 changedRows 的区别。

/**
 * 更新用户邮箱和年龄
 * @param {number} id 用户 ID
 * @param {string} newEmail 新邮箱
 * @param {number} newAge 新年龄
 * @returns {Promise<boolean>} 是否匹配到目标行
 */
async function updateUser(id, newEmail, newAge) {
  const sql = 'UPDATE `users` SET `email` = ?, `age` = ? WHERE `id` = ?';
  const [result] = await pool.execute(sql, [newEmail, newAge, id]);

  // affectedRows: 满足 WHERE 条件被匹配到的行数
  // changedRows: 实际发生物理值变更的行数（如果新值与旧值完全相同，changedRows 可能为 0）
  console.log(`更新操作：匹配到 ${result.affectedRows} 行，实际修改 ${result.changedRows} 行`);
  return result.affectedRows > 0;
}

4. 删 (Delete) - 删除数据

删除指定主键的数据行。生产环境中通常要求严格带有限定条件，避免误删全表。

/**
 * 根据 ID 删除用户
 * @param {number} id 用户 ID
 * @returns {Promise<boolean>} 是否成功删除记录
 */
async function deleteUser(id) {
  const sql = 'DELETE FROM `users` WHERE `id` = ?';
  const [result] = await pool.execute(sql, [id]);

  console.log(`删除操作完成，删除行数: ${result.affectedRows}`);
  return result.affectedRows > 0;
}

五、综合测试与运行示例 (app.js)

将上述方法整合到一个完整的业务流程中，依次执行新增、查询、修改、重新查询和删除，并在流程结束时安全释放连接池资源。

// app.js
const pool = require('./db');

async function runDemo() {
  try {
    console.log('--- 1. 执行新增 (Create) ---');
    const insertSql = 'INSERT INTO `users` (`username`, `email`, `age`) VALUES (?, ?, ?)';
    const [createResult] = await pool.execute(insertSql, ['张伟', 'zhangwei@example.com', 25]);
    const userId = createResult.insertId;
    console.log(`成功插入用户，ID = ${userId}`);

    console.log('\n--- 2. 执行单条查询 (Read) ---');
    const selectSql = 'SELECT * FROM `users` WHERE `id` = ? LIMIT 1';
    const [rows] = await pool.execute(selectSql, [userId]);
    console.log('查询结果:', rows[0]);

    console.log('\n--- 3. 执行更新 (Update) ---');
    const updateSql = 'UPDATE `users` SET `age` = ? WHERE `id` = ?';
    const [updateResult] = await pool.execute(updateSql, [26, userId]);
    console.log(`更新结果: affectedRows=${updateResult.affectedRows}, changedRows=${updateResult.changedRows}`);

    console.log('\n--- 4. 再次查询验证修改 ---');
    const [updatedRows] = await pool.execute(selectSql, [userId]);
    console.log('更新后数据:', updatedRows[0]);

    console.log('\n--- 5. 执行删除 (Delete) ---');
    const deleteSql = 'DELETE FROM `users` WHERE `id` = ?';
    const [deleteResult] = await pool.execute(deleteSql, [userId]);
    console.log(`删除成功，affectedRows=${deleteResult.affectedRows}`);

    console.log('\n--- 6. 验证已删除 ---');
    const [afterDeleteRows] = await pool.execute(selectSql, [userId]);
    console.log('删除后查询结果:', afterDeleteRows[0] || '记录不存在');

  } catch (error) {
    console.error('数据库操作发生异常:', error.message);
  } finally {
    // 脚本执行完毕后关闭连接池，释放底层 TCP 套接字
    await pool.end();
    console.log('\n连接池已平滑关闭');
  }
}

runDemo();

在终端执行该脚本，预期输出如下示例：

--- 1. 执行新增 (Create) ---
成功插入用户，ID = 1

--- 2. 执行单条查询 (Read) ---
查询结果: { id: 1, username: '张伟', email: 'zhangwei@example.com', age: 25, created_at: 2026-10-31T03:50:00.000Z }

--- 3. 执行更新 (Update) ---
更新结果: affectedRows=1, changedRows=1

--- 4. 再次查询验证修改 ---
更新后数据: { id: 1, username: '张伟', email: 'zhangwei@example.com', age: 26, created_at: 2026-10-31T03:50:00.000Z }

--- 5. 执行删除 (Delete) ---
删除成功，affectedRows=1

--- 6. 验证已删除 ---
删除后查询结果: 记录不存在

连接池已平滑关闭

六、易混淆概念与常见踩坑辨析

const connection = await pool.getConnection();
try {
  await connection.beginTransaction(); // 开启事务
  await connection.execute('UPDATE accounts SET balance = balance - 100 WHERE id = ?', [fromId]);
  await connection.execute('UPDATE accounts SET balance = balance + 100 WHERE id = ?', [toId]);
  await connection.commit();          // 提交事务
} catch (err) {
  await connection.rollback();        // 回滚事务
  throw err;
} finally {
  connection.release();               // 务必释放连接回连接池，防止连接泄露！
}

七、CRUD 核心知识点与返回值总结

下表总结了四种基础操作所对应的 SQL 模板、关键返回字段及其在业务判断中的具体含义：