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)";

实际执行的sql

UPDATE 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 ==== 搞定