如何从MySQL表中删除副本?(复制)

Possible Duplicate:
Remove duplicates using only a MySQL query?

可能的重复:只使用MySQL查询删除重复?

I have a table with different pages but some of them are duplicates. The only way to determine duplicates are by title. I run the code below and it works perfectly:

我有一张不同页面的表格，但有些是重复的。确定副本的唯一方法是通过标题。我运行下面的代码，它工作得很好:

<?php

mysql_connect("localhost", "root", "root") or die(mysql_error());
mysql_select_db("old") or die(mysql_error());
$result = mysql_query("SELECT pagetitle, COUNT( * ) c
FROM cms_site_content
GROUP BY pagetitle
HAVING c >1") or die(mysql_error());  
while($row = mysql_fetch_array($result)){
    echo $row['id'].'-'.$row['pagetitle'].'<br />';
}

?>

My question is: How can I erase duplicates and keep only one entry. For example if I have an article called "Duplicate Article" and the result is

我的问题是:如何删除重复的内容，只保留一个条目?例如，如果我有一篇名为“Duplicate article”的文章，结果是

Duplicate Article: 3

重复的文章:3

I want to keep only one.

我只想保留一个。

5 个解决方案

#1

You'll want to INNER JOIN the table on itself and delete where the pagetitle values are identical but the primary key (I'm using ID as the identifier) is not.

您将希望内部连接表，并删除页面标题值相同但主键(我使用ID作为标识符)不相同的地方。

Try:

试一试:

DELETE c2 FROM `cms_site_content` c1 INNER JOIN
               `cms_site_content` c2
          ON c1.pagetitle = c2.pagetitle AND c1.ID <> c2.ID

Reference: http://dev.mysql.com/doc/refman/5.0/en/join.html

参考:http://dev.mysql.com/doc/refman/5.0/en/join.html

#2

Select all single rows, insert them into a temp table, delete all record, insert them back from temp

选择所有单行，将它们插入到临时表中，删除所有记录，从临时表中插入它们

Use some application logic: select only one from each multiple row, delete all from the table, then insert back the single ones

使用一些应用程序逻辑:从每个多行中选择一个，从表中删除所有，然后插入单个行

#3

Try this: SELECT DISTINCT pagetitle, id, FROM cms_site_content

试试这个:从cms_site_content中选择不同的pagetitle、id

Save all ids to idlist use PHP from above SQL and then execute:

将所有id保存到idlist使用上面SQL中的PHP，然后执行:

DELETE FROM cms_site_content WHERE id NOT in ($idlist)

从没有id的cms_site_content中删除($idlist)

You need to translate above SQL statements to PHP/MySQL code.

您需要将上面的SQL语句转换为PHP/MySQL代码。

#4

This line woked:

这条线锅:

DELETE t2
FROM cms_site_content t1
JOIN cms_site_content t2 ON (t2.pagetitle = t1.pagetitle AND t2.id > t1.id);

#5

-2

try this

试试这个

DELETE t1 FROM table t1, table t2 WHERE t1.id > t2.id AND t1.name = t2.name

#1