MySQL基础教程（三十七）MySQL之Node.js 连接与使用：Node.js与MySQL的终极指，连接池、异步与实战秘籍

在当今数据驱动的开发环境中，Node.js与MySQL的组合已成为全栈开发的黄金标准。本文将深入解析如何高效连接和使用MySQL，并附有实用示例。

连接池：性能优化的核心

创建连接是昂贵的操作。连接池通过复用连接显著提升性能：

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

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'your_password',
  database: 'test_db',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

异步操作：避免阻塞Event Loop

使用async/await确保非阻塞IO：

async function getUser(id) {
  const [rows] = await pool.execute(
    'SELECT * FROM users WHERE id = ?',
    [id]
  );
  return rows[0];
}

完整CRUD示例

// 创建
async function createUser(user) {
  const [result] = await pool.execute(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    [user.name, user.email]
  );
  return result.insertId;
}

// 查询
async function getUsers() {
  const [rows] = await pool.execute('SELECT * FROM users');
  return rows;
}

// 更新
async function updateUser(id, updates) {
  const [result] = await pool.execute(
    'UPDATE users SET name = ?, email = ? WHERE id = ?',
    [updates.name, updates.email, id]
  );
  return result.affectedRows;
}

// 删除
async function deleteUser(id) {
  const [result] = await pool.execute(
    'DELETE FROM users WHERE id = ?',
    [id]
  );
  return result.affectedRows;
}

错误处理与事务管理

async function transferFunds(fromId, toId, amount) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    
    await connection.execute(
      'UPDATE accounts SET balance = balance - ? WHERE id = ?',
      [amount, fromId]
    );
    
    await connection.execute(
      'UPDATE accounts SET balance = balance + ? WHERE id = ?',
      [amount, toId]
    );
    
    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}