WithCoderWithCoderWithCoder

mysql删除重复记录并且只保留一条

    公司项目需要和第三方对接数据,因为后续要分析对方数据(分析的业务稍微复杂,有点耗时),所以先把对方数据取到后插入数据库,稍后另起任务分析原始数据。

    在插入数据的过程中,因为程序bug问题,同一条记录连续插入了多条,等发现的时候,已经有几万条重复的记录了。因为数据时实时数据,对方没有备份,所以没办法全部删除重复记录再重新获取一遍。这时需要根据固定的条件,查询出重复的记录,并且删掉重复的记录(每个重复的记录还要保留1条)。

    假设表名字为“tn_data”,可以判断数据重复的字段为“update_time”,表的结构如下:

CREATE TABLE `tn_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key_id` int(11) DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `data` mediumtext,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

    一、查询重复次数大于1的记录

    首先使用 GROUP BY 对 update_time 字段进行分组,且使用 HAVING 子句统计每个时间的记录个数,SQL语句伪代码如下:

SELECT COUNT(重复字段), 重复字段 FROM 表 GROUP BY 重复字段 HAVING COUNT(*) > 1

    针对上面的 tn_data表,查看是否有重复的数据SQL语句如下:

SELECT COUNT(update_time),update_time FROM tn_data GROUP BY update_time HAVING COUNT(*) > 1

    查询结果如下图所示:

    1-20060523063K92.png

    注意,HAVING 子句中,count函数的参数可以是 count(*)、count(1) 或 count(列名),它们在大部分情况下是没有区别的:

    1. ount(*) 与 count(1) 其实没有什么差别,用那个都可以

    2. count(*) 与 count(列名)的区别是,count(*) 将返回表格中所有存在的行的总数包括值为null的行,而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入)

    二、查询全部重复的数据:

    通过第一步的查询,可以查出重复次数大于1的记录。在此基础上,我们就可以查询出全部的重复记录,SQL伪代码如下:

SELECT * FROM 表 WHERE 重复字段 In (SELECT 重复字段 FROM 表 GROUP BY 重复字段 HAVING COUNT(*) > 1)

    针对本文的举例表,SQL语句如下:

SELECT * FROM tn_data WHERE update_time IN ( SELECT update_time FROM tn_data GROUP BY update_time HAVING COUNT(1) > 1 )

    执行结果如下图,可以看到查询出所有时间 update_time 重复的记录:

1-200605231J1M5.png

    三、 删除全部重复

    3.1 不保留任何重复记录的删除

    因为第二部已经查询出重复的记录,有了判断的依据,因此将上面的查询select改为delete进行删除操作(不过这样会出错的),SQL语句如下:

DELETE FROM tn_data WHERE update_time IN ( SELECT update_time FROM tn_data GROUP BY update_time HAVING COUNT(1) > 1 )

    注意执行SQL,会出现如下错误:

错误代码: 1093
You can't specify target table 'tn_data' for update in FROM clause

    查询原因,是因为更新这个表的同时又查询了这个表,查询这个表的同时又去更新了这个表,可以理解为死锁。mysql不支持这种更新查询同一张表的操作。

    解决办法:把要删除的数据查询出来做为一个第三方表,然后在进行查询删除,代码如下:   

DELETE FROM tn_data WHERE update_time IN (
 SELECT update_time FROM ( SELECT update_time FROM  tn_data  GROUP BY update_time HAVING COUNT(1) > 1 ) a
)

    执行以上的代码,会将重复的数据全部删掉(当然,在查询重复的SQL语句中,可以添加where子句,进行筛分) 。

    3.2 删除重复数据,每个重复的记录需保留1条(假设保留时间 update_time 最大的1条记录)

    在3.1的基础上,只要排除每个重复时间中 id 最大的记录即可,SQL语句修改如下:   

DELETE FROM tn_data WHERE update_time IN (
 SELECT update_time FROM ( SELECT update_time FROM  tn_data  GROUP BY update_time HAVING COUNT(1) > 1 ) a
)
AND id NOT IN (
 SELECT id FROM (SELECT MAX(id) AS id FROM tn_data GROUP BY update_time HAVING COUNT(1) > 1) b
)

    执行以上代码,会将重复的记录删除,但会保留重复数据中id最大的1条记录。

     四、最后

    在上面提到的解决方法中,SQL语句可能不是效率最优的,本文只是提供一种解决方案。当然,查询重复的SQL中也没有添加过滤筛选条件,大家在实际开发中,可以根据需要添加相应的where筛选条件。

    另外,上面的SQL仅针对是单个字段分组,但多个字段分组道理是一样的,只是将 group by 的字段增加为你想要的字段即可。

欢迎分享交流,转载请注明出处:WithCoder » mysql删除重复记录并且只保留一条