php - Mysql:根据最小数量删除重复记录

Question

我需要根据相同订单号的最小数量删除重复记录。表有 100k 条记录，结构是这样的。tmp_id 是此表中的主键。

------------------------------------
user_id   order_number amount  tmp_id
-------------------------------------
15         12364        25      1
20         454544       75      2
4          12364        100     3
6          45487        45      4
8          454544       330     5

我这样尝试删除最小数量的重复记录使用

$qb_duplicate_data_query="select user_id,order_number,amount from tmp_qbappraiser limit 10";

$qb_duplicate_data_sql=mysql_query($qb_duplicate_data_query) or die(mysql_error());

while($row=mysql_fetch_array($qb_duplicate_data_sql)) {
     $amount=$row['amount'];
     $order_no=$row['order_number'];

     $sql="SELECT c1.tmp_id from tmp_qbappraiser c1
inner join (SELECT tmp_id FROM tmp_qbappraiser WHERE order_number='$order_no' ORDER BY amount asc) AS c 
on c.tmp_id=c1.tmp_id GROUP BY c1.order_number having count(order_number)>1";

     $query=mysql_query($sql) or die(mysql_error());

     $result=mysql_fetch_array($query);

     $db_tmp_id=$result['tmp_id'];

     if($db_tmp_id!='') {
         $delete_sql="delete from tmp_qbappraiser where order_number='$order_no' and tmp_id='$db_tmp_id'";

         $delete_query=mysql_query($delete_sql) or die(mysql_error());
     }

}

当我执行这段代码时，我得到了 lock wait timeout exceeded try restarting transaction 错误。如何优化我的查询或是否有任何其他方法可以删除数千条记录？

Answer 1

您不需要编写多个查询来删除重复的条目，这可以通过一个查询来完成。

delete t1 from tmp_qbappraiser t1
join tmp_qbappraiser t2
on t1.order_number = t2.order_number
and t1.amount < t2.amount ;

上述查询将保留每个 order_number 的最大金额，并将删除其余部分。

您可能需要在表中添加一些索引以使其更快

alter table tmp_qbappraiser add index ord_amt_idx(order_number,amount);

注意:确保在应用索引和运行删除查询之前对表进行备份。