3.MySQL 数据库集成

核心目标

掌握 Node.js 连接 MySQL、基本 CRUD 操作，结合 Express 编写数据库接口。

MySQL 基础与环境准备

MySQL 入门

MySQL 是什么？

• 是能按规则存数据、快速查数据、改数据，还能防止数据丢 / 乱.
• 关系型：数据之间可建立关联。
• 核心优势：数据结构化（表格形式）、支持复杂查询（比如 “查近 7 天买过牛奶的会员”）、数据安全（可备份、权限控制）。

MySQL 核心基本概念（库、表、字段）：

SQL 语句：操作 MySQL 的 “指令”

SQL（结构化查询语言）是和 MySQL 对话的 “语言”，你输入指令，MySQL 执行对应的操作（增 / 删 / 改 / 查数据）。

1. 查（最常用）：SELECT → 找数据

SELECT * FROM 会员表 WHERE 姓名 = '张三';

2. 增：INSERT → 加数据

INSERT INTO 会员表 (ID, 姓名, 电话) VALUES (3, '王五', '137xxxx9999');

3. 改：UPDATE → 改数据

UPDATE 会员表 SET 电话 = '138xxxx4321' WHERE ID = 1;

4. 删：DELETE → 删数据

DELETE FROM 会员表 WHERE ID = 3;

建库：CREATE DATABASE 超市数据;

建表：CREATE TABLE 会员表 (ID INT, 姓名 VARCHAR(20), 电话 VARCHAR(11));

和 Express 结合的核心逻辑

整体流程：环境准备 → 创建 MySQL 库/表 → 搭建 Express 项目 → 配置 MySQL 连接 → 编写 CRUD 接口 → 测试接口

配置 MySQL 连接（核心桥梁）

将 Express 项目和 MySQL 打通，核心是创建「连接池」（复用连接，性能更高）。

1 新建数据库连接模块（db/index.js）+封装事务通用逻辑。

npm i mysql2 用 Promise 版本（适配 async/await）

npm i dotenv 环境变量管理

// 引入依赖 const mysql = require('mysql2/promise'); const dotenv = require('dotenv'); // 加载环境变量 dotenv.config({ path: './config/.env' }); // 数据库连接配置（从环境变量读取） const dbConfig = { host: process.env.DB_HOST, port: process.env.DB_PORT, user: process.env.DB_USER, password: process.env.DB_PASSWORD, database: process.env.DB_NAME, charset: process.env.DB_CHARSET, connectionLimit: process.env.DB_CONNECTION_LIMIT, // 连接池最大连接数 waitForConnections: true, // 连接池满时等待 queueLimit: 0 // 等待队列无上限 }; // 创建连接池（核心：复用连接，提升性能） const pool = mysql.createPool(dbConfig); // 封装通用查询方法（所有 SQL 操作都通过这个方法，统一处理） // execute连接池中处理sql的方法 async function query(sql, params = []) { try { const [rows] = await pool.execute(sql, params); return { success: true, data: rows }; } catch (err) { console.error('SQL 执行失败：', sql, params, err.message); return { success: false, error: err.message }; } } // 通用事务逻辑方法 async function executeTransaction(tasks) { let connection; // 声明独立连接变量（原因：事务必须用独立连接，避免多请求共享连接导致事务混乱） try { // 从连接池获取独立连接（原因：连接池默认是共享连接，事务需要独占连接） connection = await pool.getConnection(); // 开启事务（原因：关闭MySQL的自动提交模式，后续SQL需手动提交） await connection.beginTransaction(); // 批量执行事务任务（原因：按顺序执行所有跨表操作，保证逻辑顺序） const results = []; // 存储每个SQL的执行结果（便于后续排查） for (const task of tasks) { // 执行单个SQL（使用?占位符，原因：防止SQL注入，这是安全必备） const [rows] = await connection.execute(task.sql, task.params); results.push(rows); // 记录结果 } // 提交事务（原因：所有SQL执行成功，确认修改生效） await connection.commit(); // 返回成功结果（results包含每个SQL的执行结果，便于业务层判断） return { success: true, data: results }; } catch (err) { // 事务回滚（原因：任意SQL执行失败，撤销所有已执行的修改，保证原子性） if (connection) { // 先判断连接是否获取成功（避免空指针） await connection.rollback(); } // 返回失败结果（包含错误信息，便于业务层返回友好提示） return { success: false, error: err.message }; } finally { // 释放连接（原因：无论成功/失败，必须释放连接到连接池，否则连接池会被耗尽） if (connection) { connection.release(); } } } // 导出连接池和通用查询方法 module.exports = { pool, query, executeTransaction };

其中：const [rows] = await pool.execute(sql, params);中的rows是mysql2的特定返回结果载体pool.execute()的返回值是一个数组。

补充：事务通俗来说就是，我们在方法中使用sql语句来操作表1时（例如删除用户），表2 和表3中的某条数据受影响了（例如他们都是基于用户的数据），就需要在操作表1之前先把表2和表三的相关数据进行操作（例如删除），删除成功，大家都删除，只要有一个删除失败，就回滚恢复原样都不删除。这样一个过程就叫事务。

先理解事务基础逻辑，再去进行使用封装。

不同的SQL 类型下，rows 的具体含义不同：

1. 读操作（SELECT）：rows 是「查询结果数组」

// 执行查询 SQL const sql = 'SELECT id, name FROM user WHERE age > ?'; const params = [18]; const [rows] = await pool.execute(sql, params); // rows 是数组，每个元素是一行数据（对象） console.log(rows); // 输出示例：[{id: 1, name: '张三'}, {id: 2, name: '李四'}] console.log(rows.length); // 2（查询到2行） // 你的返回值：{success: true, data: [{id:1, name:'张三'}, ...]} return { success: true, data: rows };

2. 写操作（INSERT）：rows 是「执行结果对象」

# 核心包含 affectedRows（插入的行数）和 insertId（自增主键 ID），无实际数据行。 // 执行插入 SQL const sql = 'INSERT INTO user(name, age) VALUES(?, ?)'; const params = ['赵六', 20]; const [rows] = await pool.execute(sql, params); // rows 是执行结果对象 console.log(rows); // 输出示例：{ // affectedRows: 1, // 插入了1行 // insertId: 102, // 自增ID为102 // fieldCount: 0, // serverStatus: 2, // warningCount: 0, // ... // } // 你的返回值：{success: true, data: {affectedRows:1, insertId:102}} return { success: true, data: rows };

3. 写操作（UPDATE）：rows 是「执行结果对象」

# 核心是 affectedRows（实际更新的行数）—— 即使 SQL 执行成功，若没有匹配的记录 / 字段值未变化，affectedRows 可能为 0。 // 执行更新 SQL const sql = 'UPDATE user SET age = ? WHERE id = ?'; const params = [21, 102]; const [rows] = await pool.execute(sql, params); // rows 是执行结果对象 console.log(rows); // 输出示例：{ // affectedRows: 1, // 更新了1行 // changedRows: 1, // 实际修改的字段行数（区别于affectedRows） // warningCount: 0, // ... // } // 你的返回值：{success: true, data: {affectedRows:1, changedRows:1}} return { success: true, data: rows };

4. 写操作（DELETE）：rows 是「执行结果对象」

// 执行删除 SQL const sql = 'DELETE FROM user WHERE id = ?'; const params = [102]; const [rows] = await pool.execute(sql, params); // rows 是执行结果对象 console.log(rows); // 输出示例：{affectedRows: 1, warningCount: 0, ...} // 你的返回值：{success: true, data: {affectedRows:1}} return { success: true, data: rows };

2 业务封装层

调用事务

const { executeTransaction } = require('../config/db'); class UserDb { //删除 static async deleteUserWithRelations(userId) { // 前置校验1：判断用户是否存在（原因：先校验非事务操作，减少事务执行时间，避免长事务锁表） const [userExist] = await pool.execute('SELECT id FROM user WHERE id = ?', [userId]); if (userExist.length === 0) { return { success: false, error: '用户不存在，无需删除' }; } // 前置校验2：格式化参数（原因：确保userId是数字，避免SQL执行异常） const targetUserId = Number(userId); if (isNaN(targetUserId)) { return { success: false, error: '用户ID格式错误' }; } // 定义事务任务数组（核心：按“先删关联数据，后删主数据”的顺序，原因：避免外键约束报错） const transactionTasks = [ // 任务1：删除该用户的所有物品（原因：先删子表，再删主表，避免外键约束阻止删除） { sql: 'DELETE FROM goods WHERE user_id = ?', // 精准删除该用户的物品 params: [targetUserId] // 占位符参数（防注入） }, // 任务2：删除该用户的所有朋友（原因：同理，先删关联数据） { sql: 'DELETE FROM frieds WHERE user_id = ?', params: [targetUserId] }, // 任务3：删除用户本身（原因：最后删主表，确保关联数据已清理） { sql: 'DELETE FROM user WHERE id = ?', params: [targetUserId] } ]; // 调用通用事务方法（原因：复用事务逻辑，避免重复写try/catch/回滚） const transactionResult = await executeTransaction(transactionTasks); // 处理事务结果（原因：给业务层返回清晰的执行状态） if (transactionResult.success) { // 解析每个任务的执行结果（便于前端/日志展示） const [goodsDeleteRes, relativeDeleteRes, userDeleteRes] = transactionResult.data; return { success: true, msg: '用户及关联数据删除成功', data: { deletedGoodsCount: goodsDeleteRes.affectedRows, // 删除物品数量 deletedRelativeCount: relativeDeleteRes.affectedRows, // 删除朋友数量 deletedUserCount: userDeleteRes.affectedRows // 删除用户数量（正常是1） } }; } else { // 事务失败，返回错误信息（原因：便于排查问题，如外键冲突、SQL错误） return { success: false, error: transactionResult.error }; } } } // 导出类（原因：模块化封装，路由层只需调用方法，无需关心SQL细节） module.exports = UserDb;

class（类）+static（静态方法：挂载到类）的核心目的是：在 “模块化封装” 的基础上，兼顾代码的可读性、可维护性、扩展性，同时避免无意义的实例化开销。

3 路由层调用

const express = require('express'); const router = express.Router(); const UserDb = require('../db/userDb');//业务封装逻辑地址 const { success, error } = require('../utils/response'); // 统一响应封装：成功失败的逻辑封装 router.delete('/:id', async (req, res) => { try { //获取URL中的用户ID（原因：RESTful风格，路径参数传递资源ID） const userId = parseInt(req.params.id); //基础参数校验（原因：提前过滤无效参数，减少数据库请求） if (isNaN(userId) || userId <= 0) { return error(res, '用户ID必须为正整数', 400); } //调用业务层的事务方法（原因：路由层只做请求分发，不写业务逻辑） const result = await UserDb.deleteUserWithRelations(userId); //处理返回结果（统一响应格式，原因：前端无需适配不同的返回结构） if (result.success) { return success(res, result.data, result.msg); } // 区分业务错误和系统错误（原因：返回不同的HTTP状态码，便于前端处理） if (result.error.includes('用户不存在')) { return error(res, result.error, 404); // 404：资源不存在 } else { return error(res, `删除失败：${result.error}`, 500); // 500：服务器错误 } } catch (err) { // 全局异常捕获（原因：防止未处理的异常导致服务崩溃） console.error('删除用户接口异常：', err.stack); return error(res, '服务器内部错误', 500, err.message); } }); module.exports = router;

4 自动路由挂载工具

前面的核心逻辑 如果有多个就会有多个路由文件。每个路由文件独立封装对应模块的接口，统一导出express.Router()实例。这时候可以封装一个方法自动挂载。
使用工具：fs（nodejs的内置模块，封装了操作系统文件的能力，直接使用，无需安装）

const fs = require('fs'); const path = require('path'); const express = require('express'); function loadRouters(app) { // 获取routes目录的绝对路径（兼容不同系统） const routesDir = path.resolve(__dirname, '../routes'); // 读取routes目录下所有文件 fs.readdirSync(routesDir).forEach((file) => { // 过滤非.js文件、隐藏文件（如 .DS_Store） if (!file.endsWith('.js') || file.startsWith('.')) return; // 提取模块名（如 goodsOwnedRouter.js → goods-owned） // 规则：去掉Router.js后缀，驼峰转连字符（符合RESTful路径规范） const moduleName = file.replace('Router.js', '') .replace(/([A-Z])/g, '-$1') // 驼峰转连字符（GoodsOwned → goods-owned） .toLowerCase() // 转小写 .replace(/^-/, ''); // 去掉开头的- // 拼接路由文件路径，引入Router实例 const routerPath = path.join(routesDir, file); const router = require(routerPath); // 自动挂载路由：前缀 /api/模块名（如 /api/user、/api/goods-owned） const apiPrefix = `/api/${moduleName}`; app.use(apiPrefix, router); }); } module.exports = { loadRouters };

5 入口文件配置 app.js

统一整合中间件、路由自动挂载、数据库连接、端口监听，是项目的 “总开关”。

const express = require('express'); const dotenv = require('dotenv'); const { testDbConnection } = require('./config/db'); const { loadRouters } = require('./utils/routerLoader'); // 路由自动挂载 const { success, error } = require('./utils/response'); // 统一响应 // 加载环境变量（端口、数据库信息） dotenv.config({ path: `.env.${process.env.NODE_ENV || "development"}` }); // 创建Express实例 const app = express(); const PORT = process.env.PORT || 3000; // 优先读环境变量，默认3000 // 通用中间件配置（必须放在路由挂载前！） app.use(express.json()); // 解析JSON请求体 app.use(express.urlencoded({ extended: true })); // 解析表单请求体 // 全局错误处理中间件（捕获所有路由的异常） app.use((err, req, res, next) => { console.error('全局错误：', err.stack); error(res, '服务器内部错误', 500, err.message); }); // 启动流程（先连数据库，再挂载路由，最后监听端口） async function startServer() { try { //测试数据库连接（失败则退出） await testDbConnection(); // 自动挂载所有路由（核心！无需手动app.use） loadRouters(app); //监听端口（必须！否则接口无法访问） app.listen(PORT, () => { console.log("监听端口"); }); } catch (err) { console.error('❌ 服务启动失败：', err.message); process.exit(1); // 退出进程 } } // 执行启动 startServer();

6 环境变量配置

.env.development

# 开发环境配置 DB_HOST=localhost DB_USER=root DB_PASSWORD=root DB_DATABASE=text01 DB_PORT=3306 PORT= 3000 # 额外开发配置：比如调试模式、日志级别 NODE_ENV=development LOG_LEVEL=debug

.env.production

# 生产环境配置 # 生产数据库地址（云服务器/内网IP） DB_HOST=10.0.0.5 # 生产专用账号（非root） DB_USER=prod_express # 高强度密码 DB_PASSWORD=Pro@Db123456! # 生产正式库 DB_DATABASE=prod_ecommerce DB_PORT=3306 # 生产端口（HTTP默认80） PORT=80 # 生产配置：关闭调试、日志级别为error NODE_ENV=production LOG_LEVEL=error

7 统一响应封装response.js

const success = (res, data = null, msg = "操作成功", code = 200) => { // 确保status是合法的HTTP状态码（避免传入非标准码导致报错） const validStatus = [200, 201, 204].includes(code) ? code : 200; res.status(validStatus).json({ code, // 业务码（可自定义，如20000） msg, data, success: true, timestamp: new Date().getTime(), // 新增：响应时间戳（便于排查问题） }); }; const error = (res, msg = "操作失败", code = 500, error = null) => { // 区分HTTP状态码和业务码：HTTP状态码只能是标准值（400/404/500等） let httpStatus = code; // 校验HTTP状态码合法性，非法则默认500 if (![400, 401, 403, 404, 409, 500].includes(httpStatus)) { httpStatus = 500; } res.status(httpStatus).json({ code, // 业务码（可自定义，如50000） msg, // 生产环境隐藏错误详情，且错误信息转成字符串（避免循环引用） error: process.env.NODE_ENV === "development" ? (error instanceof Error ? error.stack : String(error)) : null, success: false, timestamp: new Date().getTime(), // 新增：响应时间戳 }); }; module.exports = { success, error, };