Node.js MySQL 连接

章节概述

MySQL 是一种流行的关系型数据库管理系统，广泛应用于各种 Web 应用和企业级系统中。在 Node.js 应用中，我们通常使用 mysql2 驱动来与 MySQL 数据库交互，它提供了高效、可靠的数据库连接和查询功能。本集将详细介绍如何在 Node.js 中连接 MySQL，执行 SQL 查询，以及实现一个完整的商品管理系统。

核心知识点讲解

1. MySQL 简介

MySQL 是一个开源的关系型数据库管理系统（RDBMS），它的主要特点包括：

• 关系型数据库：使用表结构存储数据，支持 SQL 查询语言
• 可靠性：提供事务支持和数据完整性保障
• 可扩展性：支持大型数据库和高并发访问
• 安全性：提供用户权限管理和数据加密功能
• 性能：优化的查询执行引擎，支持索引和缓存
• 跨平台：可在多种操作系统上运行

2. mysql2 驱动简介

mysql2 是一个 Node.js 的 MySQL 客户端驱动，是对传统 mysql 驱动的改进版本，提供了更好的性能和更多的特性：

• 更快的性能：比 mysql 驱动有更好的性能表现
• Promise 支持：原生支持 Promise API，便于使用 async/await
• 预处理语句：支持参数化查询，防止 SQL 注入
• 连接池：内置连接池功能，优化数据库连接管理
• SSL 支持：支持安全的 SSL 连接
• 压缩：支持数据压缩，减少网络传输开销

3. 连接 MySQL

首先，需要安装 mysql2 模块：

npm install mysql2

3.1 基本连接

const mysql = require('mysql2');

// 创建数据库连接
const connection = mysql.createConnection({
  host: 'localhost',      // 数据库主机地址
  user: 'root',           // 数据库用户名
  password: 'password',   // 数据库密码
  database: 'mydatabase'  // 数据库名称
});

// 连接数据库
connection.connect((err) => {
  if (err) {
    console.error('连接数据库失败:', err);
    return;
  }
  console.log('成功连接到 MySQL 数据库');
});

// 关闭连接
// connection.end();

3.2 使用 Promise API

const mysql = require('mysql2/promise');

// 创建数据库连接
async function connect() {
  try {
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'mydatabase'
    });
    console.log('成功连接到 MySQL 数据库');
    return connection;
  } catch (err) {
    console.error('连接数据库失败:', err);
    throw err;
  }
}

// 调用连接函数
connect();

3.3 连接池

对于生产环境，建议使用连接池来管理数据库连接，它可以提高性能并优化资源使用：

const mysql = require('mysql2');

// 创建连接池
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydatabase',
  waitForConnections: true,  // 当连接池无可用连接时，等待而不是抛出错误
  connectionLimit: 10,       // 连接池最大连接数
  queueLimit: 0              // 连接池队列限制（0 表示无限制）
});

// 从连接池获取连接
pool.getConnection((err, connection) => {
  if (err) {
    console.error('获取连接失败:', err);
    return;
  }
  console.log('成功获取数据库连接');
  
  // 使用连接执行查询
  // ...
  
  // 释放连接回连接池
  connection.release();
});

// 关闭连接池
// pool.end();

3.4 使用 Promise 连接池

const mysql = require('mysql2/promise');

// 创建 Promise 连接池
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydatabase',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 使用连接池执行查询
async function queryDatabase() {
  try {
    // 直接使用连接池执行查询（内部会自动管理连接）
    const [rows, fields] = await pool.execute('SELECT * FROM users');
    console.log('查询结果:', rows);
  } catch (err) {
    console.error('查询失败:', err);
  }
}

// 调用查询函数
queryDatabase();

4. 执行 SQL 查询

4.1 基本查询

const mysql = require('mysql2/promise');

async function runQuery() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase'
  });
  
  try {
    // 执行 SELECT 查询
    const [rows] = await connection.execute('SELECT * FROM users');
    console.log('查询结果:', rows);
    
    // 执行 INSERT 查询
    const [insertResult] = await connection.execute(
      'INSERT INTO users (name, email) VALUES (?, ?)',
      ['John Doe', 'john@example.com']
    );
    console.log('插入结果:', insertResult);
    
    // 执行 UPDATE 查询
    const [updateResult] = await connection.execute(
      'UPDATE users SET name = ? WHERE id = ?',
      ['Jane Doe', 1]
    );
    console.log('更新结果:', updateResult);
    
    // 执行 DELETE 查询
    const [deleteResult] = await connection.execute(
      'DELETE FROM users WHERE id = ?',
      [1]
    );
    console.log('删除结果:', deleteResult);
  } catch (err) {
    console.error('查询失败:', err);
  } finally {
    // 关闭连接
    await connection.end();
  }
}

runQuery();

4.2 预处理语句

预处理语句（Prepared Statements）是一种参数化查询的方式，可以防止 SQL 注入攻击，并提高查询性能：

// 使用预处理语句执行查询
const [rows] = await connection.execute(
  'SELECT * FROM users WHERE age > ? AND status = ?',
  [18, 'active']
);
console.log('查询结果:', rows);

4.3 事务处理

事务是一组原子性的 SQL 操作，要么全部成功，要么全部失败。在需要确保多个操作的一致性时，应该使用事务：

async function processTransaction() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase'
  });
  
  try {
    // 开始事务
    await connection.beginTransaction();
    
    // 执行第一个操作
    await connection.execute(
      'UPDATE accounts SET balance = balance - ? WHERE id = ?',
      [100, 1]
    );
    
    // 执行第二个操作
    await connection.execute(
      'UPDATE accounts SET balance = balance + ? WHERE id = ?',
      [100, 2]
    );
    
    // 提交事务
    await connection.commit();
    console.log('事务执行成功');
  } catch (err) {
    // 回滚事务
    await connection.rollback();
    console.error('事务执行失败:', err);
  } finally {
    // 关闭连接
    await connection.end();
  }
}

processTransaction();

5. 连接池管理

连接池是一种管理数据库连接的机制，它可以：

• 减少连接建立和关闭的开销
• 控制并发连接数量
• 提高应用程序性能
• 提供连接复用

5.1 连接池配置

const pool = mysql.createPool({
  // 基本配置
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydatabase',
  
  // 连接池配置
  waitForConnections: true,  // 当无可用连接时，等待而不是抛出错误
  connectionLimit: 10,       // 最大连接数
  queueLimit: 0,             // 队列限制（0 表示无限制）
  
  // 其他配置
  charset: 'utf8mb4',        // 字符集
  timezone: 'local',         // 时区
  multipleStatements: false  // 是否允许多条 SQL 语句
});

5.2 连接池事件

// 连接池创建连接时触发
pool.on('connection', (connection) => {
  console.log('新连接创建:', connection.threadId);
});

// 连接被释放回连接池时触发
pool.on('release', (connection) => {
  console.log('连接释放:', connection.threadId);
});

// 连接池获取连接超时触发
pool.on('enqueue', () => {
  console.log('等待可用连接');
});

实用案例分析

案例：商品管理系统

下面我们将使用 Express 和 mysql2 实现一个完整的商品管理系统，支持商品的添加、查询、更新和删除操作。

项目结构

product-management-system/
├── app.js
├── config/
│   └── db.js
├── routes/
│   └── products.js
├── controllers/
│   └── productController.js
├── models/
│   └── productModel.js
├── package.json
└── .env

安装依赖

npm install express mysql2 dotenv

配置文件（.env）

# MySQL 数据库配置
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=password
DB_NAME=product_management

# 服务器端口
PORT=3000

数据库配置（config/db.js）

const mysql = require('mysql2/promise');
require('dotenv').config();

// 创建数据库连接池
const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 测试数据库连接
async function testConnection() {
  try {
    const [rows] = await pool.execute('SELECT 1');
    console.log('数据库连接成功');
  } catch (err) {
    console.error('数据库连接失败:', err);
  }
}

// 导出连接池
module.exports = {
  pool,
  testConnection
};

数据库初始化

在开始之前，需要创建数据库和表结构：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS product_management;

-- 使用数据库
USE product_management;

-- 创建商品表
CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10, 2) NOT NULL,
  quantity INT NOT NULL DEFAULT 0,
  category VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 插入测试数据
INSERT INTO products (name, description, price, quantity, category) VALUES
('智能手机', '最新款智能手机，支持5G网络', 5999.99, 50, '电子产品'),
('笔记本电脑', '高性能笔记本电脑，适合办公和游戏', 8999.99, 30, '电子产品'),
('无线耳机', '高品质无线蓝牙耳机', 1299.99, 100, '音频设备'),
('智能手表', '多功能智能手表，支持健康监测', 2999.99, 40, '穿戴设备'),
('平板电脑', '轻薄便携平板电脑', 3999.99, 25, '电子产品');

商品模型（models/productModel.js）

const { pool } = require('../config/db');

// 商品模型
class Product {
  // 获取所有商品
  static async getAll() {
    const [rows] = await pool.execute('SELECT * FROM products');
    return rows;
  }
  
  // 根据 ID 获取商品
  static async getById(id) {
    const [rows] = await pool.execute('SELECT * FROM products WHERE id = ?', [id]);
    return rows[0] || null;
  }
  
  // 搜索商品
  static async search(keyword) {
    const [rows] = await pool.execute(
      'SELECT * FROM products WHERE name LIKE ? OR description LIKE ? OR category LIKE ?',
      [`%${keyword}%`, `%${keyword}%`, `%${keyword}%`]
    );
    return rows;
  }
  
  // 根据分类获取商品
  static async getByCategory(category) {
    const [rows] = await pool.execute('SELECT * FROM products WHERE category = ?', [category]);
    return rows;
  }
  
  // 创建商品
  static async create(productData) {
    const { name, description, price, quantity, category } = productData;
    const [result] = await pool.execute(
      'INSERT INTO products (name, description, price, quantity, category) VALUES (?, ?, ?, ?, ?)',
      [name, description, price, quantity, category]
    );
    return this.getById(result.insertId);
  }
  
  // 更新商品
  static async update(id, productData) {
    const { name, description, price, quantity, category } = productData;
    await pool.execute(
      'UPDATE products SET name = ?, description = ?, price = ?, quantity = ?, category = ? WHERE id = ?',
      [name, description, price, quantity, category, id]
    );
    return this.getById(id);
  }
  
  // 删除商品
  static async delete(id) {
    const [result] = await pool.execute('DELETE FROM products WHERE id = ?', [id]);
    return result.affectedRows > 0;
  }
  
  // 获取商品数量
  static async count() {
    const [rows] = await pool.execute('SELECT COUNT(*) as count FROM products');
    return rows[0].count;
  }
  
  // 获取分类列表
  static async getCategories() {
    const [rows] = await pool.execute('SELECT DISTINCT category FROM products');
    return rows.map(row => row.category);
  }
}

module.exports = Product;

商品控制器（controllers/productController.js）

const Product = require('../models/productModel');

// 获取所有商品
async function getAllProducts(req, res) {
  try {
    const products = await Product.getAll();
    res.json({ success: true, data: products });
  } catch (error) {
    res.status(500).json({ success: false, message: '获取商品失败', error: error.message });
  }
}

// 根据 ID 获取商品
async function getProductById(req, res) {
  try {
    const { id } = req.params;
    const product = await Product.getById(id);
    if (!product) {
      return res.status(404).json({ success: false, message: '商品不存在' });
    }
    res.json({ success: true, data: product });
  } catch (error) {
    res.status(500).json({ success: false, message: '获取商品失败', error: error.message });
  }
}

// 搜索商品
async function searchProducts(req, res) {
  try {
    const { keyword } = req.query;
    if (!keyword) {
      return res.status(400).json({ success: false, message: '搜索关键词不能为空' });
    }
    const products = await Product.search(keyword);
    res.json({ success: true, data: products });
  } catch (error) {
    res.status(500).json({ success: false, message: '搜索商品失败', error: error.message });
  }
}

// 根据分类获取商品
async function getProductsByCategory(req, res) {
  try {
    const { category } = req.params;
    const products = await Product.getByCategory(category);
    res.json({ success: true, data: products });
  } catch (error) {
    res.status(500).json({ success: false, message: '获取商品失败', error: error.message });
  }
}

// 创建商品
async function createProduct(req, res) {
  try {
    const productData = req.body;
    // 验证必填字段
    if (!productData.name || !productData.price || productData.quantity === undefined) {
      return res.status(400).json({ success: false, message: '商品名称、价格和数量为必填字段' });
    }
    const product = await Product.create(productData);
    res.status(201).json({ success: true, message: '商品创建成功', data: product });
  } catch (error) {
    res.status(500).json({ success: false, message: '创建商品失败', error: error.message });
  }
}

// 更新商品
async function updateProduct(req, res) {
  try {
    const { id } = req.params;
    const productData = req.body;
    
    // 检查商品是否存在
    const existingProduct = await Product.getById(id);
    if (!existingProduct) {
      return res.status(404).json({ success: false, message: '商品不存在' });
    }
    
    const product = await Product.update(id, productData);
    res.json({ success: true, message: '商品更新成功', data: product });
  } catch (error) {
    res.status(500).json({ success: false, message: '更新商品失败', error: error.message });
  }
}

// 删除商品
async function deleteProduct(req, res) {
  try {
    const { id } = req.params;
    
    // 检查商品是否存在
    const existingProduct = await Product.getById(id);
    if (!existingProduct) {
      return res.status(404).json({ success: false, message: '商品不存在' });
    }
    
    const success = await Product.delete(id);
    if (success) {
      res.json({ success: true, message: '商品删除成功' });
    } else {
      res.status(500).json({ success: false, message: '删除商品失败' });
    }
  } catch (error) {
    res.status(500).json({ success: false, message: '删除商品失败', error: error.message });
  }
}

// 获取商品统计信息
async function getProductStats(req, res) {
  try {
    const count = await Product.count();
    const categories = await Product.getCategories();
    res.json({ success: true, data: { totalProducts: count, categories } });
  } catch (error) {
    res.status(500).json({ success: false, message: '获取统计信息失败', error: error.message });
  }
}

module.exports = {
  getAllProducts,
  getProductById,
  searchProducts,
  getProductsByCategory,
  createProduct,
  updateProduct,
  deleteProduct,
  getProductStats
};

商品路由（routes/products.js）

const express = require('express');
const router = express.Router();
const productController = require('../controllers/productController');

// 获取所有商品
router.get('/', productController.getAllProducts);

// 获取商品统计信息
router.get('/stats', productController.getProductStats);

// 搜索商品
router.get('/search', productController.searchProducts);

// 根据分类获取商品
router.get('/category/:category', productController.getProductsByCategory);

// 根据 ID 获取商品
router.get('/:id', productController.getProductById);

// 创建商品
router.post('/', productController.createProduct);

// 更新商品
router.put('/:id', productController.updateProduct);

// 删除商品
router.delete('/:id', productController.deleteProduct);

module.exports = router;

主应用文件（app.js）

const express = require('express');
const dotenv = require('dotenv');
const { testConnection } = require('./config/db');
const productRoutes = require('./routes/products');

// 加载环境变量
dotenv.config();

// 创建 Express 应用
const app = express();

// 中间件
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

// 路由
app.use('/api/products', productRoutes);

// 健康检查
app.get('/health', (req, res) => {
  res.json({ status: 'ok', message: '服务运行正常' });
});

// 测试数据库连接
testConnection();

// 启动服务器
const port = process.env.PORT || 3000;
app.listen(port, () => {
  console.log(`服务器已启动，监听端口 ${port}`);
});

代码解析：

1. 项目结构：使用了模块化的项目结构，将代码分为配置、路由、控制器和模型等部分

2. 数据库连接：使用 mysql2 的 Promise 连接池，优化数据库连接管理

3. 模型层：实现了商品模型，封装了数据库操作方法，提供了清晰的 API 接口

4. 控制器层：处理 HTTP 请求，调用模型方法，返回响应结果

5. 路由层：定义了 API 路由，将请求映射到对应的控制器方法

6. 错误处理：对各种操作可能出现的错误进行了处理，返回友好的错误信息

7. 数据验证：对请求数据进行了基本的验证，确保数据的完整性

运行方法：

1. 安装依赖：npm install express mysql2 dotenv
2. 创建项目结构，将上述代码保存到对应文件
3. 创建 .env 文件，配置 MySQL 数据库连接信息
4. 启动 MySQL 服务
5. 执行数据库初始化 SQL 语句，创建数据库和表结构
6. 启动应用：node app.js
7. 使用 API 测试工具（如 Postman）测试各个接口

学习目标

通过本集的学习，你应该能够：

1. 理解 MySQL 的基本概念和特点
2. 掌握使用 mysql2 驱动连接 MySQL 的方法
3. 学会执行各种 SQL 查询，包括 SELECT、INSERT、UPDATE 和 DELETE
4. 理解并应用预处理语句，防止 SQL 注入攻击
5. 掌握事务处理的方法，确保数据一致性
6. 学会使用连接池管理数据库连接，优化性能
7. 实现一个完整的商品管理系统，包括商品的添加、查询、更新和删除操作

小结

MySQL 是一种功能强大、可靠性高的关系型数据库管理系统，广泛应用于各种 Web 应用和企业级系统中。在 Node.js 应用中，使用 mysql2 驱动可以高效、可靠地与 MySQL 数据库交互，它提供了 Promise API、预处理语句和连接池等高级特性，使得数据库操作更加方便和安全。

通过本集的学习，你已经掌握了 MySQL 的基本概念、mysql2 驱动的使用方法，以及如何实现一个完整的商品管理系统。这些知识将为你开发各种基于 Node.js 和 MySQL 的应用打下坚实的基础。

在下一集中，我们将学习 Node.js Redis 缓存，了解如何使用 Redis 提高应用性能和可靠性。