更新大型数据集

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

首先，在和列上添加索引，SymbolData_date其次，将更新查询更改为一个查询，这样您就不需要像这样循环更新：$sql&nbsp;=&nbsp;"UPDATE &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;datanyc&nbsp;AS&nbsp;a &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INNER&nbsp;JOIN&nbsp;datanyc&nbsp;b&nbsp;ON&nbsp;a.symbol&nbsp;=&nbsp;b.symbol &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;b.Data_date&nbsp;=&nbsp;'$Prevdate' &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SET&nbsp;a.Prevclose&nbsp;=&nbsp;b.last";$date1&nbsp;=&nbsp;mysqli_query($con,&nbsp;$sql);