php之批量更新mysql数据库字段

       为了提高数据库的响应速度，希望仅通过访问一次数据库完成对一个字段中多个记录的更新操作，也就是批量更新，在网上查了许多关于批量更新的例子，一直没找合适的，不过网上的资料给了我不少启发，现将这几天实现的代码与大家分享。

首先简述一下我的问题，目的：根据数据库中图片的id字段实现对所有Score字段的更新，假设图片有200张，即200个记录。数据库中的update操作可以说是非常耗时的，有些人宁愿使用delete加insert操作完成，也不愿意用update。但是有些情况并不适用。

首先上传之前没有优化的代码，即一行一行的循环更新，即多次访问数据库。

$query =  'SELECT * FROM facemash WHERE IA = 1';
$result = mysql_query ($query);
while ( $obj = mysql_fetch_object ( $result ) ) {
		$return [] = $obj;
	}		
$learn_rate = 0.1;
$lamba = 0.01;
		
foreach ($return as $value){
	$grad = exp($x2->Score-$x1->Score)*(($x1->Score)-($x2->Score))+2*$lamba*$value->Score;
	$value->Score = $value->Score - $learn_rate*$grad;
	$sql = sprintf('UPDATE facemash SET Score = "%f" WHERE ID = "%s"',$value->Score,$value->id)
	$result = mysql_query ($sql);}	$sql = sprintf('UPDATE facemash SET Score = "%f" WHERE ID = "%s"',$value->Score,$value->id)
	$result = mysql_query ($sql);}

这样就是总的访问200次数据库才能完成修改，效果可想而知，慢的不行。参考网上的有以下几种方式：

 

1、小批量的更新

 

$sql = "UPDATE facemash SET Score = CASE id 
			WHEN '0141e381ed555a95fc8974cb1d6affd9' THEN 121.44
			WHEN '018949c3e777d50802ae4ad4e36b4413' THEN 121.46022
			WHEN '034f48f56a758f3565ad5c76a8b18b49' THEN 98 
		END WHERE ID IN ('0141e381ed555a95fc8974cb1d6affd9','018949c3e777d50802ae4ad4e36b4413','034f48f56a758f3565ad5c76a8b18b49')";
$result = mysql_query ( $sql ) or die(mysql_error());

缺点：只能手动将填写修改后的值及主键值，效率低，不适用于规模大的记录。

 

2、大批量的更新，这里直接上我修改好的。

 

	foreach ($return1 as $value){
		$grad = exp($x2->Score-$x1->Score)*($x1->Score-$x2->Score)+2*$lamba*$value->Score;
		$value->Score = $value->Score - $learn_rate*$grad;
		$value_array[] = $value->Score;
		$string_id = sprintf("'%s'",$value->id);
		$id_array[] = $string_id;
			
		}
		
		$ids = implode(',',$id_array);
		$sql = "UPDATE facemash SET Score = CASE id ";
		for($i=0; $i<count($value_array); $i++){
			$sql .= sprintf("WHEN %s THEN %f ",$id_array[$i],$value_array[$i]);
		}
		$sql .= "END WHERE ID IN ($ids)";
		$result = mysql_query( $sql ) or die( mysql_error());foreach ($return1 as $value){
		$grad = exp($x2->Score-$x1->Score)*($x1->Score-$x2->Score)+2*$lamba*$value->Score;
		$value->Score = $value->Score - $learn_rate*$grad;
		$value_array[] = $value->Score;
		$string_id = sprintf("'%s'",$value->id);
		$id_array[] = $string_id;
			
		}
		
		$ids = implode(',',$id_array);
		$sql = "UPDATE facemash SET Score = CASE id ";
		for($i=0; $i<count($value_array); $i++){
			$sql .= sprintf("WHEN %s THEN %f ",$id_array[$i],$value_array[$i]);
		}
		$sql .= "END WHERE ID IN ($ids)";
		$result = mysql_query( $sql ) or die( mysql_error());

        思路是：先求出字段更新之后所有的值存放在数组当中，然后在循环之外一并提交完成更新。这里更新的是Score字段，主键是id，关键在于如何让数据库执行的时候将主键和得分一一对应起来，格式很重要。

 

这里简单说明一下，首先借鉴前面形式，将你要更新的字段语句通过串接字符".="连接起来，对应下一段

$sql = "UPDATE facemash SET Score = CASE id ";
for($i=0; $i<count($value_array); $i++){
$sql .= sprintf("WHEN %s THEN %f ",$id_array[$i],$value_array[$i]);
}
$sql .= "END WHERE ID IN ($ids)";

        这一步最为关键，主要是变量$ids的格式，可能大家都在网上查到加上这一句$ids = implode(',',$id_array);数据库就能识别出每一个id对应的字符串了，这得取决于数组$id_array的形式了，查询所返回的结果id，如果直接存入$id_array，在执行完implode语句之后，就会把$ids当成一个字符串，尽管我们知道有","分割，但是这个字符串不能被IN关键字当做若干个id的集合，只有在每个id字符串加上单引号，才能识别出，这一步花了好长时间才OK，那如何加引号呢？只要在创建$id_array数组,对每一个id格式化输出即可，没错，就是加这一句

$string_id = sprintf("'%s'",$value->id);
$id_array[] = $string_id;

到这里所有问题基本解决，当然，并不是所有都要加引号，假如前面的键值是数字的话，加上逗号，IN关键字还是能够识别出来的。