使用Delete JOIN语句删除重复的行:

DELETE t1 FROM table_name t1
JOIN table_name t2
WHERE
    t1.id < t2.id AND
    t1.title = t2.title AND t1.company = t2.company AND t1.site_id = t2.site_id;

更快的方法是在临时表中插入不同的行。使用delete，我花了几个小时从一个800万行的表中删除重复项。使用insert和distinct，只花了13分钟。

CREATE TABLE tempTableName LIKE tableName;  
CREATE INDEX ix_all_id ON tableName(cellId,attributeId,entityRowId,value);  
INSERT INTO tempTableName(cellId,attributeId,entityRowId,value) SELECT DISTINCT cellId,attributeId,entityRowId,value FROM tableName;  
TRUNCATE TABLE tableName;
INSERT INTO tableName SELECT * FROM tempTableName; 
DROP TABLE tempTableName;  

使用Delete JOIN语句删除重复的行:

DELETE t1 FROM table_name t1
JOIN table_name t2
WHERE
    t1.id < t2.id AND
    t1.title = t2.title AND t1.company = t2.company AND t1.site_id = t2.site_id;

你可以很容易地从这个代码中删除重复的记录。

$qry = mysql_query("SELECT * from cities");
while($qry_row = mysql_fetch_array($qry))
{
$qry2 = mysql_query("SELECT * from cities2 where city = '".$qry_row['city']."'");

if(mysql_num_rows($qry2) > 1){
    while($row = mysql_fetch_array($qry2)){
        $city_arry[] = $row;

        }

    $total = sizeof($city_arry) - 1;
        for($i=1; $i<=$total; $i++){


            mysql_query( "delete from cities2 where town_id = '".$city_arry[$i][0]."'");

            }
    }
    //exit;
}

Deleting duplicates on MySQL tables is a common issue, that's genarally the result of a missing constraint to avoid those duplicates before hand. But this common issue usually comes with specific needs... that do require specific approaches. The approach should be different depending on, for example, the size of the data, the duplicated entry that should be kept (generally the first or the last one), whether there are indexes to be kept, or whether we want to perform any additional action on the duplicated data.

MySQL本身也有一些特殊性，比如在执行表UPDATE时不能在FROM上引用同一个表(它会引发MySQL错误#1093)。这种限制可以通过使用带有临时表的内部查询来克服(如上面的一些方法所建议的)。但是这种内部查询在处理大数据源时表现不佳。

然而，确实存在一种更好的方法来删除副本，这种方法既有效又可靠，并且可以很容易地适应不同的需求。

一般的想法是创建一个新的临时表，通常添加一个唯一的约束以避免进一步的重复，并将前一个表中的数据插入到新表中，同时处理重复的数据。这种方法依赖于简单的MySQL INSERT查询，创建一个新的约束以避免进一步的重复，并且跳过了使用内部查询来搜索重复和应该保存在内存中的临时表的需要(因此也适合大数据源)。

这就是实现它的方法。假设我们有一个表employee，有以下列:

employee (id, first_name, last_name, start_date, ssn)

为了删除具有重复ssn列的行，并只保留找到的第一个条目，可以遵循以下过程:

-- create a new tmp_eployee table
CREATE TABLE tmp_employee LIKE employee;

-- add a unique constraint
ALTER TABLE tmp_employee ADD UNIQUE(ssn);

-- scan over the employee table to insert employee entries
INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id;

-- rename tables
RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

技术的解释

Line #1 creates a new tmp_eployee table with exactly the same structure as the employee table Line #2 adds a UNIQUE constraint to the new tmp_eployee table to avoid any further duplicates Line #3 scans over the original employee table by id, inserting new employee entries into the new tmp_eployee table, while ignoring duplicated entries Line #4 renames tables, so that the new employee table holds all the entries without the duplicates, and a backup copy of the former data is kept on the backup_employee table

使用这种方法，160万个寄存器在不到200秒的时间内转换为6k。

Chetan，按照这个过程，你可以快速轻松地删除所有副本，并通过运行创建一个UNIQUE约束:

CREATE TABLE tmp_jobs LIKE jobs;

ALTER TABLE tmp_jobs ADD UNIQUE(site_id, title, company);

INSERT IGNORE INTO tmp_jobs SELECT * FROM jobs ORDER BY id;

RENAME TABLE jobs TO backup_jobs, tmp_jobs TO jobs;

当然，在删除重复项时，可以进一步修改此过程以适应不同的需要。以下是一些例子。

✔保留最后一个条目而不是第一个条目的变化

有时我们需要保留最后一个重复的条目，而不是第一个。

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id DESC;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

在第3行，ORDER BY id DESC子句使最后一个id优先于其他id

在副本上执行一些任务的变化，例如统计发现的副本

有时，我们需要对找到的重复条目执行一些进一步的处理(例如保持重复条目的计数)。

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

ALTER TABLE tmp_employee ADD COLUMN n_duplicates INT DEFAULT 0;

INSERT INTO tmp_employee SELECT * FROM employee ORDER BY id ON DUPLICATE KEY UPDATE n_duplicates=n_duplicates+1;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

在第3行中，创建了一个新列n_duplicate 在第4行，INSERT INTO…ON DUPLICATE KEY UPDATE查询用于在发现副本时执行额外的更新(在本例中，增加计数器) 插入…ON DUPLICATE KEY UPDATE查询可用于对找到的副本执行不同类型的更新。

重新生成自动递增字段id的变量

有时我们使用自动增量字段，为了使索引尽可能紧凑，我们可以利用删除重复项来在新的临时表中重新生成自动增量字段。

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

INSERT IGNORE INTO tmp_employee SELECT (first_name, last_name, start_date, ssn) FROM employee ORDER BY id;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

在第3行中，没有选择表中的所有字段，而是跳过了id字段，以便DB引擎自动生成一个新字段

进一步的变化

根据所需的行为，还可以进行许多进一步的修改。例如，下面的查询将使用第二个临时表，除了1)保留最后一个条目而不是第一个条目;2)在发现的副本上增加计数器;另外3)重新生成自动增量字段id，同时保持输入顺序，因为它是在以前的数据。

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

ALTER TABLE tmp_employee ADD COLUMN n_duplicates INT DEFAULT 0;

INSERT INTO tmp_employee SELECT * FROM employee ORDER BY id DESC ON DUPLICATE KEY UPDATE n_duplicates=n_duplicates+1;

CREATE TABLE tmp_employee2 LIKE tmp_employee;

INSERT INTO tmp_employee2 SELECT (first_name, last_name, start_date, ssn) FROM tmp_employee ORDER BY id;

DROP TABLE tmp_employee;

RENAME TABLE employee TO backup_employee, tmp_employee2 TO employee;

这将删除标题、公司和网站值相同的重复行。最后一个重复项将被保留，其余重复项将被删除(如果你想保留第一个重复项并删除其他重复项，将id的比较项更改为大于t1。Id > t2.id)

DELETE t1 FROM tablename t1
INNER JOIN tablename t2 
WHERE 
    t1.id < t2.id AND
    t1.title = t2.title AND
    t1.company=t2.company AND
    t1.site_ID=t2.site_ID;