从MySQL数据库中删除重复的条目
我有一个表中有8列的表,但随着时间的推移,我拿起了许多重复。我用另一个类似的话题看了另一个问题,但它并没有解决我目前遇到的问题。从MySQL数据库中删除重复的条目
+---------------------------------------------------------------------------------------+
| id | market | agent | report_name | producer_code | report_date | entered_date | sync |
+---------------------------------------------------------------------------------------+
定义唯一条目的定义基于市场,代理,report_name,producer_code和report_date字段。我正在寻找的是一种列出所有重复条目并将其删除的方法。或者只是删除重复的条目。
我曾经想过用脚本来完成它,但是表中包含2.5mil条目,所花费的时间将是不可行的。
有人可以提出任何替代方案吗?我看到有人得到使用以下查询副本的列表,但不知道如何使它适应我的情况:
SELECT id, count(*) AS n
FROM table_name
GROUP BY id
HAVING n > 1
以下是您可能考虑的两种策略。您必须根据您实际考虑的重复情况来调整用于选择重复项的列。我只是列出了除id列之外的所有列出的列。
第一个简单地创建一个没有重复的新表。有时候这比尝试删除所有违规行更快更容易。只需创建一个新表,插入唯一的行(我使用min(id)作为结果行的id),重命名这两个表,并且(一旦您满意所有事情都可以正常工作),请删除原始表。当然,如果你有任何外键约束,你也必须处理这些。
create table table_copy like table_name;
insert into table_copy
(id, market, agent, report_name, producer_code, report_date, entered_date, sync)
select min(id), market, agent, report_name, producer_code, report_date,
entered_date, sync
from table_name
group by market, agent, report_name, producer_code, report_date,
entered_date, sync;
RENAME TABLE table_name TO table_old, table_copy TO table_name;
drop table table_old;
第二个策略,刚刚删除重复项,使用临时表来保存哪些行有重复,因为MySQL不会允许您从您从在子查询中删除同一表中选择信息。只需创建一个临时表,其中包含标识重复项的列以及实际上将保留该id的id列,然后您可以执行多表删除,您可以在其中加入两个表以选择重复项。
create temporary table dups
select min(id), market, agent, report_name, producer_code, report_date,
entered_date, sync
from table_name
group by market, agent, report_name, producer_code, report_date,
entered_date, sync
having count(*) > 1;
delete t
from table_name t, dups d
where t.id != d.id
and t.market = d.market
and t.agent = d.agent
and t.report_name = d.report_name
and t.producer_code = d.producer_code
and t.report_date = d.report_date
and t.entered_date = d.entered_date
and t.sync = d.sync;
您可以找到受骗者,根据您的“重点”领域,这样做:
select id, count(*) as row_count
from table
group by market, agent, report_name, producer_code, report_date
having (row_count > 1)
然后,您可以在删除脚本中使用它。当然,您必须非常小心,因为它会返回所有重复的行,并且您希望至少保存每个分组中的其中一行。
您也可以在唯一条目所基于的列上使用主键,这将防止添加具有重复详细信息的新记录。
请你举个例子说明我会如何去做这个请吗? – Skippy 2011-03-17 06:27:15
@Surim:虽然主键是防止数据冗余的好方法,但是如果数据库作为任何应用程序的后端,最好防止从前端进行重复,也就是使用应用程序逻辑可以查看http://databases.about.com/cs/administration/g/primarykey.htm以了解有关主键的更多信息,并且在此处:http://www.danielschneller.com/2007/05/mysql- add-primary-key-to-table-with.html将其添加到表中的方法。 – Kushal 2011-03-17 12:58:51
这个系统有点旧,原本没有任何检查来防止被骗。我现在已经添加了,所以这很好,这只是一个必须删除当前累积的重复数据的情况。 – Skippy 2011-03-18 09:49:38
另一种简单的方法是将
- 创建新表
- 把唯一索引,你必须是唯一的字段(主键是一种特殊的唯一索引)
- 使用INSERT IGNORE INTO新建表SELECT * FROM oldtable(ORDER BY如果您希望最后的/第一个记录保留 - 应该在其他列中有所不同)
- DROP旧表和RENAME新桌到旧桌
太好了,刚刚尝试过你的第一个方法,它似乎已经完美工作,只需要大约30分钟执行。谢啦。 – Skippy 2011-03-17 07:48:27
只需要在另一个数据库上做同样的事情,第二种方法也可以完美地工作。再次感谢。 – Skippy 2011-08-05 07:16:09