如何在数据库中查找重复项?
关于如何在数据库中查找重复内容有很多问题,但没有解决具体问题。如何在数据库中查找重复项?
我有一个约表。 120000个条目。我需要找到重复的。为了找到他们,我用一个PHP脚本的结构如下所示:
//get all entries from database
//loop through them
//get entries with greater id
//compare all of them with the original one
//update database (delete duplicate, update information in linked tables, etc.)
它不可能在最初的查询已经整理出所有重复,因为我经历了,因为我重复的搜索中的所有条目必须循环不仅对100%相同的条目敏感,而且对90%相同的条目也很敏感。我使用了similar_text()。
我认为第一个循环没问题,但循环循环内的所有其他条目太多了。有120000个条目,这将接近(120000^2)/ 2次迭代。
因此,不要在循环中使用循环,而必须有更好的方法来做到这一点。你有什么想法?我想过使用in_array(),但它对90%字符串相似性这样的东西不敏感,也没有给我数组的字段找到重复项 - 我需要那些获得条目的id来更新数据库正确。
任何想法?
非常感谢!
查尔斯
更新1
我使用的是现在的查询如下:
SELECT a.host_id
FROM host_webs a
JOIN host_webs b ON a.host_id != b.host_id AND a.web = b.web
GROUP BY a.host_id
这表明原件及复印件完美,但我需要摆脱原件,即与相关数据一起找到的第一个。我怎么能做到这一点?
可以JOIN
表到自身,做这一切在SQL(我知道你SA你不认为你可以,但如果是这种情况,我会感到惊讶)。您需要做的就是将所有用于测试重复项的列放入JOIN
的ON
子句中。
SELECT id
FROM tablename a
JOIN tablename b ON a.id != b.id AND a.col1 = b.col1 AND a.col2 = b.col2
GROUP BY id
这将只返回id
S其中col1
和col2
是重复的行。您可以将所需的任何字符串比较合并到此中,ON
子句可以像您需要的那样复杂。例如:
SELECT id
FROM tablename a
JOIN tablename b ON a.id != b.id AND
(a.col1 = b.col1 AND (a.col2 = b.col2 OR a.col3 = b.col3))
OR ((a.col1 = b.col1 OR a.col2 = b.col2) AND a.col3 = b.col3)
OR (SOUNDEX(a.col1) = SOUNDEX(b.col1) AND SOUNDEX(a.col2) = SOUNDEX(b.col2) AND SOUNDEX(a.col3) = SOUNDEX(b.col3))
GROUP BY id
编辑
因为所有你实际上是与您的查询是寻找行,其中的web
列是相同这样做,这将做只发现重复的,而不是的工作原来的“良”的记载 - 假设host_id
是数字和“好”的记录将是一个具有最低host_id
:
SELECT b.host_id
FROM host_webs a
INNER JOIN host_webs b ON b.web = a.web AND b.host_id > a.host_id
GROUP BY b.host_id
我意马NE比赛结束这里将删除重复的,所以如果你感觉勇敢的你实际上可以一次性删除:
DELETE b.*
FROM host_webs a
INNER JOIN host_webs b ON b.web = a.web AND b.host_id > a.host_id
的GROUP BY
不在DELETE
语句,因为它并不重要,如果有必要您尝试在单个语句中多次删除同一行。
如果你正在做一次删除重复的项目,我不会打扰编写一个PHP脚本 - 它在sql中更清洁。
去除重复,我觉得作品最好的是一般的算法:
1.复制表
2.截断原始表
3.设置为准列必须是唯一的唯一索引
4.采用任何INSERT IGNORE INTO original_table SELECT * FROM duplicate_table
或REPLACE INTO original_table SELECT * FROM duplicate table
5.固定链接表重新插入行 - 删除孤立行(DELETE x FROM x LEFT JOIN original TABLE ON (...) WHERE original_table.id IS NULL
)
谢谢!查询容忍90%的相似性吗? – weltschmerz 2012-07-12 22:48:04
@Charles它取决于你如何定义90% - 这就是第二个例子。这返回至少两个'col1','col2','col3'是相同或相同的列都具有相同的soundex得分的行。您使用的确切查询用例取决于列类型和用于确定相似性的标准。 – DaveRandom 2012-07-12 22:51:30
我正在尝试使用你的第一个解决方案,但它带来了一个问题:它不仅给我找到的重复项,而且还给出了原始的重复项。我该怎么做才能使它不显示原件?注意:可以有多个重复。查看我现在正在使用的查询的问题编辑。谢谢! – weltschmerz 2012-07-21 16:01:08