删除表中重复的记录。

delete from job s 
where rowid < any 
(select rowid from job k 
where s.site_id = k.site_id and 
s.title = k.title and 
s.company = k.company);

or

delete from job s 
where rowid not in 
(select max(rowid) from job k 
where s.site_id = k.site_id and
s.title = k.title and 
s.company = k.company);

使用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 t1 FROM my_table t1, my_table t2 WHERE t1.id < t2.id AND t1.my_field = t2.my_field AND t1.my_field_2 = t2.my_field_2 AND ...

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;

从8.0版(2018)开始，MySQL终于支持窗口函数了。

窗口函数既方便又高效。下面是一个演示如何使用它们来解决这个任务的解决方案。

在子查询中，可以使用ROW_NUMBER()为表中columnn1 /column2组中的每条记录分配位置，按id排序。如果没有重复项，记录将获得第1行。如果存在副本，它们将按升序编号(从1开始)。

一旦子查询中的记录正确编号，外层查询只删除行号不是1的所有记录。

查询:

DELETE FROM tablename
WHERE id IN (
    SELECT id
    FROM (
        SELECT 
            id, 
            ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY id) rn
        FROM output
    ) t
    WHERE rn > 1
)

我想更具体地说明我删除了哪些记录，下面是我的解决方案:

delete
from jobs c1
where not c1.location = 'Paris'
and  c1.site_id > 64218
and exists 
(  
select * from jobs c2 
where c2.site_id = c1.site_id
and   c2.company = c1.company
and   c2.location = c1.location
and   c2.title = c1.title
and   c2.site_id > 63412
and   c2.site_id < 64219
)