Express集成MySQL时服务崩溃，异常未被正确捕获处理

问题场景 #

开发图书详情查询接口时，传入合法整数ID能正常返回数据，但以下两种情况会导致服务器崩溃：

• 图书ID未定义时
• 查询的表名不存在时（比如把books改成book）
  外层的try-catch无法捕获这类错误，直接导致服务器退出。

现有代码 #

connectDB.js（数据库连接文件） #

const pool = mysql.createPool({
  connectionLimit: 10,
  multipleStatements: true,
  host: "localhost",
  user: "root",
  password: "password",
  database: "library_management",
  port: 3306,
});
pool.getConnection((error, connection) => {
  if (error) throw error;
  console.log("connected to db");
  connection.release();
});
export default pool;

books.js（路由文件） #

import express from "express";
import pool from "../connectDB.js";
const booksRouter = express.Router();
booksRouter.get("/book/:bookId", async (req, res) => {
  try {
    const { bookId } = req.params;

    const query = `select * from books where book_id = ${bookId};`;
    pool.query(query, (error, result) => {
      if (error) throw error;
      res
        .status(200)
        .json({ access: true, msg: "fetched book details", bookDetails: result[0] });
    });
  } catch (error) {
     console.log(error);
    res.status(500).json({ access: false, error: "server error" });
  }
});

错误日志 #

/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/Parser.js:437
      throw err; // Rethrow non-MySQL errors
      ^

Error: ER_BAD_FIELD_ERROR: Unknown column 'undefined' in 'where clause'
    at Sequence._packetToError (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:324:12)
    --------------------
    at Pool.query (/home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/node_modules/mysql/lib/Pool.js:199:23)
    at file:///home/amber/full-stack-web-dev/mini-projects/library-management-mysql/server/controllers/books.js:10:10
    at Layer.handle [as handle_request] (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/layer.js:95:5)
    at next (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/route.js:144:13)
    at Route.dispatch (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/route.js:114:3)
    at Layer.handle [as handle_request] (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/layer.js:95:5)
    at /home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/index.js:284:15
    at param (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/index.js:365:14)
    at param (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/index.js:376:14)
    at Function.process_params (/home/amber/full-stack-web-dev/mini-projects/node_modules/express/lib/router/index.js:421:3) {
  code: 'ER_BAD_FIELD_ERROR',
  errno: 1054,
  sqlMessage: "Unknown column 'undefined' in 'where clause'",
  sqlState: '42S22',
  index: 0,
  sql: 'select * from books where book_id = undefined;'
}

Node.js v18.12.1
[nodemon] app crashed - waiting for file changes before starting...

问题原因 #

1. 异步回调错误无法被外层try-catch捕获：pool.query是基于回调的异步操作，回调函数内的throw error会直接抛出到Node.js全局上下文，外层同步的try-catch无法捕获。
2. SQL字符串拼接导致的问题：直接用${bookId}拼接SQL，当bookId未定义时，会生成where book_id = undefined的错误SQL，触发MySQL语法错误。
3. 未做参数校验：没有提前检查bookId的合法性，直接传入查询。

修复方案 #

方案1：改用Promise风格查询（配合async/await） #

将pool.query包装为Promise，让异步错误能被try-catch捕获：

import express from "express";
import pool from "../connectDB.js";
const booksRouter = express.Router();

// 把pool.query包装成Promise
const queryPromise = (sql, params) => {
  return new Promise((resolve, reject) => {
    pool.query(sql, params, (error, results) => {
      if (error) reject(error);
      else resolve(results);
    });
  });
};

booksRouter.get("/book/:bookId", async (req, res) => {
  try {
    const { bookId } = req.params;
    
    // 1. 提前校验参数：检查bookId是否为有效整数
    const parsedBookId = parseInt(bookId);
    if (isNaN(parsedBookId)) {
      return res.status(400).json({ access: false, error: "无效的图书ID" });
    }

    // 2. 使用参数化查询，避免SQL注入和undefined问题
    const query = "select * from books where book_id = ?;";
    const result = await queryPromise(query, [parsedBookId]);

    res.status(200).json({ 
      access: true, 
      msg: "获取图书详情成功", 
      bookDetails: result[0] 
    });
  } catch (error) {
    console.log(error);
    // 区分错误类型，返回更友好的提示
    if (error.code === "ER_NO_SUCH_TABLE") {
      res.status(500).json({ access: false, error: "数据表不存在" });
    } else {
      res.status(500).json({ access: false, error: "服务器内部错误" });
    }
  }
});

方案2：在回调内直接处理错误（不抛出） #

如果不想修改Promise风格，直接在pool.query的回调里处理错误，避免抛出到全局：

import express from "express";
import pool from "../connectDB.js";
const booksRouter = express.Router();

booksRouter.get("/book/:bookId", (req, res) => {
  const { bookId } = req.params;
  
  // 提前校验参数
  const parsedBookId = parseInt(bookId);
  if (isNaN(parsedBookId)) {
    return res.status(400).json({ access: false, error: "无效的图书ID" });
  }

  // 参数化查询
  const query = "select * from books where book_id = ?;";
  pool.query(query, [parsedBookId], (error, result) => {
    if (error) {
      console.log(error);
      if (error.code === "ER_NO_SUCH_TABLE") {
        return res.status(500).json({ access: false, error: "数据表不存在" });
      }
      return res.status(500).json({ access: false, error: "服务器内部错误" });
    }
    res.status(200).json({ 
      access: true, 
      msg: "获取图书详情成功", 
      bookDetails: result[0] 
    });
  });
});

额外优化点 #

• 全局错误处理中间件：在Express应用末尾添加全局错误处理中间件，捕获所有未处理的错误，避免服务器崩溃：

// 放在所有路由之后
app.use((err, req, res, next) => {
  console.error(err.stack);
  res.status(500).json({ access: false, error: "服务器内部错误" });
});

• 避免SQL注入：始终使用参数化查询（?占位符），不要直接拼接用户输入到SQL语句中。

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