使用原生 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 ==== 搞定