在使用CSV从PHP更新MySQL时超时 [英] Timing out while updating MySQL with PHP from a CSV
问题描述
我需要提出一种方法，使一个大任务更快地超过超时。
I need to come up with a way to make a large task faster to beat the timeout.
我有非常
我有一个系统设置，其中cron访问一个PHP文件，抓取包含某些产品数据的csv。 csv不包含产品将具有的所有字段。只有几个必要的。
I have a system set up where a cron visits a PHP file that grabs a csv that contains data on some products. The csv does not contain all of the fields that the product would have. Just a handful of essential ones.
我已经阅读了大量关于超时和处理csv的文章，目前（试图刮胡子时间）我做了一个表（我们称之为csv_data）保存csv数据。我有一个脚本截断csv_data表，然后从csv插入数据，因此每晚csv的最新记录集在该表（csv文件每晚更新）。到目前为止，没有超时问题...任务只需要大约4-5秒。
I've read a fair number of articles on timeouts and handling csv's and currently (in an attempt to shave time) I have made a table (let's call it csv_data) to hold the csv data. I have a script that truncates the csv_data table then inserts data from the csv so each night the latest recordset from the csv is in that table (the csv file gets updated nightly). So far, no timeout problems..the task only takes about 4-5 seconds.
超时发生在我必须筛选数据以更新产品时表。它现在运行的步骤是这样
The timeouts occur when I have to sift through the data to make updates to the products table. The steps that it is running right now is like this
1. Get the sku from csv_data table (that holds thousands of records)
2. Select * from Products where products.sku = csv.sku (products table also holds thousands of records to loop through)
3. Get numrows.
If numrows<0{no record in products, so skip}.
If numrows>1{duplicate entries, don't change anything, but later on report the sku}
If numrows==1{Update selected fields in the products table with csv data}
4. Go to the next record in csv_data all over again
（我想大概说明过程较短，比起在代码中更容易。）
我查看了MySQl视图和存储过程，但我不熟练，知道它是否会处理'if'语句部分。
(I figured outlining the process is shorter and easier than dropping in the code.) I looked into MySQl views and stored procedures but I am not skilled enough in it to know if it will handle the 'if' statement portion.
有什么我可以做的更快，以避免超时吗？
Is there anything I can do to make this faster to avoid the timeouts?
我应该提到 set_time_limit （0）; 不是这样做。如果有帮助，服务器使用 IIS7 和 fastcgi
感谢您的帮助。
I should mention that set_time_limit(0); isn't doing it. And if it helps, the server uses IIS7 and fastcgi
Thanks for your help.
使用Jakob和Shawn的建议后更新：
Update after using suggestions from Jakob and Shawn:
我做错了。速度肯定更快，csv sku增加，
I'm doing something wrong. The speed is definitely faster and the csv sku is incrementing,
但是当我试图实现肖恩的解决方案;查询给我一个PHP警告：mysql_result（）期望参数1是资源，布尔错误。
but when I tried to implement Shawn's solution; the query is giving me a PHP Warning: mysql_result() expects parameter 1 to be resource, boolean error.
您能帮我找出我做错了什么吗？
Can you help me spot what I am doing wrong?
这是代码段：
$csvdata="SELECT * FROM csv_update";
$csvdata_result=mysql_query($csvdata);
mysql_query($csvdata);
$csvdata_num = mysql_num_rows($csvdata_result);
$i=0;
while($i<$csvdata_num){
$csv_code=@mysql_result($csvdata_result,$i,"skucode");
$datacheck=NULL;
$datacheck=substr($csv_code,0,1);
if($datacheck>='0' && $datacheck<='9'){
$csv_price=@mysql_result($csvdata_result,$i,"price");
$csv_retail=@mysql_result($csvdata_result,$i,"retail");
$csv_stock=@mysql_result($csvdata_result,$i,"stock");
$csv_weight=@mysql_result($csvdata_result,$i,"weight");
$csv_manufacturer=@mysql_result($csvdata_result,$i,"manufacturer");
$csv_misc1=@mysql_result($csvdata_result,$i,"misc1");
$csv_misc2=@mysql_result($csvdata_result,$i,"misc2");
$csv_selectlist=@mysql_result($csvdata_result,$i,"selectlist");
$csv_level5=@mysql_result($csvdata_result,$i,"level5");
$csv_frontpage=@mysql_result($csvdata_result,$i,"frontpage");
$csv_level3=@mysql_result($csvdata_result,$i,"level3");
$csv_minquantity=@mysql_result($csvdata_result,$i,"minquantity");
$csv_quantity1=@mysql_result($csvdata_result,$i,"quantity1");
$csv_discount1=@mysql_result($csvdata_result,$i,"discount1");
$csv_quantity2=@mysql_result($csvdata_result,$i,"quantity2");
$csv_discount2=@mysql_result($csvdata_result,$i,"discount2");
$csv_quantity3=@mysql_result($csvdata_result,$i,"quantity3");
$csv_discount3=@mysql_result($csvdata_result,$i,"discount3");
$count_check="SELECT COUNT(*) AS totalCount FROM products WHERE skucode = '$csv_code'";
$count_result=mysql_query($count_check);
mysql_query($count_check);
$totalCount=@mysql_result($count_result,0,'totalCount');
$loopCount = ceil($totalCount / 25);
for($j = 0; $j < $loopCount; $j++){
$prod_check="SELECT skucode FROM products WHERE skucode = '$csv_code' LIMIT ($loopCount*25), 25;";
$prodresult=mysql_query($prod_check);
mysql_query($prod_check);
$prodnum =@mysql_num_rows($prodresult);
$prod_id=@mysql_result($prodresult,0,"catalogid");
if($prodnum<1){
echo "NOT FOUND:$csv_code<br>";
$count_sku_not_found=$count_sku_not_found+1;
$list_sku_not_found=$list_sku_not_found." $csv_code";}
if($prodnum>1){
echo "DUPLICATE:$csv_ccode<br>";
$count_duplicate_skus=$count_duplicate_skus+1;
$list_duplicate_skus=$list_duplicate_skus." $csv_code";}
if ($prodnum==1){
///This prevents an overwrite from happening if the csv file doesn't produce properly
if ($csv_price!="" OR $csv_price!=NULL)
{$sql_price='price="'.$csv_price.'"';}
if ($csv_retail!="" OR $csv_retail!=NULL)
{$sql_retail=',retail="'.$csv_retail.'"';}
if ($csv_stock!="" OR $csv_stock!=NULL)
{$sql_stock=',stock="'.$csv_stock.'"';}
if ($csv_weight!="" OR $csv_weight!=NULL)
{$sql_weight=',weight="'.$csv_weight.'"';}
if ($csv_manufacturer!="" OR $csv_manufacturer!=NULL)
{$sql_manufacturer=',manufacturer="'.$csv_manufacturer.'"';}
if ($csv_misc1!="" OR $csv_misc1!=NULL)
{$sql_misc1=',misc1="'.$csv_misc1.'"';}
if ($csv_misc2!="" OR $csv_misc2!=NULL)
{$sql_pother2=',pother2="'.$csv_misc2.'"';}
if ($csv_selectlist!="" OR $csv_selectlist!=NULL)
{$sql_selectlist=',selectlist="'.$csv_selectlist.'"';}
if ($csv_level5!="" OR $csv_level5!=NULL)
{$sql_level5=',level5="'.$csv_level5.'"';}
if ($csv_frontpage!="" OR $csv_frontpage!=NULL)
{$sql_frontpage=',frontpage="'.$csv_frontpage.'"';}
$import="UPDATE products SET $sql_price $sql_retail $sql_stock $sql_weight $sql_manufacturer $sql_misc1 $sql_misc2 $sql_selectlist $sql_level5 $sql_frontpage $sql_in_stock WHERE skucode='$csv_code'";
mysql_query($import) or die(mysql_error("error updating in products table"));
echo "Update ".$csv_code." successful ($i)<br>";
$count_success_update_skus=$count_success_update_skus+1;
$list_success_update_skus=$list_success_update_skus." $csv_code";
//empty out variables
$sql_price='';
$sql_retail='';
$sql_stock='';
$sql_weight='';
$sql_manufacturer='';
$sql_misc1='';
$sql_misc2='';
$sql_selectlist='';
$sql_level5='';
$sql_frontpage='';
$sql_in_stock='';
$prodnum=0;
}
}
$i++;
}
推荐答案
返回第一行还是在读取期间在行之间？一个好的做法是处理你的查询块;首先做一个计数，看看你正在处理的SKU的记录数，循环通过较小的块（这些块的大小将取决于你必须做多少事情，每一行）。您更新的工作流程看起来更像这样：
Is it timing out before the first row is returned or is it between rows during the read? One good practice bit would be to handle your query in chunks; do a count first to see how many records you are dealing with for the SKU, the loop through smaller chunks (the size of these chunks would depend on how many things you have to do with each row). Your updated workflow would look more like this:
- 从CSV获取下一个SKU
- count：
SELECT COUNT（*）AS totalCount FROM products WHERE products.sku = csv.sku - 确定块大小此演示）
-
loopCount = ceil（totalCount / 25） - 使用如下的循环：
for（$ i = 0; $ i
<应该运行这样的查询：
SELECT * FROM products WHERE products.sku = csv.sku LIMIT（loopCount * 25），25 - Get next SKU from CSV
- Get a total count:
SELECT COUNT(*) AS totalCount FROM products WHERE products.sku = csv.sku - Determine chunk size (using 25 for this demo)
loopCount = ceil(totalCount / 25)- Loop through all results using a loop like this:
for($i = 0; $i < loopCount; $i++) - Inside your loop you should be running a query like this:
SELECT * FROM products WHERE products.sku = csv.sku LIMIT (loopCount*25), 25
您将要为SELECT块使用一个常量顺序;您的唯一ID可能是最好的。
You will want to use a constant order for your SELECT chunks; your unique ID would probably be best.
这篇关于在使用CSV从PHP更新MySQL时超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
