Node中操作MySQL

MariaH
1 阅读6分钟

前言

前面我们学习了MySQL单表、多表、一对多、多对多SQL语法，实际项目中不会手动在Navicat等工具执行SQL，而是通过后端代码操作数据库。Node.js生态主流使用 mysql2 库操作MySQL，相比老旧的mysql库性能更强、支持预编译防注入、原生Promise、连接池等企业级能力。

本文结合前文商品-品牌一对多、学生-课程多对多案例，完整讲解：

  1. MySQL JSON_OBJECT/JSON_ARRAYAGG 实现联表结构化返回对象/数组
  2. mysql2 基础使用、预编译语句Prepared Statement
  3. 连接池 Connection Pool 生产环境标准方案
  4. async/await Promise 异步写法
    文中所有SQL、JS代码全部保留文档原版示例，可直接复制运行。

一、多表查询痛点：字段平铺混乱，JSON函数格式化返回数据

1. 普通LEFT JOIN查询问题

执行联表语句 SELECT * FROM products LEFT JOIN brand ON products.brand_id = brand.id;
查询结果会把商品、品牌所有字段平铺在同一行，两张表同名字段（id）冲突，前端解析数据极其麻烦。

2. JSON_OBJECT()：一对多查询，子表转为对象

需求：查询商品，品牌信息封装为一个JSON对象，避免字段平铺。

完整SQL示例

SELECT 
  products.id as id, 
  products.title as title, 
  products.price as price, 
  products.score as score, 
  JSON_OBJECT(
    'id', brand.id, 
    'name', brand.name, 
    'rank', brand.phoneRank, 
    'website', brand.website
  ) as brand 
FROM products 
LEFT JOIN brand ON products.brand_id = brand.id;

返回结果结构

idtitlepricescorebrand
1华为nova3(全网通)26996.7{"id": 100, "name": "华为", "rank": 2, "website": "www.huawei.com"}
2华为P20Pro (6GB RAM/全网通)44888.3{"id":100,"name":"华为","rank":2,"website": "www.huawei.com"}

后端拿到数据后，直接JSON.parse(row.brand)即可得到品牌对象，结构清晰，适配前端接口返回格式。

3. JSON_ARRAYAGG + JSON_OBJECT：多对多查询，子数据转为数组

场景：学生和课程多对多，一个学生对应多门课程，需要把课程封装成数组。

完整SQL示例

SELECT 
  stu.id, 
  stu.name, 
  stu.age, 
  JSON_ARRAYAGG(
    JSON_OBJECT('id', cs.id, 'name', cs.name)
  ) as courses 
FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id 
LEFT JOIN courses cs ON ssc.course_id = cs.id 
GROUP BY stu.id;

返回结果示例

idnameagecourses
1why18[{"id":1,"name":"英语"},{"id": 3,"name":"数学"}]
2tom22[{"id": null, "name": null}]
3lilei25[{"id": 2,"name":"语文"},{"id":3,"name":"数学"},{"id":4,"name":"历史"}]

多对多场景下，这条SQL完美实现一条SQL查询出学生+全部选课数组，无需多次循环查询数据库。

二、mysql2 库介绍与基础使用

1. mysql 和 mysql2 对比

  • mysql：早期Node MySQL驱动，性能差，无原生预编译、Promise支持差；
  • mysql2：完全兼容mysql API，优势：
    1. 执行速度、底层性能大幅优化；
    2. 支持预编译语句Prepared Statement，提升性能+防御SQL注入；
    3. 原生支持Promise，可使用async/await；
    4. 内置连接池，适配高并发服务。

2. 安装依赖

npm install mysql2

3. 基础单连接 createConnection 使用

创建单次数据库连接，执行SQL（适合简单测试，不推荐生产环境）

const mysql = require('mysql2');

// 1. 创建连接
const connection = mysql.createConnection({
  host: 'localhost',
  database: 'coderhub',
  user: 'root',
  password: 'Coderwhy888.'
});

// 2. 执行普通查询
const sql = 'SELECT * FROM products';
connection.query(sql, (err, results) => {
  if (err) {
    console.error('查询失败：', err);
    return;
  }
  console.log('查询结果：', results);
});

三、预编译语句 Prepared Statement（核心安全优化）

预编译两大核心优势

  1. 性能提升：SQL模板仅编译一次，多次传参重复执行，省去重复解析优化开销；
  2. 杜绝SQL注入：参数和SQL模板分离，传入的内容不会被数据库解析为SQL逻辑，or 1=1 这类注入语句失效。

代码示例：execute 执行预编译SQL

使用 ? 作为占位符，参数通过数组传递

const mysql = require('mysql2');
const connection = mysql.createConnection({
  host: 'localhost',
  database: 'coderhub',
  user: 'root',
  password: 'Coderwhy888.'
});

// 预编译SQL模板，? 占位符
const statement = 'SELECT * FROM products WHERE price > ? and brand = ?;';
// 执行语句，第二个数组对应占位符参数
connection.execute(statement, [1000, '华为'], (err, results) => {
  if (err) throw err;
  console.log('华为千元以上手机：', results);
});

四、连接池 Connection Pool（生产环境标准方案）

为什么要用连接池？

  1. 单连接同一时间只能处理一个请求，并发场景阻塞；
  2. 频繁创建/销毁数据库连接开销巨大；
  3. 连接池预先创建一批连接，请求自动复用，空闲连接缓存复用，支持配置最大连接数限流。

1. 创建连接池基础代码

const mysql = require('mysql2');

// 创建连接池
const pool = mysql.createPool({
  host: 'localhost',
  database: 'coderhub',
  user: 'root',
  password: 'Coderwhy888.',
  connectionLimit: 5 // 最大并发连接数
});

// 使用连接池执行预编译查询
const statement = 'SELECT * FROM products WHERE price > ? and brand = ?;';
pool.execute(statement, [1000, '华为'], (err, results) => {
  if (err) throw err;
  console.log('连接池查询结果：', results);
});

五、Promise + async/await 现代化写法

回调地狱难以维护，mysql2 提供 .promise() API，支持Promise、async/await，适配现代Node项目。

1. .then() 链式调用写法

const mysql = require('mysql2');
const pool = mysql.createPool({
  host: 'localhost',
  database: 'coderhub',
  user: 'root',
  password: 'Coderwhy888.',
  connectionLimit: 5
});

const statement = 'SELECT * FROM products WHERE price > ? and brand = ?;';
pool.promise().execute(statement, [1000, '华为'])
  .then(([results, fields]) => {
    console.log('查询数据：', results);
  })
  .catch(err => {
    console.error('查询异常：', err);
  });

2. async/await 同步风格（推荐项目使用）

const mysql = require('mysql2');
const pool = mysql.createPool({
  host: 'localhost',
  database: 'coderhub',
  user: 'root',
  password: 'Coderwhy888.',
  connectionLimit: 5
});

// 封装查询函数
async function queryHuaweiPhone() {
  const statement = 'SELECT * FROM products WHERE price > ? and brand = ?;';
  const [results] = await pool.promise().execute(statement, [1000, '华为']);
  return results;
}

// 调用
queryHuaweiPhone().then(data => console.log(data));

六、完整业务场景整合示例

场景1：查询商品并返回结构化品牌对象（JSON_OBJECT）

async function getProductWithBrand() {
  const sql = `
    SELECT 
      products.id as id, 
      products.title as title, 
      products.price as price, 
      products.score as score, 
      JSON_OBJECT(
        'id', brand.id, 
        'name', brand.name, 
        'rank', brand.phoneRank, 
        'website', brand.website
      ) as brand 
    FROM products 
    LEFT JOIN brand ON products.brand_id = brand.id
  `;
  const [rows] = await pool.promise().query(sql);
  // 解析JSON字符串为JS对象
  const res = rows.map(item => {
    item.brand = JSON.parse(item.brand);
    return item;
  });
  console.log(res);
}

场景2：查询学生+选课数组（JSON_ARRAYAGG多对多）

async function getStudentWithCourse() {
  const sql = `
    SELECT 
      stu.id, 
      stu.name, 
      stu.age, 
      JSON_ARRAYAGG(
        JSON_OBJECT('id', cs.id, 'name', cs.name)
      ) as courses 
    FROM students stu
    LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id 
    LEFT JOIN courses cs ON ssc.course_id = cs.id 
    GROUP BY stu.id
  `;
  const [rows] = await pool.promise().query(sql);
  const res = rows.map(item => {
    item.courses = JSON.parse(item.courses);
    return item;
  });
  console.log(res);
}

七、全文总结

  1. SQL结构化返回
    • JSON_OBJECT：一对多联表，子表数据封装为对象，解决字段平铺冲突；
    • JSON_ARRAYAGG + JSON_OBJECT：多对多场景，子数据聚合为数组，单SQL完成一对多完整数据查询。
  1. mysql2 核心能力
    • 优于老旧mysql库，性能更高、功能更完善；
    • execute() 预编译语句：防SQL注入、重复查询性能更好；
    • 连接池createPool：生产环境必备，解决并发连接性能问题；
    • .promise() 支持async/await，摆脱回调地狱，代码可读性更强。
  1. 开发规范建议
    • 线上项目一律使用连接池，禁止单连接createConnection；
    • 所有动态参数查询必须使用预编译execute，杜绝SQL注入风险；
    • 多表联表返回时使用JSON函数格式化数据，后端统一解析JSON字符串，直接返回结构化数据给前端。
avatar
文章
阅读
粉丝