删除MySQL中的重复行-Java 学习之路

311

我有一个包含以下字段的表：

id (Unique)
url (Unique)
title
company
site_id

现在，我需要删除具有相同 title, company and site_id 的行 . 一种方法是使用以下SQL和脚本（ PHP ）：

SELECT title, site_id, location, id, count( * ) 
FROM jobs
GROUP BY site_id, company, title, location
HAVING count( * ) >1

运行此查询后，我可以使用服务器端脚本删除重复项 .

但是，我想知道是否只能使用SQL查询来完成 .

17 回答

563

还有另一种解决方案：

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 ...

回复于 2024-04-23T18:21:19+08:00

2
我随时随地访问此页面谷歌“删除重复的形式mysql”但我的theIGNORE解决方案不起作用，因为我有一个InnoDB mysql表

此代码随时可以更好地工作
```
CREATE TABLE tableToclean_temp LIKE tableToclean;
ALTER TABLE tableToclean_temp ADD UNIQUE INDEX (fontsinuse_id);
INSERT IGNORE INTO tableToclean_temp SELECT * FROM tableToclean;
DROP TABLE tableToclean;
RENAME TABLE tableToclean_temp TO tableToclean;
```
tableToclean =您需要清理的表的名称

tableToclean_temp =创建和删除的临时表
回复于 2024-04-23T18:21:19+08:00
4
删除MySQL表上的重复项是一个常见的问题，这通常是缺少约束的结果，以避免事先重复这些重复 . 但这个常见问题通常伴随着特定的需求......确实需要特定的方法 . 该方法应该根据，例如，数据的大小，应保留的重复条目（通常是第一个或最后一个），是否存在索引，或者我们是否要执行任何其他对重复数据采取行动 .

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;
```
技术说明
- 第1行创建一个新的 tmp_eployee 表，其结构与 employee 表完全相同
- 第2行为新的 tmp_eployee 表添加了一个UNIQUE约束，以避免任何进一步的重复
- 第3行按ID扫描原始 employee 表，将新员工条目插入新的 tmp_eployee 表，同时忽略重复的条目
- 第4行重命名表，以便新的 employee 表保存所有没有重复项的条目，并且前一数据的备份副本保存在 backup_employee 表中
⇒使用这种方法，1.6M寄存器在不到200s的时间内转换为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_duplicates
- 在第4行， INSERT INTO ... ON DUPLICATE KEY UPDATE 查询用于在找到重复项时执行其他更新（在这种情况下，增加计数器） INSERT INTO ... 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字段，以便数据库引擎自动生成一个新字段
✔进一步的变化

根据所需的行为，许多进一步的修改也是可行的 . 例如，以下查询将使用第二个临时表，除了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;
```
回复于 2024-04-23T18:21:19+08:00

-3

如果您不想更改列属性，则可以使用下面的查询 .

由于您的列具有唯一ID（例如， auto_increment 列），因此您可以使用它来删除重复项：

DELETE `a`
FROM
    `jobs` AS `a`,
    `jobs` AS `b`
WHERE
    -- IMPORTANT: Ensures one version remains
    -- Change "ID" to your unique column's name
    `a`.`ID` < `b`.`ID`

    -- Any duplicates you want to check for
    AND (`a`.`title` = `b`.`title` OR `a`.`title` IS NULL AND `b`.`title` IS NULL)
    AND (`a`.`company` = `b`.`company` OR `a`.`company` IS NULL AND `b`.`company` IS NULL)
    AND (`a`.`site_id` = `b`.`site_id` OR `a`.`site_id` IS NULL AND `b`.`site_id` IS NULL);

在MySQL中，您可以使用NULL-safe equal operator（aka "spaceship operator"）进一步简化它：

DELETE `a`
FROM
    `jobs` AS `a`,
    `jobs` AS `b`
WHERE
    -- IMPORTANT: Ensures one version remains
    -- Change "ID" to your unique column's name
    `a`.`ID` < `b`.`ID`

    -- Any duplicates you want to check for
    AND `a`.`title` <=> `b`.`title`
    AND `a`.`company` <=> `b`.`company`
    AND `a`.`site_id` <=> `b`.`site_id`;

回复于 2024-04-23T18:21:19+08:00

MySQL对引用要删除的表有限制 . 您可以使用临时表解决此问题，例如：

create temporary table tmpTable (id int);

insert  tmpTable
        (id)
select  id
from    YourTable yt
where   exists
        (
        select  *
        from    YourTabe yt2
        where   yt2.title = yt.title
                and yt2.company = yt.company
                and yt2.site_id = yt.site_id
                and yt2.id > yt.id
        );

delete  
from    YourTable
where   ID in (select id from tmpTable);

From Kostanos' suggestion in the comments:
对于您拥有非常大的数据库的情况，上面唯一的慢查询是DELETE . 此查询可能更快：

DELETE FROM YourTable USING YourTable, tmpTable WHERE YourTable.id=tmpTable.id

回复于 2024-04-23T18:21:19+08:00

如果 IGNORE 语句不能像我的情况那样工作，您可以使用以下语句：

CREATE TABLE your_table_deduped like your_table;
INSERT your_table_deduped SELECT * FROM your_table GROUP BY index1_id, index2_id;
RENAME TABLE your_table TO your_table_with_dupes;
RENAME TABLE your_table_deduped TO your_table;
#OPTIONAL
ALTER TABLE `your_table` ADD UNIQUE `unique_index` (`index1_id`, `index2_id`);
#OPTIONAL
DROP TABLE your_table_with_dupes;

回复于 2024-04-23T18:21:19+08:00

如果你有一个包含大量记录的大表，那么以上解决方案将无法工作或占用太多时间 . 然后我们有不同的解决方案

-- Create temporary table

CREATE TABLE temp_table LIKE table1;

-- Add constraint
ALTER TABLE temp_table ADD UNIQUE(title, company,site_id);

-- Copy data
INSERT IGNORE INTO temp_table SELECT * FROM table1;

-- Rename and drop
RENAME TABLE table1 TO old_table1, temp_table TO table1;
DROP TABLE old_table1;

回复于 2024-04-23T18:21:19+08:00

-2

所有情况都简单快捷：

CREATE TEMPORARY TABLE IF NOT EXISTS _temp_duplicates AS (SELECT dub.id FROM table_with_duplications dub GROUP BY dub.field_must_be_uniq_1, dub.field_must_be_uniq_2 HAVING COUNT(*)  > 1);

DELETE FROM table_with_duplications WHERE id IN (SELECT id FROM _temp_duplicates);

回复于 2024-04-23T18:21:19+08:00

我有这个查询snipet for SQLServer但我认为它可以在其他DBMS中使用，几乎没有变化：

DELETE
FROM Table
WHERE Table.idTable IN  (  
    SELECT MAX(idTable)
    FROM idTable
    GROUP BY field1, field2, field3
    HAVING COUNT(*) > 1)

我忘了告诉你这个查询不会删除重复行id最低的行 . 如果这适用于您尝试此查询：

DELETE
FROM jobs
WHERE jobs.id IN  (  
    SELECT MAX(id)
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING COUNT(*) > 1)

回复于 2024-04-23T18:21:19+08:00

-2

您可以轻松地从此代码中删除重复记录 .

$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;
}

回复于 2024-04-23T18:21:19+08:00

6
一个非常简单的方法是在3列上添加 UNIQUE 索引 . 编写 ALTER 语句时，请包含 IGNORE 关键字 . 像这样：
```
ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (site_id, title, company);
```
这将删除所有重复的行 . 作为一个额外的好处，未来 INSERTs 是重复的将错误 . 与往常一样，您可能希望在运行此类内容之前进行备份...
回复于 2024-04-23T18:21:19+08:00
72
一个易于理解且无需主键的解决方案：

1）添加一个新的布尔列
```
alter table mytable add tokeep boolean;
```
2）在重复列和新列上添加约束
```
alter table mytable add constraint preventdupe unique (mycol1, mycol2, tokeep);
```
3）将布尔列设置为true . 由于新约束，这将仅在其中一个重复行上成功
```
update ignore mytable set tokeep = true;
```
4）删除尚未标记为维护的行
```
delete from mytable where tokeep is null;
```
5）删除添加的列
```
alter table mytable drop tokeep;
```
我建议您保留您添加的约束，以便将来阻止新的重复项 .
回复于 2024-04-23T18:21:19+08:00
16
我必须使用文本字段执行此操作，并且在索引上遇到了100个字节的限制 .

我通过添加一个列，执行字段的md5哈希以及执行alter来解决了这个问题 .
```
ALTER TABLE table ADD `merged` VARCHAR( 40 ) NOT NULL ;
UPDATE TABLE SET merged` = MD5(CONCAT(`col1`, `col2`, `col3`))
ALTER IGNORE TABLE table ADD UNIQUE INDEX idx_name (`merged`);
```
回复于 2024-04-23T18:21:19+08:00

139

此解决方案将 move the duplicates into one table 和 uniques into another .

-- speed up creating uniques table if dealing with many rows
CREATE INDEX temp_idx ON jobs(site_id, company, title, location);

-- create the table with unique rows
INSERT jobs_uniques SELECT * FROM
    (
    SELECT * 
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING count(1) > 1
    UNION
    SELECT *
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING count(1) = 1
) x

-- create the table with duplicate rows
INSERT jobs_dupes 
SELECT * 
FROM jobs
WHERE id NOT IN
(SELECT id FROM jobs_uniques)

-- confirm the difference between uniques and dupes tables
SELECT COUNT(1)
AS jobs, 
(SELECT COUNT(1) FROM jobs_dupes) + (SELECT COUNT(1) FROM jobs_uniques)
AS sum
FROM jobs

回复于 2024-04-23T18:21:19+08:00

我想更具体一点，我删除了哪些记录，所以这里是我的解决方案：

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
)

回复于 2024-04-23T18:21:19+08:00

更快的方法是将不同的行插入临时表 . 使用删除，我花了几个小时从一个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;

回复于 2024-04-23T18:21:19+08:00

36
使用DELETE JOIN语句删除重复行MySQL为您提供了DELETE JOIN语句，您可以使用该语句快速删除重复的行 .

以下语句删除重复行并保留最高ID：
```
DELETE t1 FROM contacts t1
    INNER JOIN
contacts t2 WHERE
t1.id < t2.id AND t1.email = t2.email;
```
回复于 2024-04-23T18:21:19+08:00

删除MySQL中的重复行

17 回答

技术说明

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

✔对重复项执行某些任务的变化，例如对发现的重复项进行计数

✔重新生成自动增量字段ID的变化

✔进一步的变化

相关问题