Node.js中批量更新多行数据为何出现SQL语法错误？

我编写了一段用于批量更新用户任务数据的Node.js代码：

exports.createTaskDataForNewDay = async function(values) {
  try {
    console.log("values", JSON.stringify(values))
    let pool = await CreatePool() //[timestamp , requiredTimes , reward , difficulty ,taskId , uid , csn]
    let query = "update userTaskData set timestamp = ?,requiredTimes=?,timesCompleted=0,reward=?,difficulty=?,state=1,taskId=?,replacedF=0,replacedC=0 where uid =? and suitCase = ?"
    let resp = await pool.query(query, [values])
    if (resp.changedRows > 0) {
      return resp
    } else return {
      code: 400,
      mesage: "Could not insert data ! please try again or check syntax"
    }
  } catch (error) {
    console.error(error)
    return {
      code: 500,
      message: error.message
    }
  }
}

传入的values是数组的数组，每个子数组对应一行待更新数据的占位符值。但执行时出现SQL语法解析错误，生成的SQL语句如下：

update userTaskData set timestamp = (1686124176992, 1, '{\\"t\\":\\"c\\",\\"v\\":1000}', 1, 't1', '21GGZzSudOdUjKXcbVQHtFtTK772', 1), (1686124176992, 3, '{\\"t\\":\\"g\\",\\"v\\":10}', 1, 't9', '21GGZzSudOdUjKXcbVQHtFtTK772', 1), (1686124176992, 5, '{\\"t\\":\\"c\\",\\"v\\":4000}', 2, 't17', '21GGZzSudOdUjKXcbVQHtFtTK772', 1), (1686124176992, 3, '{\\"t\\":\\"c\\",\\"v\\":1000}', 3, 't21', '21GGZzSudOdUjKXcbVQHtFtTK772', 1),requiredTimes=?,timesCompleted=0,reward=?,difficulty=?,state=1,taskId=?,replacedF=0,replacedC=0 where uid =? and suitCase = ?

可以看到所有子数组被填充到第一个占位符中，而相同的传参方式在INSERT语句中可正常工作，请问我哪里出错了？

问题根源 #

你用了单条UPDATE语句的写法，但传入了批量参数数组。MySQL的INSERT支持INSERT INTO ... VALUES (...), (...)的批量语法，驱动会自动把二维数组转换成对应格式，但UPDATE没有这种直接的批量语法，所以驱动把整个二维数组塞进了第一个?占位符，导致SQL结构彻底混乱。

解决方案 #

要实现批量更新，需要用CASE WHEN构造支持批量更新的SQL语句，或者循环执行单条UPDATE（效率较低）。这里推荐CASE WHEN的方式，同时调整参数传递逻辑：

exports.createTaskDataForNewDay = async function(values) {
  try {
    console.log("values", JSON.stringify(values))
    let pool = await CreatePool() 
    // 子数组结构：[timestamp, requiredTimes, reward, difficulty, taskId, uid, csn]
    
    // 构造CASE WHEN的更新片段
    const caseSegment = values.map(() => 'WHEN uid = ? AND suitCase = ? THEN ?').join(' ');
    const whereInSegment = values.map(() => '(?, ?)').join(', ');

    // 拼接完整的批量更新SQL
    const query = `
      UPDATE userTaskData 
      SET 
        timestamp = CASE ${caseSegment} ELSE timestamp END,
        requiredTimes = CASE ${caseSegment} ELSE requiredTimes END,
        reward = CASE ${caseSegment} ELSE reward END,
        difficulty = CASE ${caseSegment} ELSE difficulty END,
        state = 1,
        taskId = CASE ${caseSegment} ELSE taskId END,
        timesCompleted = 0,
        replacedF = 0,
        replacedC = 0
      WHERE (uid, suitCase) IN (${whereInSegment})
    `;

    // 展开参数数组，匹配所有占位符
    const params = [];
    values.forEach(item => {
      // 为每个字段的CASE语句添加参数：uid, csn, 字段值
      params.push(item[5], item[6], item[0]); // timestamp
      params.push(item[5], item[6], item[1]); // requiredTimes
      params.push(item[5], item[6], item[2]); // reward
      params.push(item[5], item[6], item[3]); // difficulty
      params.push(item[5], item[6], item[4]); // taskId
      // WHERE IN的参数：uid, csn
      params.push(item[5], item[6]);
    });

    const resp = await pool.query(query, params);
    if (resp.changedRows > 0) {
      return resp;
    } else {
      return {
        code: 400,
        message: "未找到匹配记录或无数据更新"
      };
    }
  } catch (error) {
    console.error(error);
    return {
      code: 500,
      message: error.message
    };
  }
}

额外修正 #

原代码中返回的错误信息里mesage拼写错误，已修正为message。

补充说明 #

INSERT能直接用二维数组是因为MySQL原生支持批量插入语法，而UPDATE没有对应的批量语法，必须手动通过CASE WHEN来实现批量更新逻辑，同时要确保参数数组和SQL中的占位符数量完全匹配。

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