删除重复行MySQL

Question

我们希望删除MySQL数据库中重复的行，我们已经尝试了很多查询，但不幸的是，我们还没有成功。我们在几个帖子中找到了这个查询，但也没有起作用：

DELETE t1 FROM Raw_Validated_backup AS t1 INNER JOIN Raw_Validated_backup AS t2 
    ON t1.time_start=t2.time_start 
    AND t1.time_end=t2.time_end 
    AND t1.first_temp_lpn=t2.first_temp_lpn 
    AND t1.first_WL=t2.first_WL 
    AND t1.first_temp_lpn_validated=t2.first_temp_lpn_validated 
    AND t1.second_temp_lpn=t2.second_temp_lpn 
    AND t1.second_WL=t2.second_WL 
    AND t1.second_temp_lpn_validated=t2.second_temp_lpn_validated 
    AND t1.third_temp_lpn=t2.third_temp_lpn 
    AND t1.third_WL=t2.third_WL 
    AND t1.third_temp_lpn_validated=t2.third_temp_lpn_validated 
    AND t1.first_temp_rising=t2.first_temp_rising 
    AND t1.first_WR=t2.first_WR 
    AND t1.first_temp_rising_validated=t2.first_temp_rising_validated 
    AND t1.second_temp_rising=t2.second_temp_rising 
    AND t1.second_WR=t2.second_WR 
    AND t1.second_temp_rising_validated=t2.second_temp_rising_validated 
    AND t1.third_temp_rising=t2.third_temp_rising 
    AND t1.third_WR=t2.third_WR 
    AND t1.third_temp_rising_validated=t2.third_temp_rising_validated 
    AND t1.id<t2.id;

运行查询后收到的消息：No errors, 0 rows affected, taking 40,4 s

Answer 1

此查询：

select max(id) id
from Raw_Validated_backup
group by <list of all the columns except id>

返回要保留的行的所有ids。

因此，删除其余的：

delete from Raw_Validated_backup
where id not in (
  select t.id from (
    select max(id) id
    from Raw_Validated_backup
    group by <list of all the columns except id>
  ) t
)

见演示。

另一个具有自联接的选项：

delete v1 
from Raw_Validated_backup v1 inner join Raw_Validated_backup v2
on v1.time_start = v2.time_start and v1.time_end = v2.time_end and .......
and v1.id < v2.id;

请参阅简化的演示。