MySQL-基于列子集重复项删除行

更准确的查询是:

DELETE t1
    FROM uploaddata_copy t1 JOIN
         uploaddata_copy t2
         ON t2.Name = t1.Name AND
            t2.ID = t1.ID AND
            t2.Order = t1.Order AND
            (t2.Count < t1.Count OR
             t2.Count = t1.Count AND t2.Date < t1.Date
            );

但是,修复逻辑不会(在这种情况下)提高性能。首先,您需要索引 uploaddata_copy(Name, Id, Order, Count, Date) 。这允许“查找”介于原始数据和索引之间。

第二,从小处着手。添加一个 LIMIT 1 或 LIMIT 10 查看只删除几行所需的时间。删除行是一个复杂的过程,因为它会影响表、索引和事务日志,更不用说表上的任何触发器了。

如果删除了很多行,您可能会发现重新创建表的速度更快,但这在很大程度上取决于删除的相对行数。

为什么加入?如果存在,则要删除行 存在 “更好”的记录。因此,请使用 EXISTS 条款:

delete from dup using uploaddata_copy as dup
where exists
(
  select *
  from uploaddata_copy better
  where better.name = dup.name
    and better.id = dup.id
    and better.order = dup.order
    and (better.count > dup.count or (better.count = dup.count and better.date > dup.date))
);

(请检查我的比较。我是这样理解的:名称+id+订单的更好记录的计数更大或计数相同且日期更高。您认为更差的记录是要删除的不需要的重复记录。)

你会有一个索引 uploaddata_copy(id, name, order) 至少或更好 uploaddata_copy(id, name, order, count, date) 要使此delete语句运行良好。

请尝试以下操作:

 DELETE t1 FROM uploaddata_copy t1
  JOIN uploaddata_copy t2
  ON t2.Name = t1.Name
  AND t2.ID = t1.ID
  AND t2.Order = t1.Order
  AND t2.Count < t1.Count
  AND t2.Date < t1.Date
  AND t2.primary_key != t1.primary_key