本文讲述如何查找数据库里重复的行。这是初学者十分普遍遇到的问题。方法也很简单。这个问题还可以有其他演变,例如,如何查找“两字段重复的行”(#mysql IRC 频道问到的问题)
如何查找重复行
第一步是定义什么样的行才是重复行。多数情况下很简单:它们某一列具有相同的值。本文采用这一定义,或许你对“重复”的定义比这复杂,你需要对sql做些修改。
本文要用到的数据样本
create table test(id int not null primary key, day date not null); insert into test(id, day) values(1, '2006-10-08'); select * from test; |
前面两行在day字段具有相同的值,因此如何我将他们当做重复行,这里有一查询语句可以查找。查询语句使用GROUP BY子句把具有相同字段值的行归为一组,然后计算组的大小。
select day, count(*) from test GROUP BY day; +------------+----------+ | day | count(*) | +------------+----------+ | 2006-10-08 | 2 | | 2006-10-09 | 1 | +------------+----------+ |
重复行的组大小大于1。如何希望只显示重复行,必须使用HAVING子句,比如
select day, count(*) from test group by day HAVING count(*) > 1; +------------+----------+ | day | count(*) | +------------+----------+ | 2006-10-08 | 2 | +------------+----------+ |
这是基本的技巧:根据具有相同值的字段分组,然后知显示大小大于1的组。
为什么不能使用WHERE子句?
因为WHERE子句过滤的是分组之前的行,HAVING子句过滤的是分组之后的行。
如何删除重复行
一个相关的问题是如何删除重复行。一个常见的任务是,重复行只保留一行,其他删除,然后你可以创建适当的索引,防止以后再有重复的行写入数据库。
同样,首先是弄清楚重复行的定义。你要保留的是哪一行呢?第一行,或者某个字段具有最大值的行?本文中,假设要保留的是第一行——id字段具有最小值的行,意味着你要删除其他的行。
也许最简单的方法是通过临时表。尤其对于MYSQL,有些限制是不能在一个查询语句中select的同时update一个表。在我的另一篇文章中 MySQL SELECT同时UPDATE同一张表(How to select from an update target in MySQL), 讲述了如何绕过这些限制。简单起见,这里只用到了临时表的方法。
我们的任务是:删除所有重复行,除了分组中id字段具有最小值的行。因此,需要找出大小大于1的分组,以及希望保留的行。你可以使用MIN()函数。这里的语句是创建临时表,以及查找需要用DELETE删除的行。
create temporary table to_delete (day date not null, min_id int not null); insert into to_delete(day, min_id) select * from to_delete; |