最新消息:

tip:删除表中的重复记录,并保留最新的一条记录

数据库 ksharpdabu 2476浏览 0评论

 

需求:

假设有一个表 test ,包含如下数据:

 

我们需要找到所有重复的email并删掉它们,同时只保留其中id最新的那条记录。这里我们以test,表,来说明,“aaa”,”bbb”和“eee”是重复的,所以我们需要删掉id为 ,1,7,2和6的记录。
创表语句:

 

第一种方法

先用email分组(group by),再找到该分组下的最大的id(最新的id,我们叫做 lastId),然后用email字段进行inner join,把join结果中 id小于lastId的删掉即可。

第一步:查找重复的记录

要想删除重复的记录,就必须先找到重复的记录,如下:

 

第二步:查找重复的记录,且找到包含最新id的记录


 

第三步:使用email字段来inner join ,删除重复的记录


 

删除后的结果:

 

第二种方法

用email进行分组,找到每个分组的最大的id,然后把test中id 不等于该值的记录删掉,如下:

 

第三种方法(使用临时表):

直接使用disnct来查询包含最新id的记录并导入到一张临时表中,然后将临时表重命名为原来的表明(当然也可以导回到原表)。因为有时候可能原来已经有了索引,此时test表数据量很大,而重复的数据很多,delete的时候还要不断维护索引,可能导致delete效率很低,还不如直接新建一个表把最新的唯一数据插入到新表中,插入完成后再恢复索引。
如果只是需要删掉重复值的要求,如下:

 

直接简化下:

 

如果是要求删掉重复记录且保留最新id的要求,如下

 

第四种方法(不推荐)

使用self join,这种方法效率低,如果表数据很多,不推荐这种。

 

删除重复记录方法

除了上面的一些删除重复的记录的方法(不仅删除,而且保留最新的一条记录),还有余下的几种,其实和前面的都差不多。

使用临时表+insert ignore

先用distinct将test表中的唯一数据被分到tempTest表,然后清空test表,对emai字段建立唯一索引,然后使用insert ignore方法将tempTest表中的数据拷贝回test表 :

 

使用ROW_NUMBER()函数来删除重复记录(仅支持mysql 8.x)

与oracle中的ROW_NUMBER()类似,我们对email字段进行分组,然后使用ROW_NUMBER()函数,为每个分组的每条记录的增加一个数字来表示这条是该分组的第几条数据,如果有重复的数据,那么这个数字肯定是大于1的,如下:

 

这个也可以改成保留最新的记录,不过个人感觉没必要了。

感想:

公司从oracle切换到mysql,会发现很多函数和功能在mysql中都没有,这不得不承认oracle真的很强大,商用的东西要完善很多,贵有贵的道理。
参考:
https://stackoverflow.com/questions/6107167/mysql-delete-duplicate-records-but-keep-latest/6108860
http://www.mysqltutorial.org/mysql-delete-duplicate-rows/

 

转载请注明:大步's Blog » tip:删除表中的重复记录,并保留最新的一条记录

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

网友最新评论 (2)

  1. 这好像是论坛上的帖子吧,这几天讨论过。
    antior7个月前 (11-05)Reply
  2. 那就把重复的非最新的记录找出来,然后删除
    wordpress建站吧5个月前 (12-30)Reply