mysql - 使用mysql和nodeJs更新多条记录

Question

我正在尝试使用 NodeJS 和 Sequelize 更新满足特定条件的多条记录。例如，如果student_id为1且advisor_id为1，则将分数设置为90，如果student_id为1且advisor_id为2，则将分数设置为80，依此类推......每个学生由3位顾问评估。在我的分数表中，我有id、student_id、advisor_id、score。

<小时/>

下面是用户传递的 JSON 数据示例。

 {
      "students": [
          {
            "student_id": 1,
            "committee": [
                { "advisor_id": 1, "score": 90 },
                { "advisor_id": 2, "score": 80 },
                { "advisor_id": 3, "score": 90 },
              ]
          },
          {
            "student_id": 2,
            "committee": [
                { "advisor_id": 1, "score": 70 },
                { "advisor_id": 2, "score": 70 },
                { "advisor_id": 3, "score": 80 },
              ]
          },
          {
            "student_id": 3,
            "committee": [
                { "advisor_id": 1, "score": 90 },
                { "advisor_id": 2, "score": 70 },
                { "advisor_id": 3, "score": 70 },
              ]
          },
          {
            "student_id": 4,
            "committee": [
                { "advisor_id": 1, "score": 80 },
                { "advisor_id": 2, "score": 90 },
                { "advisor_id": 3, "score": 80 },
              ]
          },

        ]  
    }

Answer 1

你可以这样做:

var queries = '';

  values.forEach(function (item) {
    queries += mysql.format("UPDATE tabletest SET score = 90 WHERE 
  student_id = 1 AND advisor_id = 1;");
    queries += mysql.format("UPDATE tabletest SET score = 80 WHERE 
  student_id = 1 AND advisor_id = 2;");
  });

  connection.query(queries, defered.makeNodeResolver());

要使用多语句功能，您必须为您的连接启用它:

var connection = mysql.createConnection({
  ...
  multipleStatements: true,
});