Node.js使用mysql包操作MySQL事务的嵌套回调代码优化咨询

核心问题说明 #

• 你遇到的多层嵌套是JS中经典的回调地狱问题，并非mysql库的限制，只是该库默认的异步API采用回调风格导致的
• 原有代码还存在隐藏bug：connection.release()的调用时机不对，会在事务还没执行完成时就提前释放连接，导致运行异常

最优优化方案：使用mysql2 + async/await #

推荐直接替换为兼容mysqlAPI、原生支持Promise的mysql2库，配合async/await语法就能写出和Javatry/catch/finally逻辑完全一致的代码，可读性和可维护性大幅提升。

第一步：安装依赖 #

npm install mysql2

第二步：优化后代码 #

import mysql from "mysql2/promise";

// 连接池配置和原有逻辑完全一致
const pool = mysql.createPool({
    host: "SOME_HOST",
    user: "SOME_USER",
    port: "SOME_PORT",
    password: "SOME_PASSWORD",
    database: "SOME_DB_NAME"
});

async function runQueries() {
    let connection;
    try {
        // 1. 获取连接
        connection = await pool.getConnection();
        // 2. 开启事务
        await connection.beginTransaction();
        // 3. 执行查询
        const [results] = await connection.query("some query");
        // 4. 提交事务
        await connection.commit();
        console.log(results);
    } catch (err) {
        // 任意步骤出错就回滚事务
        if (connection) await connection.rollback();
        throw err;
    } finally {
        // 无论成功失败都释放连接，和Java的finally逻辑完全一致
        if (connection) connection.release();
    }
}

如果你坚持使用原有mysql包 #

如果不想更换依赖，可以手动把回调风格的API封装成Promise，再用async/await调用，示例封装逻辑：

import mysql from "mysql";

const pool = mysql.createPool({
    host: "SOME_HOST",
    user: "SOME_USER",
    port: "SOME_PORT",
    password: "SOME_PASSWORD",
    database: "SOME_DB_NAME"
});

// 通用回调转Promise工具函数
function promisify(fn) {
    return (...args) => new Promise((resolve, reject) => {
        fn(...args, (err, res) => err ? reject(err) : resolve(res));
    });
}

async function runQueries() {
    let connection;
    try {
        connection = await promisify(pool.getConnection.bind(pool))();
        await promisify(connection.beginTransaction.bind(connection))();
        const results = await promisify(connection.query.bind(connection))("some query");
        await promisify(connection.commit.bind(connection))();
        console.log(results);
    } catch (err) {
        if (connection) await promisify(connection.rollback.bind(connection))();
        throw err;
    } finally {
        if (connection) connection.release();
    }
}

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