MySQL 数据库连接与操作

作者：陈川阅读数：55792人阅读分类： Node.js

MySQL 数据库连接与操作

Koa2 是一个基于 Node.js 的轻量级 Web 框架，它通过中间件机制提供了强大的异步流程控制能力。在实际开发中，数据库操作是后端服务的核心功能之一，而 MySQL 作为最流行的关系型数据库之一，与 Koa2 的结合使用非常普遍。

安装 MySQL 驱动

在 Koa2 项目中使用 MySQL 需要先安装 mysql2 包，这是 Node.js 中一个高性能的 MySQL 客户端：

npm install mysql2 --save

mysql2 相比 mysql 包有更好的性能和 Promise 支持，这对于 Koa2 的异步特性非常重要。

创建数据库连接

首先需要创建一个数据库连接池，这是推荐的生产环境用法：

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

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

连接池配置说明：

connectionLimit: 连接池最大连接数
queueLimit: 当没有可用连接时，排队等待的最大请求数
waitForConnections: 当连接池达到上限时是否等待

基本查询操作

执行简单查询

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

带参数的查询

使用预处理语句防止 SQL 注入：

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

事务处理

Koa2 中处理事务需要特别注意异步流程：

async function transferMoney(fromId, toId, amount) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    
    // 扣款
    await conn.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId]);
    
    // 存款
    await conn.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId]);
    
    await conn.commit();
    return true;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

与 Koa2 中间件集成

可以将数据库连接封装为 Koa2 中间件：

app.use(async (ctx, next) => {
  ctx.db = pool;
  await next();
});

// 在路由中使用
router.get('/users', async (ctx) => {
  const [users] = await ctx.db.query('SELECT * FROM users');
  ctx.body = users;
});

高级查询技巧

分页查询

async function getUsersWithPagination(page = 1, pageSize = 10) {
  const offset = (page - 1) * pageSize;
  const [rows] = await pool.query(
    'SELECT * FROM users LIMIT ? OFFSET ?', 
    [pageSize, offset]
  );
  return rows;
}

联表查询

async function getUserWithPosts(userId) {
  const [rows] = await pool.query(`
    SELECT u.*, p.title, p.content 
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    WHERE u.id = ?
  `, [userId]);
  
  return rows;
}

性能优化

使用连接池统计

// 获取连接池状态
console.log(pool.pool.config.connectionLimit);
console.log(pool.pool._freeConnections.length);
console.log(pool.pool._allConnections.length);

批量插入

async function batchInsertUsers(users) {
  const values = users.map(user => [user.name, user.email]);
  await pool.query(
    'INSERT INTO users (name, email) VALUES ?',
    [values]
  );
}

错误处理

在 Koa2 中统一处理数据库错误：

app.use(async (ctx, next) => {
  try {
    await next();
  } catch (err) {
    if (err.code === 'ER_DUP_ENTRY') {
      ctx.status = 409;
      ctx.body = { error: 'Duplicate entry' };
    } else if (err.code === 'ER_NO_REFERENCED_ROW_2') {
      ctx.status = 400;
      ctx.body = { error: 'Foreign key constraint fails' };
    } else {
      ctx.status = 500;
      ctx.body = { error: 'Database error' };
    }
  }
});

使用 ORM 替代原生查询

虽然直接使用 SQL 查询很灵活，但在大型项目中可以考虑使用 Sequelize 这样的 ORM：

const { Sequelize } = require('sequelize');

const sequelize = new Sequelize('koa_demo', 'root', 'yourpassword', {
  host: 'localhost',
  dialect: 'mysql'
});

// 定义模型
const User = sequelize.define('User', {
  name: { type: Sequelize.STRING },
  email: { type: Sequelize.STRING }
});

// 在 Koa2 中使用
router.get('/users', async (ctx) => {
  const users = await User.findAll();
  ctx.body = users;
});

数据库迁移

对于生产环境，应该使用迁移工具来管理数据库结构变更：

npm install db-migrate mysql -g

创建迁移文件：

db-migrate create add-users-table

然后编辑生成的迁移文件：

exports.up = function(db) {
  return db.createTable('users', {
    id: { type: 'int', primaryKey: true, autoIncrement: true },
    name: 'string',
    email: { type: 'string', unique: true }
  });
};

exports.down = function(db) {
  return db.dropTable('users');
};

监控与日志

可以添加查询日志来调试 SQL：

const pool = mysql.createPool({
  // ...其他配置
  enableKeepAlive: true,
  debug: process.env.NODE_ENV === 'development'
});

// 监听查询事件
pool.on('connection', (connection) => {
  console.log('New connection established');
});

pool.on('acquire', (connection) => {
  console.log('Connection acquired');
});

连接池调优

根据应用负载调整连接池参数：

const pool = mysql.createPool({
  // ...其他配置
  connectionLimit: process.env.DB_POOL_SIZE || 10,
  idleTimeout: 60000, // 空闲连接超时时间(毫秒)
  maxIdle: 5 // 最大空闲连接数
});

安全注意事项

永远不要直接将用户输入拼接到 SQL 中
使用最小权限原则配置数据库用户
定期备份数据库
敏感数据加密存储

// 不安全的写法
const unsafeQuery = `SELECT * FROM users WHERE name = '${ctx.query.name}'`;

// 安全的写法
const safeQuery = 'SELECT * FROM users WHERE name = ?';
const [rows] = await pool.query(safeQuery, [ctx.query.name]);

测试数据库操作

使用 Jest 测试数据库相关代码：

describe('User Model', () => {
  beforeAll(async () => {
    await pool.query('CREATE TABLE IF NOT EXISTS test_users LIKE users');
  });

  afterAll(async () => {
    await pool.query('DROP TABLE test_users');
    await pool.end();
  });

  test('should create a user', async () => {
    await pool.query('INSERT INTO test_users (name, email) VALUES (?, ?)', ['Test', 'test@example.com']);
    const [rows] = await pool.query('SELECT * FROM test_users WHERE email = ?', ['test@example.com']);
    expect(rows.length).toBe(1);
  });
});

生产环境实践

使用环境变量管理数据库配置：

const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'koa_demo'
});

实现数据库健康检查端点：

router.get('/health', async (ctx) => {
  try {
    await pool.query('SELECT 1');
    ctx.body = { status: 'OK' };
  } catch (err) {
    ctx.status = 503;
    ctx.body = { status: 'Database unavailable' };
  }
});

常见问题解决

连接超时问题

const pool = mysql.createPool({
  // ...其他配置
  connectTimeout: 10000, // 10秒连接超时
  acquireTimeout: 10000 // 10秒获取连接超时
});

处理断开连接

pool.on('error', (err) => {
  if (err.code === 'PROTOCOL_CONNECTION_LOST') {
    console.error('Database connection was closed.');
  } else if (err.code === 'ER_CON_COUNT_ERROR') {
    console.error('Database has too many connections.');
  } else if (err.code === 'ECONNREFUSED') {
    console.error('Database connection was refused.');
  }
});

性能基准测试

可以使用 benchmark 模块测试查询性能：

const Benchmark = require('benchmark');
const suite = new Benchmark.Suite;

suite.add('Simple query', {
  defer: true,
  fn: function(deferred) {
    pool.query('SELECT 1 + 1 AS solution')
      .then(() => deferred.resolve());
  }
})
.on('cycle', function(event) {
  console.log(String(event.target));
})
.run({ 'async': true });

数据库设计建议

为常用查询字段添加索引
避免使用 SELECT *
合理设计表关系
考虑使用存储过程处理复杂逻辑

// 创建索引示例
await pool.query('CREATE INDEX idx_user_email ON users(email)');

// 调用存储过程
await pool.query('CALL sp_get_user_posts(?)', [userId]);

连接多个数据库

有时需要连接多个 MySQL 实例：

const mainPool = mysql.createPool({/* 主库配置 */});
const readPool = mysql.createPool({/* 只读库配置 */});

// 根据操作类型选择连接池
async function query(sql, params, isReadOnly = false) {
  const pool = isReadOnly ? readPool : mainPool;
  return pool.query(sql, params);
}

使用连接池事件进行监控

pool.on('enqueue', () => {
  console.log('Waiting for available connection slot');
});

pool.on('release', (connection) => {
  console.log('Connection %d released', connection.threadId);
});

数据库版本兼容性

处理不同 MySQL 版本的差异：

const mysqlVersion = await pool.query('SELECT VERSION() AS version');
console.log(`MySQL version: ${mysqlVersion[0][0].version}`);

// 根据版本执行不同的SQL
if (mysqlVersion[0][0].version.startsWith('5.7')) {
  // MySQL 5.7 特定语法
} else if (mysqlVersion[0][0].version.startsWith('8.0')) {
  // MySQL 8.0 特定语法
}

做个网站！

本站部分内容来自互联网,一切版权均归源网站或源作者所有。

如果侵犯了你的权益请来信告知我们删除。邮箱：cc@cccx.cn

上一篇：错误响应标准化处理

下一篇：MongoDB 集成与 ORM 使用