Node.js 数据库连接完全指南：MySQL 与 MongoDB

MySQL 数据库操作

安装与连接

npm install mysql2
# mysql2 支持 Promise 和连接池，性能优于原生 mysql 驱动

// db/mysql.js
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME || 'myapp',
  waitForConnections: true,    // 连接池等待方式
  connectionLimit: 10,           // 最大连接数
  queueLimit: 0,                 // 队列上限（0 表示无限制）
  enableKeepAlive: true,        // 保活连接
  keepAliveInitialDelay: 0
});

// 测试连接
async function testConnection() {
  try {
    const conn = await pool.getConnection();
    console.log('MySQL 连接成功！');
    conn.release();
    return true;
  } catch (err) {
    console.error('MySQL 连接失败:', err.message);
    return false;
  }
}

module.exports = { pool, testConnection };

CRUD 操作

// repositories/userRepository.js
const { pool } = require('../db/mysql');

// 插入用户
async function createUser({ name, email, passwordHash }) {
  const [result] = await pool.execute(
    'INSERT INTO users (name, email, password_hash, created_at) VALUES (?, ?, ?, NOW())',
    [name, email, passwordHash]
  );
  return { id: result.insertId, name, email };
}

// 查询用户列表（分页）
async function getUsers({ page = 1, limit = 10, search = '' }) {
  const offset = (page - 1) * limit;
  const searchPattern = `%${search}%`;

  const [users] = await pool.execute(
    `SELECT id, name, email, created_at FROM users
     WHERE name LIKE ? OR email LIKE ?
     ORDER BY created_at DESC
     LIMIT ? OFFSET ?`,
    [searchPattern, searchPattern, String(limit), String(offset)]
  );

  const [[{ total }]] = await pool.execute(
    'SELECT COUNT(*) as total FROM users WHERE name LIKE ? OR email LIKE ?',
    [searchPattern, searchPattern]
  );

  return { users, total, page, limit };
}

// 查询单个用户
async function getUserById(id) {
  const [rows] = await pool.execute(
    'SELECT id, name, email, created_at FROM users WHERE id = ?',
    [id]
  );
  return rows[0] || null;
}

// 按邮箱查询（用于登录）
async function getUserByEmail(email) {
  const [rows] = await pool.execute(
    'SELECT * FROM users WHERE email = ?',
    [email]
  );
  return rows[0] || null;
}

// 更新用户
async function updateUser(id, { name, email }) {
  const fields = [];
  const values = [];
  if (name !== undefined) { fields.push('name = ?'); values.push(name); }
  if (email !== undefined) { fields.push('email = ?'); values.push(email); }

  if (fields.length === 0) return null;

  values.push(id);
  const [result] = await pool.execute(
    `UPDATE users SET ${fields.join(', ')} WHERE id = ?`,
    values
  );
  return result.affectedRows > 0;
}

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

// 事务示例：创建用户和用户资料（原子操作）
async function createUserWithProfile(userData, profileData) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    const [userResult] = await conn.execute(
      'INSERT INTO users (name, email, password_hash) VALUES (?, ?, ?)',
      [userData.name, userData.email, userData.passwordHash]
    );
    const userId = userResult.insertId;

    await conn.execute(
      'INSERT INTO user_profiles (user_id, bio, avatar_url) VALUES (?, ?, ?)',
      [userId, profileData.bio, profileData.avatarUrl || null]
    );

    await conn.commit();
    return { userId, profileCreated: true };
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

module.exports = {
  createUser,
  getUsers,
  getUserById,
  getUserByEmail,
  updateUser,
  deleteUser,
  createUserWithProfile
};

MongoDB 数据库操作

安装与连接

npm install mongoose

// db/mongodb.js
const mongoose = require('mongoose');

// 连接字符串
const MONGO_URI = process.env.MONGO_URI || 'mongodb://localhost:27017/myapp';

mongoose.connect(MONGO_URI, {
  maxPoolSize: 10,          // 连接池大小
  serverSelectionTimeoutMS: 5000,  // 服务器选择超时
  socketTimeoutMS: 45000,   // Socket 超时
})
.then(() => console.log('MongoDB 连接成功！'))
.catch(err => console.error('MongoDB 连接失败:', err));

// 监听连接事件
mongoose.connection.on('error', (err) => console.error('MongoDB 错误:', err));
mongoose.connection.on('disconnected', () => console.warn('MongoDB 连接断开'));

module.exports = mongoose;

Mongoose 模型与操作

// models/Post.js
const mongoose = require('mongoose');
const Schema = mongoose.Schema;

const postSchema = new Schema({
  title: {
    type: String,
    required: true,
    trim: true,
    maxlength: 200,
    index: true  // 自动建立索引
  },
  content: {
    type: String,
    required: true
  },
  author: {
    type: Schema.Types.ObjectId,
    ref: 'User',
    required: true,
    index: true
  },
  tags: [{
    type: String,
    trim: true
  }],
  status: {
    type: String,
    enum: ['draft', 'published', 'archived'],
    default: 'draft'
  },
  views: { type: Number, default: 0 },
  likes: [{ type: Schema.Types.ObjectId, ref: 'User' }]
}, {
  timestamps: true  // 自动添加 createdAt 和 updatedAt
});

// 复合索引
postSchema.index({ author: 1, status: 1, createdAt: -1 });
// 文本索引（支持全文搜索）
postSchema.index({ title: 'text', content: 'text' });

// 静态方法
postSchema.statics.findPublished = function(page = 1, limit = 10) {
  return this.find({ status: 'published' })
    .populate('author', 'name avatar')
    .sort({ createdAt: -1 })
    .skip((page - 1) * limit)
    .limit(limit);
};

// 实例方法
postSchema.methods.incrementViews = async function() {
  this.views += 1;
  return this.save({ validateBeforeSave: false });
};

// 聚合管道示例
postSchema.statics.getStats = async function(authorId) {
  return this.aggregate([
    { $match: { author: mongoose.Types.ObjectId(authorId) } },
    {
      $group: {
        _id: '$status',
        count: { $sum: 1 },
        totalViews: { $sum: '$views' },
        avgViews: { $avg: '$views' }
      }
    },
    { $sort: { _id: 1 } }
  ]);
};

const Post = mongoose.model('Post', postSchema);
module.exports = Post;

// repositories/postRepository.js
const Post = require('../models/Post');

// 创建文章
async function createPost(data) {
  const post = new Post(data);
  return await post.save();
}

// 分页查询（支持全文搜索）
async function searchPosts({ q, page = 1, limit = 10, tag }) {
  const filter = { status: 'published' };
  if (tag) filter.tags = tag;
  if (q) filter.$text = { $search: q };  // 需要先建立文本索引

  const [posts, total] = await Promise.all([
    Post.find(filter)
      .populate('author', 'name email')
      .sort({ createdAt: -1 })
      .skip((page - 1) * limit)
      .limit(limit),
    Post.countDocuments(filter)
  ]);

  return { posts, total, page, limit };
}

// 聚合管道：获取热门标签
async function getHotTags(limit = 10) {
  return Post.aggregate([
    { $match: { status: 'published' } },
    { $unwind: '$tags' },
    { $group: { _id: '$tags', count: { $sum: 1 } } },
    { $sort: { count: -1 } },
    { $limit: limit }
  ]);
}

常见问题

Q1: MySQL 连接池大小如何设置？

连接池大小取决于服务器配置和并发需求。一般公式：连接数 = (CPU 核心数 * 2) + 磁盘数。MySQL 默认最大连接 151，实际生产环境建议 10-20 个。设置过大（>100）可能导致 MySQL 连接耗尽。过小的连接池在高并发时会导致请求排队等待。

Q2: Mongoose 的 <code>save()</code> vs <code>create()</code> 有什么区别？

new Model() + save() 返回 Promise，可以对实例操作（增量更新）；Model.create() 是静态方法，直接操作数据库，对于批量插入性能更好（insertMany）。save() 支持 middleware/hook，create() 部分支持。

Q3: MongoDB 如何保证数据一致性？

MongoDB 支持单文档的原子操作（$inc、$set 等）；多文档事务需要 MongoDB 4.0+ 且副本集部署。使用 mongoose 时：const session = await mongoose.startSession(); session.startTransaction(); 然后 commitTransaction() 或 abortTransaction()。

延伸阅读

• <a href="https://github.com/sidorares/node-mysql2">MySQL2 官方文档</a>

• <a href="https://mongoosejs.com/docs/">Mongoose 官方文档</a>
• <a href="https://www.mongodb.com/docs/manual/core/transactions/">MongoDB 事务文档</a>