php更新mysql大量数据_php mysql 批量更新数据

使用原生 sql 批量更新用户积分数据

订单列表中查出 $list，包含  buy_id , total_money 字段    $list = [

0 => ['buyer_id' => 1,'total_money' => 20],

1 => ['buyer_id' => 4,'total_money' => 77],

2 => ['buyer_id' => 7,'total_money' => 50],

3 => ['buyer_id' => 10,'total_money' => 15],

4 => ['buyer_id' => 21,'total_money' => 22],

5 => ['buyer_id' => 17,'total_money' => 120],

];

组合UPDATE语句，其中 `bp` 为用户表积分字段

$ids = '';

$sql = 'UPDATE user SET bp = CASE id ';

$sql = '';

foreach ( $list as $key => $value) {

$sql .= sprintf("WHEN %d THEN `bp` + %d ", $value['buyer_id'], $value['total_money']);

$ids .= $value['buyer_id'].',';

}

$ids = rtrim($ids,',');

$sql .= "END WHERE id IN ($ids)";

实际执行的sqlUPDATE user SET bp = CASE id WHEN 1 THEN `bp` + 20 WHEN 4 THEN `bp` + 77 WHEN 7 THEN `bp` + 50 WHEN 10 THEN `bp` + 15 WHEN 21 THEN `bp` + 22 WHEN 17 THEN `bp` + 120 END WHERE id IN (1,4,7,10,21,17)执行sql ==== 搞定