Node.js AWS Lambda中循环内async MySQL删除操作未正常执行如何解决？

问题根因 #

1. 同时混用await与回调函数 #

mysql2的execute方法同时支持回调和Promise两种调用模式。当你既给execute传了回调函数，又加了await修饰时，await不会等待回调逻辑执行完成，会直接把当前异步操作挂起后继续往下走，导致还没等数据库删除请求返回结果，就触发了循环末尾的callback(null, response)，此时response还没被赋值，自然返回空响应。

2. forEach不支持等待异步回调 #

第二种写法中你把async声明放在了forEach的回调函数里，而forEach本身不会等待内部异步操作执行完成，会直接执行后续的callback逻辑，和第一种写法的问题完全一致。

修复方案 #

写法1：保留单条删除逻辑，修复异步等待 #

const mysql = require('mysql2/promise'); // 直接引入mysql2的promise版本，避免混用回调
const errorCodes = require('source/error-codes');
const PropertiesReader = require('properties-reader');

const prop = PropertiesReader('properties.properties');

// 建议用createPool代替createConnection，Lambda复用场景下性能更好
const con = mysql.createPool({
    host: prop.get('server.host'),
    user: prop.get("server.username"),
    password: prop.get("server.password"),
    port: prop.get("server.port"),
    database: prop.get("server.dbname"),
    waitForConnections: true,
    connectionLimit: 10
});


exports.deleteSellerPortfolioItemImages = async (event, context, callback) => {
    context.callbackWaitsForEmptyEventLoop = false;
    let response;

    try {
        if (!event.body) {
            response = errorCodes.missing_parameters;
            return callback(null, response);
        }
        const body = JSON.parse(event.body);
        // 参数预校验，避免删到一半才发现参数错误
        const idList = body.map(item => {
            const id = Number(item.idseller_portfolio_item_images);
            if (isNaN(id)) throw new Error('invalid_id');
            return id;
        });

        // 循环等待所有删除操作完成
        for (const id of idList) {
            const sql = "DELETE FROM seller_portfolio_item_images WHERE idseller_portfolio_item_images = ? ";
            await con.execute(sql, [id]);
        }

        response = {
            "statusCode": 200,
            "headers": {
                "Content-Type": "application/json"
            },
            "body": JSON.stringify({ status: 1 }),
            "isBase64Encoded": false
        };
        callback(null, response);
    } catch (err) {
        console.log(err);
        if (err.message === 'invalid_id') {
            response = errorCodes.invalid_parameter;
        } else if (err.toString().indexOf('cannot be null') >= 0) {
            response = errorCodes.not_null_parameters;
        } else {
            response = errorCodes.internal_server_error;
        }
        callback(null, response);
    }
};

写法2：优化为批量删除（性能更好） #

不需要循环请求数据库，直接用IN语句一次性删除所有目标记录，减少IO开销：

// 前面的引入和连接配置和上面一致
exports.deleteSellerPortfolioItemImages = async (event, context, callback) => {
    context.callbackWaitsForEmptyEventLoop = false;
    let response;

    try {
        if (!event.body) {
            response = errorCodes.missing_parameters;
            return callback(null, response);
        }
        const body = JSON.parse(event.body);
        const idList = body.map(item => {
            const id = Number(item.idseller_portfolio_item_images);
            if (isNaN(id)) throw new Error('invalid_id');
            return id;
        });

        const sql = "DELETE FROM seller_portfolio_item_images WHERE idseller_portfolio_item_images IN (?) ";
        await con.execute(sql, [idList]);

        response = {
            "statusCode": 200,
            "headers": {
                "Content-Type": "application/json"
            },
            "body": JSON.stringify({ status: 1 }),
            "isBase64Encoded": false
        };
        callback(null, response);
    } catch (err) {
        console.log(err);
        if (err.message === 'invalid_id') {
            response = errorCodes.invalid_parameter;
        } else if (err.toString().indexOf('cannot be null') >= 0) {
            response = errorCodes.not_null_parameters;
        } else {
            response = errorCodes.internal_server_error;
        }
        callback(null, response);
    }
};

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