我有以下代码:
$query = mysql_query("SELECT * FROM mytable");
while($row = mysql_fetch_assoc($query)){
mysql_query("INSERT INTO mytable2 (col1, col2)
VALUES ('".$row['val1']."', '".$row['val2']."')");
}
可以理解的是,脚本在大约 150,000 个查询时超时......除了增加脚本内存之外,防止超时的最佳方法是什么?
为什么不将其作为单个查询???运行
$SQL = "INSERT INTO mytable2 (col1,col2) SELECT val1,val2 FROM mytable";
$query = mysql_query($SQL);
另类
您也可以一次限制 200 个插入
$query = mysql_query("SELECT * FROM mytable");
$commit_count = 0;
$commit_limit = 200;
$comma = "";
$SQL = "INSERT INTO mytable2 (col1, col2) VALUES ";
while($row = mysql_fetch_assoc($query)){
$SQL .= $comma . "('".$row['val1']."','".$row['val2']."')";
$comma = ",";
$commit_count++;
if ( $commit_count == $commit_limit )
{
mysql_query($SQL);
$SQL = "INSERT INTO mytable2 (col1, col2) VALUES ";
$commit_count = 0;
$comma = "";
}
}
if ( $commit_count > 0 ) { mysql_query($SQL); }
您可以将$commit_limit
更改为任何合理的正数。
您应该考虑使用 INSERT ... SELECT
语句,而不是运行大量单个插入。