Express+MySQL：复杂查询的整洁代码实现咨询

1. 用参数化查询替代字符串拼接（安全+可读） #

直接拼接SQL字符串不仅有SQL注入风险，还会让代码变得杂乱不堪。换成mysql2（推荐用promise版）的占位符语法，既安全又清晰：

exports.addAuthor = function(req, res, next) {
  const a = checkAuthor(req.body);
  if (!a.valid) {
    const myerr = new Error(a.msg);
    myerr.status = 400;
    return next(myerr);
  }

  // 检查作者是否存在的参数化查询
  const checkExistsQuery = `
    SELECT COUNT(*) AS nb 
    FROM authors 
    WHERE name = ? AND firstname = ?
  `;

  myPool.query(checkExistsQuery, [a.name, a.firstname], (err, results) => {
    if (err) return next(err);
    
    if (results[0].nb > 0) {
      const err = new Error('该作者已存在');
      err.status = 409;
      return next(err);
    }

    // 插入新作者的参数化查询
    const insertQuery = `
      INSERT INTO authors (name, firstname, bio) 
      VALUES (?, ?, ?)
    `;
    myPool.query(insertQuery, [a.name, a.firstname, a.bio], (err, result) => {
      if (err) return next(err);
      res.status(201).json({ id: result.insertId, ...a });
    });
  });
};

2. 拆分逻辑为独立函数，用async/await让代码线性化 #

把重复或复杂的查询逻辑抽成单独工具函数，再用async/await替代回调嵌套，代码会变得更易读、易维护：

// 工具函数：检查作者是否存在
const checkAuthorExists = (name, firstname) => {
  return new Promise((resolve, reject) => {
    const query = `SELECT COUNT(*) AS nb FROM authors WHERE name = ? AND firstname = ?`;
    myPool.query(query, [name, firstname], (err, results) => {
      if (err) return reject(err);
      resolve(results[0].nb > 0);
    });
  });
};

// 工具函数：插入新作者
const insertAuthor = (authorData) => {
  return new Promise((resolve, reject) => {
    const query = `INSERT INTO authors (name, firstname, bio) VALUES (?, ?, ?)`;
    myPool.query(query, [authorData.name, authorData.firstname, authorData.bio], (err, result) => {
      if (err) return reject(err);
      resolve({ id: result.insertId, ...authorData });
    });
  });
};

// 主业务函数
exports.addAuthor = async function(req, res, next) {
  try {
    const a = checkAuthor(req.body);
    if (!a.valid) {
      const myerr = new Error(a.msg);
      myerr.status = 400;
      throw myerr;
    }

    const exists = await checkAuthorExists(a.name, a.firstname);
    if (exists) {
      const err = new Error('该作者已存在');
      err.status = 409;
      throw err;
    }

    const newAuthor = await insertAuthor(a);
    res.status(201).json(newAuthor);
  } catch (err) {
    next(err);
  }
};

3. 用模板字符串格式化多行SQL #

ES6的模板字符串（反引号）可以直接写多行SQL，保留换行和缩进，一眼就能看懂SQL的结构：

// 关联查询作者及其所有书籍的复杂SQL
const getAuthorWithBooks = async (authorId) => {
  const query = `
    SELECT a.*, b.title, b.publish_date, b.isbn
    FROM authors a
    LEFT JOIN books b ON a.id = b.author_id
    WHERE a.id = ?
    ORDER BY b.publish_date DESC
  `;
  const rows = await DB.query(query, [authorId]);
  
  // 整理结果，避免重复作者数据
  if (rows.length === 0) return null;
  const author = { ...rows[0], books: [] };
  rows.forEach(row => {
    if (row.title) {
      author.books.push({ title: row.title, publish_date: row.publish_date, isbn: row.isbn });
    }
  });
  return author;
};

4. 封装数据库基础操作 #

如果项目规模较大，建议把数据库的连接、查询等基础操作封装成工具类，减少重复代码：

// db.js - 数据库工具类
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'your-username',
  password: 'your-password',
  database: 'your-db-name'
});

class DB {
  static async query(sql, params = []) {
    const [rows] = await pool.execute(sql, params);
    return rows;
  }

  static async getOne(sql, params = []) {
    const rows = await this.query(sql, params);
    return rows[0];
  }
}

module.exports = DB;

之后业务代码里就可以简洁调用：

const DB = require('./db');

exports.getAuthor = async (req, res, next) => {
  try {
    const author = await DB.getOne(`SELECT * FROM authors WHERE id = ?`, [req.params.id]);
    if (!author) {
      const err = new Error('作者不存在');
      err.status = 404;
      throw err;
    }
    res.json(author);
  } catch (err) {
    next(err);
  }
};

5. 注释关键逻辑（别过度注释） #

对于复杂的查询分支、数据整理逻辑，加简洁的注释说明意图，而不是重复代码本身：

// 按年份分组统计作者的书籍出版数量
const getAuthorBookStats = async (authorId) => {
  const query = `
    SELECT YEAR(b.publish_date) AS publish_year, COUNT(*) AS book_count
    FROM books b
    WHERE b.author_id = ?
    GROUP BY publish_year
    ORDER BY publish_year DESC
  `;
  const stats = await DB.query(query, [authorId]);
  
  // 补全年份空缺，确保统计数据连续
  const latestYear = stats.length > 0 ? stats[0].publish_year : new Date().getFullYear();
  const fullStats = [];
  for (let year = latestYear; year >= latestYear - 5; year--) {
    const existing = stats.find(s => s.publish_year === year);
    fullStats.push({ publish_year: year, book_count: existing ? existing.book_count : 0 });
  }
  return fullStats;
};

内容的提问来源于stack exchange，提问作者JPMous