更新大型数据集

Question

我可以上传一个包含 3000 条记录的 csv 文件，但是当我尝试更新时，需要很长时间，这会导致请求超时错误。插入：$sqlInsert = "INSERT ignore into datanyc (`Symbol`,`Data_date`,`Open`,`High`,`Low`,`Last`,`Volume`)                values ('".$column0."','".$dataDate."','".$column[2]."','".$column[3]."',                '".$column[4]."','".$column[5]."','".$column[6]."')";                       更新声明：$date_query="SELECT max(Data_date) as Prev_date FROM datanyc WHERE Data_date < '$dataDate' ";$date_result=mysqli_query($con,$date_query);$fetch=mysqli_fetch_array($date_result);$Prevdate=$fetch['Prev_date'];$temp="SELECT Last as last, Symbol as symbol FROM datanyc WHERE Data_date = '$Prevdate'";$date1=mysqli_query($con,$temp);while($row=mysqli_fetch_array($date1)){    //array_push($symbol,$row['symbol']);    //array_push($last,$row['last']);    $qry="UPDATE datanyc SET Prevclose = '".$row['last']."' WHERE Symbol LIKE '".$row['symbol']."' AND Data_date= $dateDate";    mysqli_query($con,$qry);    //$date=$row['max(Data_date)'];}

Answer 1

首先，在Symbol和Data_date列上添加索引，

其次，将您的更新查询更改为一个查询，因此您不需要像这样循环更新：

$sql = "UPDATE

          datanyc AS a

        INNER JOIN datanyc b ON a.symbol = b.symbol

                     AND b.Data_date = '$Prevdate'

        SET a.Prevclose = b.last";

$date1 = mysqli_query($con, $sql);