node - 在完成原始查询之前几分钟后重复几分钟的mysql查询

I am building an app with angular and node js. I have a very big query (insert query) that inserts something like 30,000 rows which for some reason takes a few minutes (i suppose its ok).

我正在构建一个带角度和节点js的应用程序。我有一个非常大的查询(插入查询)插入类似30,000行的东西,由于某种原因需要几分钟(我想它可以)。

this is my query:

这是我的疑问:

this.createCourts = function (req, res, next){
  console.log("entered creation courts");
      connection.query('CALL filldates("' + 
        req.body['startDate'] + '","' +
        req.body['endDate'] + '","' +
        req.body['numOfCourts'] + '","' +
        req.body['duration'] + '","' +
        req.body['sundayOpen'] + '","' +
        req.body['mondayOpen'] + '","' +
        req.body['tuesdayOpen'] + '","' +
        req.body['wednesdayOpen'] + '","' +
        req.body['thursdayOpen'] + '","' +
        req.body['fridayOpen'] + '","' +
        req.body['saturdayOpen'] + '","' +
        req.body['sundayClose'] + '","' +
        req.body['mondayClose'] + '","' +
        req.body['tuesdayClose'] + '","' +
        req.body['wednesdayClose'] + '","' +
        req.body['thursdayClose'] + '","' +
        req.body['fridayClose'] + '","' +
        req.body['saturdayClose'] +
        '");', function(err, result, fields){

        if (err){
          console.log("error is" + err);
          return res.send(500, "fail");              
        }
        else{
          console.log("finsihed");
          return res.send(200);
        }
      });
  });
};

my ui will click a button which will trigger a service like this:

我的ui会点击一个按钮,它会触发这样的服务:

  CourtsService.createCourts.save({}, 
    {"startDate": dateService.Date_toYMD($scope.startDate), 
    "endDate": dateService.Date_toYMD($scope.endDate), 
    "numOfCourts": $scope.numOfCourts, 
    "duration": $scope.duration, 
    "sundayOpen": $scope.sundayOpen.hour, 
    "mondayOpen": $scope.mondayOpen.hour, 
    "tuesdayOpen": $scope.tuesdayOpen.hour, 
    "wednesdayOpen": $scope.wednesdayOpen.hour, 
    "thursdayOpen": $scope.thursdayOpen.hour, 
    "fridayOpen": $scope.fridayOpen.hour, 
    "saturdayOpen": $scope.saturdayOpen.hour, 
    "sundayClose": $scope.sundayClose.hour,
    "mondayClose": $scope.mondayClose.hour,
    "tuesdayClose": $scope.tuesdayClose.hour,
    "wednesdayClose": $scope.wednesdayClose.hour,
    "thursdayClose": $scope.thursdayClose.hour,
    "fridayClose": $scope.fridayClose.hour,
    "saturdayClose": $scope.saturdayClose.hour},
     function(data){
      $scope.showSearching = false;
      $dialog.messageBox("success", btns).open();
  }, function(err){
      $scope.showSearching = false;
      $dialog.messageBox("fail", err.data, btns).open();
  });

I added this row on the first line of the function that will actually do the query(its in the code up here) console.log("entered creation courts");

我在函数的第一行添加了这行,它将实际执行查询(在此处的代码中)console.log(“进入创建法院”);

My problem is that because the query runs for a few minutes (may be 10 minutes), it seems as if my function is being called again and again because I can see in my console the line "entered creation courts" approximately every 1 minute and this is ofcourse not something i wish to be happening.

我的问题是,因为查询运行了几分钟(可能是10分钟),似乎我的功能被一次又一次地调用,因为我可以在我的控制台中看到大约每1分钟“进入创建法院”的行这不是我希望发生的事情。

I don't know what triggers the createCourts function again and again.

我不知道是什么一次又一次触发了createCourts函数。

maybe it is because the UI goes into time out and thus triggers the service again if an answer hasn't been received after 1 minutes (or something close to that). and if this is the reason how do I tell the service to just wait for an answer?

可能是因为UI进入超时状态,因此如果在1分钟后(或接近该时间点)接收到答案,则再次触发服务。如果这是我如何告诉服务等待答案的原因?

1 个解决方案

#1

Consider async api: on the server handler immediately respond with HTTP 200 and some id, and there is another endpoint to check if insert command with id finished.

考虑async api:在服务器处理程序上立即响应HTTP 200和一些id,还有另一个端点来检查id命令的insert命令是否已完成。

Be aware that your code prone to sql injection attack. Better approach:

请注意,您的代码容易出现sql注入攻击。更好的方法:

var params = req.body; // you may want to filter names explicitly here
connection.query('CALL filldates(?,?,?,?,...)', params, function(err, result, fields){
    if (err){
      console.log("error is" + err);
      return res.send(500, "fail");              
    }
    else{
      console.log("finsihed");
      return res.send(200);
    }
});

This would escape all dangerous chars on client before sending query, or use prepared statements with mysql2 to send query intdependent of parameters:

这将在发送查询之前逃避客户端上的所有危险字符,或者使用mysql2的预准备语句来发送与参数无关的查询:

var params = req.body; // you may want to filter names explicitly here
connection.execute('CALL filldates(?,?,?,?,...)', params, function(err, result, fields){
    if (err){
      console.log("error is" + err);
      return res.send(500, "fail");              
    }
    else{
      console.log("finsihed");
      return res.send(200);
    }
});

'few minutes' IMHO is too much, in my benchmarks I have insert rate around 10k rows / second. Try replace inert calls with generation of large insert query text and test with console client. If it still takes few minutes then problem isn't in node land, you need to optimize your database.

'几分钟'恕我直言,太多了,在我的基准测试中,我的插入速率大约为10k行/秒。尝试使用生成大型插入查询文本替换惰性调用并使用控制台客户端进行测试。如果它仍然需要几分钟,那么问题不在节点域中,您需要优化您的数据库。

#1