Node.js 连接池、参数查询与事务

在 Node.js 中安全读写 MySQL,并在同一连接完成事务

🎯 引言

学完这篇文章,你能在 Node.js 中安全读写 MySQL,并在同一连接完成事务。

本篇查询和修改 blog_demo.user 中的小明,所用记录如下(其他字段省略):

idusername
1小明

用户表的初始化见 建库与增删改。后面的事务还会向 事务篇 建立的 user_change 表追加记录,其中 user_id 保存用户编号,new_city 保存本次改成的城市。


🔐 创建供 Node.js 连接数据库的账号

Node.js 连接 MySQL 时,也要提供账号和密码。 这里创建的 course_app 就是专门供后端程序使用的 MySQL 账号,后面把它填进连接配置,程序才能用它查询和修改数据。

它和 user 表里的“小明”不同:course_app 用来登录数据库;小明是数据库里保存的一条业务数据。 创建 course_app 不会向 user 表插入记录。

为什么不继续使用 root

root 是管理 MySQL 的账号,可以创建账号、分配权限等。后端程序日常只需要读写业务表,因此给它单独创建账号,只开放这些操作。这样程序即使执行了删除数据库的语句,也会因为没有权限而被拒绝。

先用 root 登录,再创建账号

沿用入门篇的 Docker 容器,在终端执行:

docker exec -it mysql-course mysql -uroot -p

这条命令打开容器中的 MySQL 命令行,用 root 登录;-p 表示随后输入 root 密码。输入你创建容器时设置的密码,看到 mysql> 后,再执行下面的 SQL:

CREATE USER 'course_app'@'%' IDENTIFIED BY 'LocalApp_84_only';

这条语句创建一个 MySQL 账号,各部分含义如下:

写法含义
CREATE USER创建数据库登录账号
'course_app'账号名称,后面填入 DB_USER
@'%'允许这个账号从任意来源主机连接,仍需通过密码验证
IDENTIFIED BY 'LocalApp_84_only'设置该账号的密码,后面填入 DB_PASSWORD

执行成功会显示 Query OK。此时账号已经创建,但还没有读取或修改 blog_demo 的权限。 这个账号只需创建一次,不要每次启动 Node.js 都执行。

本地示例用 % 是为了允许经过 Docker 网络的连接;它表示客户端可以来自哪里,不是数据库地址。本课程容器端口只绑定本机 127.0.0.1,部署时应限制来源和网络访问,并替换练习密码。

允许这个账号读写 blog_demo 中的表

继续在当前 root 登录的 mysql> 中执行:

GRANT SELECT, INSERT, UPDATE, DELETE ON blog_demo.* TO 'course_app'@'%';

这条语句的意思是:把 blog_demo 库中所有表的查询、新增、修改、删除记录权限,交给 course_app 账号。

写法允许操作的范围
SELECT、INSERT、UPDATE、DELETE查询、新增、修改、删除表中的记录
ON blog_demo.*仅针对 blog_demo 库中的所有表,星号表示所有表
TO 'course_app'@'%'把这些权限交给刚创建的账号

执行成功同样显示 Query OK。它可以删除用户记录,但没有删除整张表、删除数据库或修改表结构的权限;这些管理操作仍由管理账号完成。

检查权限,再把账号交给 Node.js 使用

仍以 root 身份查看刚才的授权:

SHOW GRANTS FOR 'course_app'@'%';

新建账号并按上面授权后,权限结果的主要内容如下:

Grants for course_app@%
GRANT USAGE ON . TO course_app@%
GRANT SELECT, INSERT, UPDATE, DELETE ON blog_demo.* TO course_app@%

USAGE 本身不授予数据读写权限;看到第二行的四项权限和 blog_demo,才说明已经允许它读写练习库。

接着输入 exit 退出 MySQL 命令行,回到终端。下面安装驱动并配置 Node.js:数据库用户名使用 course_app,密码使用刚设置的 LocalApp_84_only,不再填写 root 的账号和密码。


📦 安装驱动并配置连接

本篇使用 Node.js 24.19.0、mysql2 3.11.5,示例采用 ESM 的 .mjs 文件。mysql2 是 Node.js 连接 MySQL 的驱动:

npm install mysql2@3.11.5

在练习目录建立 .env,填写刚创建的数据库账号。DB_HOST 和 DB_PORT 指定连接哪台 MySQL,DB_USER 和 DB_PASSWORD 用来登录,DB_NAME 指定要使用的数据库:

DB_HOST=127.0.0.1
DB_PORT=3306
DB_USER=course_app
DB_PASSWORD=LocalApp_84_only
DB_NAME=blog_demo

.env 应加入 .gitignore。若之前改用本机 3307 端口,这里也要修改。远程数据库应按服务方配置启用 TLS,即传输加密,而不是关闭证书验证。


🔌 创建可复用的连接池

新建 db.mjs:

db.mjs
import mysql from 'mysql2/promise';

export const pool = 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,
    connectionLimit: 5
});

连接池保存可复用的数据库连接,避免每次查用户都重新连接。connectionLimit 限制这个池同时使用的连接数量,5 是本地练习配置,不是所有项目的推荐值。多个服务进程各有自己的池,总连接数要一起计算。


🔎 用参数查询读取用户

新建 query-user.mjs:

query-user.mjs
import { pool } from './db.mjs';

try {
    const [rows] = await pool.execute(
        'SELECT id, username FROM user WHERE id = ?', [1]
    );
    console.log(rows);
} finally {
    await pool.end();
}

运行:

node --env-file=.env query-user.mjs

保留前文数据时,脚本输出对象数组。把其中的数据行展开成表格,就是:

idusername
1小明

查不到时是空数组,没有数据行。mysql2 返回的第一项是数据行,第二项是字段信息,这里只取数据行。

execute 使用预处理语句,把 SQL 结构和参数值分开处理。问号表示一个数据值,第二个参数中的 1 对应这个问号。用户输入即使含有 SQL 文字,也只作为值使用。

问号不能代替表名、列名或排序方向;动态排序必须从后端允许的固定字段中选择。不要先拼好用户输入,再把它交给 execute。


✏️ 写入结果不是数据行

在独立脚本中可以这样检查更新是否匹配目标:

update-user.mjs
import { pool } from './db.mjs';

try {
    const [result] = await pool.execute(
        'UPDATE user SET city = ? WHERE id = ?', ['苏州', 1]
    );
    console.log(result.affectedRows);
} finally {
    await pool.end();
}

运行方式与查询脚本相同,替换文件名即可。mysql2 默认配置下,UPDATE 的 affectedRows 表示匹配的行数,不能用它判断值一定发生变化;条件包含“旧值与新值不同”等限制时再按该条件解释。INSERT 的 insertId 可以取得新生成的自增编号。


🧾 把两次相关写入放进同一连接

新建 change-city.mjs,先写业务操作。connection 由调用者传入,确保两条 SQL 使用同一连接:

change-city.mjs
export async function changeCity(connection, id, city) {
    const [result] = await connection.execute(
        'UPDATE user SET city = ? WHERE id = ?', [city, id]
    );
    if (result.affectedRows !== 1) throw new Error('用户不存在');
    await connection.execute(
        'INSERT INTO user_change (user_id, new_city) VALUES (?, ?)', [id, city]
    );
}

更新没有匹配用户就抛出错误,不再保存一条不存在用户的变更记录。这是业务判断,不是数据库自己会自动报的错误。


✅ 借出连接后再开始事务

新建 transaction-demo.mjs,先导入并借出连接:

transaction-demo.mjs
import { pool } from './db.mjs';
import { changeCity } from './change-city.mjs';

const connection = await pool.getConnection();

同一文件继续添加一个完整的事务流程:

transaction-demo.mjs
try {
    await connection.beginTransaction();
    await changeCity(connection, 1, '成都');
    await connection.commit();
    console.log('城市和变更记录已保存');
} catch (error) {
    await connection.rollback();
    throw error;
} finally {
    connection.release();
    await pool.end();
}

beginTransaction 开始,changeCity 执行两步写入,commit 一起提交。失败进入 catch,rollback 撤销本次未提交修改,再把错误抛给调用方。finally 无论成功失败都会执行,release 把连接归还给池。

运行 node --env-file=.env transaction-demo.mjs,成功时终端显示“城市和变更记录已保存”。将用户编号改成不存在的编号,再执行,应报“用户不存在”,且不新增变更记录。

这里的 pool.end 是为了让一次性练习脚本结束。HTTP 服务应复用连接池,只在服务退出时关闭,不能每次请求结束就关闭整个池。


❓ 知识问答

Q1:pool.execute 后需要 release 吗?

不需要,它自动借用和归还连接。只有自己 getConnection 借出的连接才需要明确 release。

Q2:可以先 pool.query 开始事务,再 pool.execute 更新吗?

不可以保证它们使用同一连接。事务期间所有操作都应通过借出的 connection 执行。

Q3:query 和 execute 都能使用问号吗?

可以,但机制不同。query 通常由驱动格式化参数,execute 使用服务端预处理;本课程参数读写统一使用 execute。


🧪 小练习

把事务中的用户编号改为 999,确认失败后 user_change 没有增加记录,再恢复编号 1 验证成功。

// 请在这里编写代码或记录验证结果