NodeJS中MySQL Update未按预期执行的原因及解决方法

问题 #

我尝试读取表中的用户评分并更新用户的最终评分，以下是我的NodeJS代码：

const mysql = require('mysql2/promise');
const errorCodes = require('source/error-codes');
const PropertiesReader = require('properties-reader');

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

const pool = mysql.createPool({
    connectionLimit : 10,
    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")
});


exports.userRatingManager = async (event, context) => {

    const connection = await pool.getConnection();
    context.callbackWaitsForEmptyEventLoop = false;
    connection.config.namedPlaceholders = true;


        try {
            await connection.beginTransaction();

            //Get the list of users
            const selectUserSql = "SELECT * FROM user";
            const [userData, userMeta] = await connection.query(selectUserSql);

            //Get the list of feedback for the selected user
            const feedBackSql = "SELECT * FROM job_feedback WHERE to_user = ?";

            for(let i = 0; i<userData.length; i++)
            {
                let userId = Number(userData[i].iduser);
                const [feedbackData, feedbackMeta] = await connection.query(feedBackSql, [userId]);

                if(feedbackData.length>0)
                {
                    let noOfFeedbacks = feedbackData.length;
                    let totalRating = 0;
                    let finalUserRating = 0;

                    console.log(feedbackData[0].to_user);

                    for(let feed=0; feed<feedbackData.length; feed++)
                    {
                        console.log( feedbackData[feed].feedback);
                        totalRating = totalRating+feedbackData[feed].rating;
                    }

                    finalUserRating = totalRating/noOfFeedbacks;
                    console.log("final rating: "+finalUserRating);

                    //Insert the final rating into the user commons
                    const updateRatingSql = "UPDATE user_common SET rating = ? WHERE iduser = ?";
                    const updateRatingResult = await connection.query(updateRatingSql, {finalUserRating, userId});
                    console.log("updated usrid: "+userId);

                    //Print the response
                    console.log(JSON.stringify({
                        updateRatingResult
                    }));

                }
            }

            //Commit and complete
            await connection.commit();

        } catch (error) {
            console.log(error);
            await connection.rollback();
            return errorCodes.save_failed;
            
        }
        finally{
            connection.release();
        }
};

代码逻辑正常，但无法更新user_common表记录。已确认SQL语法正确，在MySQL Workbench中用相同数据手动执行该SQL可以生效。

控制台输出显示无记录被更新：

2022-09-30T07:39:00.512Z        66fd22f4-63f7-4b36-9982-499393b4d316    INFO    29
2022-09-30T07:39:00.516Z        66fd22f4-63f7-4b36-9982-499393b4d316    INFO    A very good Buyer.
2022-09-30T07:39:00.517Z        66fd22f4-63f7-4b36-9982-499393b4d316    INFO    Amazing buyer
2022-09-30T07:39:00.518Z        66fd22f4-63f7-4b36-9982-499393b4d316    INFO    final rating: 5
2022-09-30T07:39:00.810Z        66fd22f4-63f7-4b36-9982-499393b4d316    INFO    updated usrid: 29
2022-09-30T07:39:00.811Z        66fd22f4-63f7-4b36-9982-499393b4d316    INFO    {"updateRatingResult":[{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"Rows matched: 0  Changed: 0  Warnings: 0","serverStatus":3,"warningStatus":0,"changedRows":0},null]}
2022-09-30T07:39:15.518Z        66fd22f4-63f7-4b36-9982-499393b4d316    INFO    112
2022-09-30T07:39:15.519Z        66fd22f4-63f7-4b36-9982-499393b4d316    INFO    A very good seller. Nice
2022-09-30T07:39:15.519Z        66fd22f4-63f7-4b36-9982-499393b4d316    INFO    Excellent work
2022-09-30T07:39:15.523Z        66fd22f4-63f7-4b36-9982-499393b4d316    INFO    final rating: 4.5
2022-09-30T07:39:15.849Z        66fd22f4-63f7-4b36-9982-499393b4d316    INFO    updated usrid: 112
2022-09-30T07:39:15.850Z        66fd22f4-63f7-4b36-9982-499393b4d316    INFO    {"updateRatingResult":[{"fieldCount":0,"affectedRows":0,"insertId":0,"info":"Rows matched: 0  Changed: 0  Warnings: 0","serverStatus":3,"warningStatus":0,"changedRows":0},null]}

请问这是什么原因，该如何修复？

原因分析与修复方案 #

问题根源 #

代码中开启了namedPlaceholders，但执行UPDATE语句时混用了两种参数传递方式：SQL使用的是?（位置占位符），但传入的是对象{finalUserRating, userId}（命名占位符格式），导致参数无法正确匹配，数据库找不到对应记录，因此没有更新操作发生。

修复方式（二选一即可） #

1. 保持位置占位符，用数组传参
   修改UPDATE语句的执行代码，将对象参数改为数组，按SQL中?的顺序传递：

const updateRatingResult = await connection.query(updateRatingSql, [finalUserRating, userId]);

2. 改用命名占位符
   先修改SQL语句，使用命名占位符替代?：

const updateRatingSql = "UPDATE user_common SET rating = :finalUserRating WHERE iduser = :userId";

然后保持原有的对象参数传递方式即可，此时参数会和命名占位符一一对应。

额外优化建议 #

可以将整个逻辑合并为单条SQL语句，避免在Node.js中循环查询和计算，大幅提升性能：

UPDATE user_common uc
JOIN (
    SELECT to_user, AVG(rating) AS avg_rating
    FROM job_feedback
    GROUP BY to_user
) jf ON uc.iduser = jf.to_user
SET uc.rating = jf.avg_rating;

这条SQL直接在数据库层面完成评分聚合和更新，无需前端循环处理，效率更高。

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