Node.js 连接池、参数查询与事务
🎯 引言
学完这篇文章,你能在 Node.js 中安全读写 MySQL,并在同一连接完成事务。
本篇查询和修改 blog_demo.user 中的小明,所用记录如下(其他字段省略):
| id | username |
|---|---|
| 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:
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:
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
保留前文数据时,脚本输出对象数组。把其中的数据行展开成表格,就是:
| id | username |
|---|---|
| 1 | 小明 |
查不到时是空数组,没有数据行。mysql2 返回的第一项是数据行,第二项是字段信息,这里只取数据行。
execute 使用预处理语句,把 SQL 结构和参数值分开处理。问号表示一个数据值,第二个参数中的 1 对应这个问号。用户输入即使含有 SQL 文字,也只作为值使用。
问号不能代替表名、列名或排序方向;动态排序必须从后端允许的固定字段中选择。不要先拼好用户输入,再把它交给 execute。
✏️ 写入结果不是数据行
在独立脚本中可以这样检查更新是否匹配目标:
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 使用同一连接:
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,先导入并借出连接:
import { pool } from './db.mjs';
import { changeCity } from './change-city.mjs';
const connection = await pool.getConnection();
同一文件继续添加一个完整的事务流程:
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 验证成功。
// 请在这里编写代码或记录验证结果
