【mysql】删除重复数据

最近因为发现数据库中的表有脏数据，需要维护。这些脏数据就是重复数据，需要将其删除。

可能因为你在建表的时候考虑欠佳，需要为表里面的几个字段建立一个（联合）唯一索引，但是没有建立，而由于不止一个写的程序在往表里面insert数据，造成数据的重复~~

现在需要删除这些重复数据，看了看网上前辈们写的例子，大多数不能用，rowid也出来了，而mysql中是没有rowid的。

 

现假设有一张t_test表，主键字段为id，还有date，time，cnt1，cnt，cnt3三个字段。假设date，time组合起来规定只能有一条记录（即需要为date，time建立联合唯一索引）。表中数据如下：

 

可以看出：表中数据明显有不满足条件的重复数据。

 

我们先查询出有哪些重复数据（按date, time两个字段）：

SELECT * FROM t_test WHERE (DATE, TIME) IN(SELECT DATE,TIME FROM t_test GROUP BY DATE, TIME HAVING COUNT(1)>1);

 结果如下：

 尝试使用网上的方法删除：

DELETE FROM t_test a WHERE (a.date, a.time) IN(SELECT DATE,TIME FROM t_test GROUP BY DATE, TIME HAVING COUNT(1)>1)
AND rowid NOT IN(SELECT MIN(rowid) FROM t_test GROUP BY DATE, TIME HAVING COUNT(1)>1)

 根本行不通，因为rowid在MySQL里面是不存在的，这不同于Oracle。。

还需注意的一点是：mysql中不支持在delete语句里使用表别名，所以无法进行自连接来删除表中的记录！

 

解决方法：使用一个中间临时表过渡~~

首先，建立一个临时表如下：

CREATE TEMPORARY TABLE tmp AS SELECT MIN(id) FROM t_test GROUP BY DATE,TIME 

 查看临时表tmp的内容：

SELECT * FROM tmp

 得到：

 这张临时表记录了重复记录里id最小的主键，以及没有重复记录的主键信息。

接下来，删除不在里面的记录即可：

DELETE FROM t_test WHERE id NOT IN(SELECT * FROM tmp) 

 检查下现在的记录：

SELECT * FROM t_test

 发现：

 发现，记录终于“干净”了。。重复性的记录被成功删除了！

当然比较保险的做法是建表时期就给date和time字段加上一个联合索引。或者删除重复记录之后再alter table加上一个联合索引即可。