批量更新MySQL同客户ID未完成任务计数问题求助

我开发了一个任务展示应用，任务存储在MySQL数据库中，表结构为[id][task_description][customer_id][status][task_count]。需求是将每个同customer_id的未完成（status为'open'）任务记录的task_count字段更新为该客户的未完成任务总数，用于Handlebars页面展示。

最初编写的Node.js代码因异步回调特性，仅能更新最后一条记录，调试发现循环内异步回调触发时data变量始终为rows数组的最大索引值。尝试了一个临时解决方案，但效果不佳，现寻求正确的实现方法。

原代码 #

pool.getConnection((err, connection) => {
    if (err) throw err;

    connection.query("SELECT * FROM tasks WHERE status = 'open' ORDER BY customer_id DESC", (err, rows) => {
    if (err) throw err;

    for (data in rows) {
      debug("Current value of my data variable before the first query inside the for-statement: " + data);
      connection.query("SELECT * FROM tasks WHERE status = 'open' AND customer_id = ?", [rows[data].customer_id], (err, result) => {
        if (err) throw err;
        debug("Current value of my data variable after the first query inside the for-statement: " + data);

        connection.query("UPDATE tasks SET task_count = ? WHERE customer_id = ?", [result.length, rows[data].customer_id], (err, update) => {
          if (err) throw err;

          });
      });
    }
  });
});

调试输出 #

[DEBUG] [20.1.2023 13:21:31] Server: Current value of my data variable before the first query inside the for-statement: 0
[DEBUG] [20.1.2023 13:21:31] Server: Current value of my data variable before the first query inside the for-statement: 1
[DEBUG] [20.1.2023 13:21:31] Server: Current value of my data variable before the first query inside the for-statement: 2
[DEBUG] [20.1.2023 13:21:31] Server: Current value of my data variable before the first query inside the for-statement: 3
[DEBUG] [20.1.2023 13:21:31] Server: Current value of my data variable before the first query inside the for-statement: 4

[DEBUG] [20.1.2023 13:21:31] Server: Current value of my data variable after the first query inside the for-statement: 4
[DEBUG] [20.1.2023 13:21:31] Server: Current value of my data variable after the first query inside the for-statement: 4
[DEBUG] [20.1.2023 13:21:31] Server: Current value of my data variable after the first query inside the for-statement: 4
[DEBUG] [20.1.2023 13:21:31] Server: Current value of my data variable after the first query inside the for-statement: 4
[DEBUG] [20.1.2023 13:21:31] Server: Current value of my data variable after the first query inside the for-statement: 4

临时解决方案代码 #

var task_count = 0;
var temp_id;

pool.getConnection((err, connection) => {
  if (err) throw err;

  connection.query("SELECT * FROM tasks WHERE status = 'open' ORDER BY customer_id DESC", (err, sqlQuery) => {

    for (var data in sqlQuery) {

      if (!temp_id) {
        temp_id = sqlQuery[data].customer_id;
        task_count++;
       } else {
         if (temp_id != sqlQuery[data].customer_id) {
           sqlQuery[data].task_count = connection.query("UPDATE tasks SET task_count = ? WHERE customer_id = ?", [task_count, temp_id]);
           task_count = 1;
           temp_id = sqlQuery[data].customer_id;
          }
          else {
            task_count++;
          }
       }

    }
  });
});

方案1：SQL子查询批量更新（最优） #

直接用一条SQL语句完成所有更新，无需在Node.js层循环处理，效率更高：

UPDATE tasks t1
JOIN (
    SELECT customer_id, COUNT(*) as total_open_tasks
    FROM tasks
    WHERE status = 'open'
    GROUP BY customer_id
) t2 ON t1.customer_id = t2.customer_id
SET t1.task_count = t2.total_open_tasks
WHERE t1.status = 'open';

对应的Node.js代码：

pool.getConnection((err, connection) => {
    if (err) throw err;

    const updateQuery = `
        UPDATE tasks t1
        JOIN (
            SELECT customer_id, COUNT(*) as total_open_tasks
            FROM tasks
            WHERE status = 'open'
            GROUP BY customer_id
        ) t2 ON t1.customer_id = t2.customer_id
        SET t1.task_count = t2.total_open_tasks
        WHERE t1.status = 'open';
    `;

    connection.query(updateQuery, (err, result) => {
        if (err) throw err;
        debug(`更新了 ${result.affectedRows} 条记录`);
        connection.release();
    });
});

方案2：Async/Await处理异步循环 #

如果必须在Node.js层处理逻辑，改用async/await配合for...of循环，避免闭包导致的变量引用问题：

async function updateTaskCounts() {
    const connection = await pool.getConnection();
    try {
        const rows = await connection.query("SELECT * FROM tasks WHERE status = 'open' ORDER BY customer_id DESC");
        
        // 先统计每个客户的未完成任务数，避免重复查询
        const customerCounts = {};
        for (const row of rows) {
            if (!customerCounts[row.customer_id]) {
                const result = await connection.query(
                    "SELECT COUNT(*) as count FROM tasks WHERE status = 'open' AND customer_id = ?",
                    [row.customer_id]
                );
                customerCounts[row.customer_id] = result[0].count;
            }
        }

        // 逐个更新任务记录
        for (const row of rows) {
            await connection.query(
                "UPDATE tasks SET task_count = ? WHERE id = ?",
                [customerCounts[row.customer_id], row.id]
            );
        }

        debug("所有任务的task_count更新完成");
    } catch (err) {
        console.error(err);
        throw err;
    } finally {
        connection.release();
    }
}

// 执行更新
updateTaskCounts();

问题根源说明 #

原代码使用for...in循环搭配异步回调，由于JavaScript闭包特性，所有回调共享同一个data变量引用。循环快速执行完毕后，data已经指向数组最后一个索引，此时异步回调才开始执行，导致所有更新都使用了最后一个customer_id的值。

临时方案存在的问题：

• connection.query是异步操作，直接赋值给sqlQuery[data].task_count得到的是查询对象而非实际结果
• 循环结束后，最后一个客户的任务数未执行更新
• 未处理异步操作的错误，也没有释放数据库连接

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