MySQL Node.js 连接与使用

Node.js 操作 MySQL 主流有两个包：

1. mysql2：推荐，性能更好，支持 Promise / async await，支持预处理语句，分为回调版和Promise版；

2. mysql：旧版包，不再推荐新项目使用，只支持回调。

本文全程使用 mysql2，采用 async/await 现代写法，覆盖基础连接、连接池、CRUD、预处理防注入、事务、错误处理与调试、性能优化等全场景实战内容，适配生产项目开发。

本教程使用到的 Websites 表 SQL 文件：websites.sql。

一、环境准备

1. 安装 mysql2

npm install mysql2

二、基础连接（单次连接，不推荐生产）

单次连接：每次操作新建连接，用完关闭，适合简单脚本、测试场景；高并发Web项目必须使用连接池，避免频繁创建销毁连接造成性能损耗。

const mysql = require('mysql2/promise');
async function testConnect() {
  // 创建连接
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '你的数据库密码',
    database: 'test_db',
    charset: 'utf8mb4'
  });
  console.log('数据库连接成功');
  // 执行查询
  const [rows, fields] = await connection.query('SELECT * FROM users LIMIT 10');
  console.log(rows);
  // 关闭连接
  await connection.end();
}
testConnect().catch(err => console.error(err));

三、连接池（生产环境推荐 ✅）

连接池预先初始化固定数量数据库连接，实现连接复用，规避频繁创建、销毁连接的资源开销，是所有Node.js线上项目的标准方案。

const mysql = require('mysql2/promise');// 创建连接池
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: '你的数据库密码',
  database: 'test_db',
  charset: 'utf8mb4',
  waitForConnections: true,
  connectionLimit: 10,  // 最大连接数
  queueLimit: 0         // 排队请求数限制，0为无限制
});
// 测试连接
async function testPool() {
  const [rows] = await pool.query('SELECT 1 + 1 AS result');
  console.log(rows[0].result);
}
testPool().catch(console.error);
// 导出连接池，供项目其他模块统一调用
module.exports = pool;

四、CRUD 增删改查（标准实战写法）

⚠️ 核心安全原则：禁止直接拼接用户输入到SQL字符串，严格使用 ? 占位符，彻底杜绝SQL注入漏洞。

4.1 查询 SELECT

const pool = require('./db');
async function getUserList() {
  // 占位符传参，参数统一放入数组
  const [rows] = await pool.query('SELECT id,name,email FROM users WHERE age > ?', [18]);
  return rows;
}
getUserList().then(list => console.log(list));

4.2 插入 INSERT

async function addUser() {
  const sql = 'INSERT INTO users(name,email,age) VALUES (?,?,?)';
  const [result] = await pool.query(sql, ['小明', 'xm@test.com', 22]);
  console.log('新增数据ID：', result.insertId);
}
addUser();

4.3 更新 UPDATE

async function updateUser() {
  const sql = 'UPDATE users SET name = ? WHERE id = ?';
  const [result] = await pool.query(sql, ['小红', 1]);
  console.log('受影响行数：', result.affectedRows);
}
updateUser();

4.4 删除 DELETE

async function deleteUser() {
  const sql = 'DELETE FROM users WHERE id = ?';
  const [result] = await pool.query(sql, [1]);
  console.log('删除行数：', result.affectedRows);
}
deleteUser();

五、预处理语句 Prepared Statement（高强度防注入）

pool.query 底层已封装预处理逻辑，适合普通场景；高频重复执行的SQL，手动预处理可进一步提升性能和安全性。

async function preparedDemo() {
  const [stmt] = await pool.prepare('SELECT * FROM users WHERE id = ?');
  const [rows] = await stmt.execute([2]);
  console.log(rows);
  // 关闭预处理语句，释放资源
  await stmt.close();
}
preparedDemo();

六、数据库事务（原子操作保障）

仅InnoDB引擎支持事务，适用于转账、订单创建、数据联动修改等需要要么全部成功、要么全部失败的业务场景。事务必须独占单个连接，不可复用连接池公共连接。

async function transactionDemo() {
  const conn = await pool.getConnection();
  try {
    // 开启事务
    await conn.beginTransaction();

    // 执行多条关联SQL
    await conn.query('UPDATE account SET balance = balance - 100 WHERE id = ?', [1]);
    await conn.query('UPDATE account SET balance = balance + 100 WHERE id = ?', [2]);

    // 无异常则提交事务
    await conn.commit();
    console.log('事务提交成功');
  } catch (err) {
    // 异常则回滚所有操作
    await conn.rollback();
    console.error('事务执行失败，已回滚：', err);
  } finally {
    // 释放连接，归还连接池
    conn.release();
  }
}
transactionDemo();

七、常用实用技巧

1. 批量插入数据

async function batchInsert() {
  const data = [
    ['A', 'a@test.com', 20],
    ['B', 'b@test.com', 21]
  ];
  const sql = 'INSERT INTO users(name,email,age) VALUES ?';
  await pool.query(sql, [data]);
}
batchInsert();

2. 获取数据表字段元信息

pool.query 返回第二个参数为字段元数据，可获取字段名、类型、长度等信息。

const [rows, fields] = await pool.query('SELECT * FROM users');
console.log('字段名称：', fields[0].name);
console.log('字段类型：', fields[0].type);

八、错误处理与调试（实战必备）

Node.js操作MySQL的绝大多数线上问题，都可通过规范的错误捕获、日志打印、调试定位解决。本节统一规范错误处理逻辑，规避线上崩溃、异常无感知问题。

8.1 全局统一错误捕获规范

所有数据库操作必须包裹 try/catch，禁止裸奔执行SQL，避免单条SQL异常导致整个接口/服务崩溃。

async function safeQuery() {
  try {
    const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [999]);
    return rows;
  } catch (err) {
    // 结构化打印错误信息
    console.error('【MySQL查询失败】', {
      message: err.message,
      code: err.code,
      sqlState: err.sqlState,
      sql: err.sql
    });
    // 可根据错误类型自定义业务提示
    throw new Error('数据库查询异常，请稍后重试');
  }
}

8.2 常见MySQL错误码与解决方案

错误码	错误原因	解决方案
ER_ACCESS_DENIED_ERROR	账号密码错误、主机无访问权限	核对数据库账号密码，授权远程访问权限
ER_BAD_DB_ERROR	数据库不存在	核对数据库名，手动创建对应库
ER_NO_SUCH_TABLE	数据表不存在	核对表名，确认表是否创建、库是否选择正确
ER_DUP_ENTRY	唯一字段重复插入	判断数据是否已存在，做去重处理
ER_DATA_TOO_LONG	字段值超出长度限制	截断超长内容或修改数据表字段长度
ETIMEDOUT	连接超时、数据库服务不可达	检查数据库服务状态、防火墙、连接池配置

8.3 调试核心技巧

1. 打印完整执行SQL：报错时打印 err.sql，直接复制到数据库客户端执行，快速定位语法、数据问题；

2. 开启慢查询日志：MySQL端开启慢查询，记录执行超时的SQL，排查低效语句；

3. 开发环境打印完整错误栈，生产环境隐藏详细报错，仅记录日志，避免信息泄露；

4. 事务异常强制回滚：事务场景必须在catch中执行rollback，避免死锁、数据脏写。

九、性能优化建议（生产线上最优实践）

9.1 连接池优化（核心优化点）

1. 合理设置 connectionLimit：根据服务器配置调整，常规4核8G服务器设置10-20，避免连接数过多导致数据库阻塞；

2. 开启 waitForConnections: true：连接耗尽时请求排队，直接拒绝请求，防止服务雪崩；

3. 禁止单次连接混用：所有接口、业务统一使用连接池，杜绝createConnection 高频创建连接。

9.2 SQL语句优化

1. 禁止 SELECT *：只查询业务需要的字段，减少数据传输和内存占用；

2. 分页查询必加 LIMIT：避免全表查询，大数据量表必须分页；

3. WHERE条件字段建立索引：高频查询、筛选、排序、分组字段优先建索引；

4. 避免模糊查询前置通配符（%xxx）：会导致索引失效，全表扫描；

5. 批量操作代替循环单条操作：批量插入、批量更新，减少数据库请求次数。

9.3 代码层级优化

1. 公共连接池统一封装：全局唯一连接池实例，不重复创建；

2. 高频静态数据缓存：字典、配置等不常变动数据，用内存/Redis缓存，避免重复查库；

3. 事务精简：事务内只保留核心SQL，减少事务执行时长，降低锁等待、死锁概率；

4. 及时释放连接：事务、自定义连接使用完毕必须release/end，避免连接泄露占满连接池。

9.4 数据库配置优化

1. 统一编码 utf8mb4，避免乱码问题，无需重复转换编码；

2. 开启MySQL慢查询监控，定时优化低效SQL；

3. 生产环境关闭多余日志，平衡性能和可排查性；

4. 大表定期分表、分区，避免单表数据量过大导致查询卡顿。

十、安全注意事项

1. 永远使用占位符 ? 传参，严禁字符串拼接SQL，彻底防御SQL注入；

2. 数据库账号遵循最小权限原则，业务账号仅授予增删改查权限，禁止授予DROP、ALTER等高风险权限；

3. 数据库密码存入环境变量（process.env）或配置文件，禁止硬编码到代码、上传仓库；

4. 生产环境关闭详细错误输出到前端，仅后台记录日志；

5. 定期备份数据库，搭配binlog实现时间点恢复。

十一、mysql 与 mysql2 对比总结

npm包	Promise支持	性能	推荐度
mysql	需手动封装	一般	❌ 仅老项目维护
mysql2	原生支持async/await	优异（比mysql快2倍+）	✅ 新项目首选

十二、TypeScript 精简示例

import mysql from 'mysql2/promise';
const pool = mysql.createPool({
  host:'localhost',
  user:'root',
  password:'你的密码',
  database:'test_db',
  charset: 'utf8mb4'
});
async function demo() {
  const [rows] = await pool.query('SELECT * FROM users LIMIT 10');
  console.log(rows);
}
demo();