mysql - Verwijder dubbele rijen in MySQL

Ik heb een tabel met de volgende velden:

id (Unique)
url (Unique)
title
company
site_id

Nu moet ik rijen met dezelfde title, company and site_idverwijderen. Een manier om dit te doen is door de volgende SQL samen met een script te gebruiken (PHP):

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

Na het uitvoeren van deze query kan ik dubbele bestanden verwijderen met behulp van een server-side script.

Maar ik wil weten of dit alleen kan worden gedaan met behulp van een SQL-query.

Antwoord 1, autoriteit 100%

Een heel gemakkelijke manier om dit te doen is door een UNIQUEindex toe te voegen aan de 3 kolommen. Wanneer u de instructie ALTERschrijft, neemt u het sleutelwoord IGNOREop. Vind ik leuk:

ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (site_id, title, company);

Hiermee worden alle dubbele rijen verwijderd. Als bijkomend voordeel zullen toekomstige INSERTsdie duplicaten zijn, fouten bevatten. Zoals altijd wil je misschien een back-up maken voordat je zoiets als dit uitvoert…

Antwoord 2, autoriteit 31%

Als u de kolomeigenschappen niet wilt wijzigen, kunt u de onderstaande query gebruiken.

Aangezien u een kolom heeft met unieke ID’s (bijv. kolommen auto_increment), kunt u deze gebruiken om de duplicaten te verwijderen:

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);

In MySQL kunt u het nog meer vereenvoudigen met de Null-safe Equal Operator (aka ” Ruimteschip-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`;

Antwoord 3, Autoriteit 13%

MySQL heeft beperkingen over het verwijzen naar de tabel waaruit u verwijdert. Je kunt dat rond werken met een tijdelijke tafel, zoals:

create temporary table tmpTable (id int);
insert  into 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);

van de suggestie van Kostanos in de opmerkingen:
De enige langzame vraag hierboven is DELETE, voor gevallen waarin u een zeer grote database heeft. Deze zoekopdracht kan sneller zijn:

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

Antwoord 4, autoriteit 8%

Als de IGNORE-instructie niet werkt zoals in mijn geval, kunt u de onderstaande instructie gebruiken:

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;

Antwoord 5, autoriteit 7%

Het verwijderen van duplicaten op MySQL-tabellen is een veelvoorkomend probleem, dat over het algemeen het gevolg is van een ontbrekende beperking om die duplicaten vooraf te voorkomen. Maar dit veelvoorkomende probleem gaat meestal gepaard met specifieke behoeften… die wel een specifieke aanpak vereisen. De aanpak moet anders zijn, bijvoorbeeld afhankelijk van de grootte van de gegevens, de dubbele invoer die moet worden bewaard (meestal de eerste of de laatste), of er indexen moeten worden bewaard, of dat we aanvullende actie op de gedupliceerde gegevens.

Er zijn ook enkele specifieke kenmerken van MySQL zelf, zoals het niet kunnen verwijzen naar dezelfde tabel met een FROM-oorzaak bij het uitvoeren van een tabel-UPDATE (het zal MySQL-fout #1093 veroorzaken). Deze beperking kan worden overwonnen door een innerlijke query met een tijdelijke tabel te gebruiken (zoals gesuggereerd bij sommige benaderingen hierboven). Maar deze innerlijke vraag zal niet bijzonder goed presteren bij het omgaan met grote gegevensbronnen.

Er bestaat echter een betere aanpak om duplicaten te verwijderen, die zowel efficiënt als betrouwbaar is en die gemakkelijk kan worden aangepast aan verschillende behoeften.

Het algemene idee is om een nieuwe tijdelijke tabel te maken, meestal door een unieke beperking toe te voegen om verdere duplicaten te voorkomen, en om de gegevens van uw vorige tabel in de nieuwe te INSERT, terwijl u voor de duplicaten zorgt. Deze aanpak is gebaseerd op eenvoudige MySQL INSERT-query’s, creëert een nieuwe beperking om verdere duplicaten te voorkomen en slaat de noodzaak over van het gebruik van een interne query om naar duplicaten te zoeken en een tijdelijke tabel die in het geheugen moet worden bewaard (en dus ook geschikt is voor big data-bronnen).

Dit is hoe het kan worden bereikt. Aangezien we een tabel werknemerhebben, met de volgende kolommen:

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

Om de rijen met een dubbele ssn-kolom te verwijderen en alleen de eerste gevonden invoer te behouden, kan het volgende proces worden gevolgd:

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

Technische uitleg

Regel #1 maakt een nieuwe tabel tmp_eployeemet exact dezelfde structuur als de tabel employee
Regel #2 voegt een UNIEKE beperking toe aan de nieuwe tabel tmp_eployeeom verdere duplicaten te voorkomen
Regel #3 scant de originele employee-tabel op id, waarbij nieuwe werknemersvermeldingen worden ingevoegd in de nieuwe tmp_eployee-tabel, terwijl dubbele vermeldingen worden genegeerd
Regel #4 hernoemt tabellen, zodat de nieuwe tabel employeealle vermeldingen bevat zonder de duplicaten, en een reservekopie van de voormalige gegevens wordt bewaard op de backup_employeetafel

⇒ Met deze aanpak werden 1,6 miljoen registers in minder dan 200 seconden omgezet in 6k.

Chetan, door dit proces te volgen, kunt u snel en gemakkelijk al uw duplicaten verwijderen en een UNIEKE beperking maken door het volgende uit te voeren:

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;

Natuurlijk kan dit proces verder worden aangepast om het aan te passen aan verschillende behoeften bij het verwijderen van duplicaten. Enkele voorbeelden volgen.

✔ Variatie voor het behouden van de laatste invoer in plaats van de eerste

Soms moeten we de laatste dubbele invoer behouden in plaats van de eerste.

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;

Op regel #3 zorgt de ORDER BY id DESC-clausule ervoor dat de laatste ID’s voorrang krijgen op de rest

✔ Variatie voor het uitvoeren van enkele taken op de duplicaten, bijvoorbeeld het tellen van de gevonden duplicaten

Soms moeten we verdere verwerking uitvoeren op de dubbele vermeldingen die worden gevonden (zoals het tellen van de dubbele vermeldingen).

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;

Op regel #3 is een nieuwe kolom n_duplicatesgemaakt
Op regel #4 wordt de INSERT INTO … ON DUPLICATE KEY UPDATE-query gebruikt om een extra update uit te voeren wanneer een duplicaat wordt gevonden (in dit geval een teller verhogen)
De INSERT INTO … ON DUPLICATE KEY UPDATE-query kan worden gebruikt om verschillende soorten updates uit te voeren voor de gevonden duplicaten.

✔ Variatie voor het opnieuw genereren van de auto-incrementele veld-ID

Soms gebruiken we een auto-incrementeel veld en om de index zo compact mogelijk te houden, kunnen we profiteren van het verwijderen van de duplicaten om het auto-incrementele veld opnieuw te genereren in de nieuwe tijdelijke tabel.

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;

Op regel #3, in plaats van alle velden in de tabel te selecteren, wordt het id-veld overgeslagen zodat de DB-engine automatisch een nieuwe genereert

✔ Verdere variaties

Veel verdere aanpassingen zijn ook wel te doen, afhankelijk van het gewenste gedrag. Als voorbeeld zullen de volgende vragen een tweede tijdelijke tabel gebruiken voor, naast 1) houden de laatste invoer in plaats van de eerste; en 2) een teller op de gevonden duplicaten verhogen; Ook 3) Regenereer de Auto-incrementele veld-ID tijdens het bewaren van de invoeropdracht zoals op de voormalige gegevens.

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;

Antwoord 6, Autoriteit 5%

Er is een andere oplossing:

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

Antwoord 7

Als u een grote tabel met een enorm aantal records hebt, zullen boven oplossingen niet werken of te veel tijd nemen. Dan hebben we een andere oplossing

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

Antwoord 8

Hiermee wordt de dubbele rijen met dezelfde waarden voor titel, bedrijf en site verwijderd. De eerste gebeurtenis zal worden bewaard en rusten alle duplicaten worden

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;

Antwoord 9

Ik heb deze query snipet voor SQLSERVER, maar ik denk dat het kan worden gebruikt in anderen DBMS met weinig wijzigingen:

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

Ik vergat u te vertellen dat deze query de rij niet verwijdert met de laagste ID van de gedupliceerde rijen. Als dit voor u werkt, probeer dan deze query:

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

Antwoord 10

De snellere manier is om verschillende rijen in een tijdelijke tafel in te voegen. Het gebruik van Delete, het kostte me een paar uur om duplicaten uit een tabel met 8 miljoen rijen te verwijderen. Met behulp van insert en verschillend duurde het slechts 13 minuten.

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;

Antwoord 11

Verwijder dubbele rijen met de instructie DELETE JOIN
MySQL biedt u de DELETE JOIN-instructie die u kunt gebruiken om dubbele rijen snel te verwijderen.

De volgende instructie verwijdert dubbele rijen en behoudt de hoogste id:

DELETE t1 FROM contacts t1
    INNER JOIN
contacts t2 WHERE
t1.id < t2.id AND t1.email = t2.email;

Antwoord 12

Een oplossing die eenvoudig te begrijpen is en werkt zonder primaire sleutel:

voeg een nieuwe booleaanse kolom toe

alter table mytable add tokeep boolean;

voeg een beperking toe aan de gedupliceerde kolommen EN de nieuwe kolom

alter table mytable add constraint preventdupe unique (mycol1, mycol2, tokeep);

stel de booleaanse kolom in op true. Dit lukt alleen op een van de gedupliceerde rijen vanwege de nieuwe beperking
```
update ignore mytable set tokeep = true;
```
verwijder rijen die niet zijn gemarkeerd als tokeep
```
delete from mytable where tokeep is null;
```
zet de toegevoegde kolom neer
```
alter table mytable drop tokeep;
```

Ik stel voor dat u de door u toegevoegde beperking behoudt, zodat nieuwe duplicaten in de toekomst worden voorkomen.

Antwoord 13

Eenvoudig en snel voor alle gevallen:

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);

Antwoord 14

Ik heb een eenvoudige manier gevonden. (laatste houden)

DELETE t1 FROM tablename t1 INNER JOIN tablename t2 
WHERE t1.id < t2.id AND t1.column1 = t2.column1 AND t1.column2 = t2.column2;

Antwoord 15

Ik blijf deze pagina altijd bezoeken als ik google “verwijder duplicaten van mysql” maar voor mijn theIGNORE-oplossingen werken niet omdat ik een InnoDB mysql-tabellen heb

deze code werkt altijd beter

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 = de naam van de tabel die u moet opschonen

tableToclean_temp = een tijdelijke tabel aangemaakt en verwijderd

Antwoord 16

Vanaf versie 8.0 (2018) ondersteunt MySQL eindelijk vensterfuncties.

Vensterfuncties zijn zowel handig als efficiënt. Hier is een oplossing die laat zien hoe je ze kunt gebruiken om deze opdracht op te lossen.

In een subquery kunnen we ROW_NUMBER()om een positie toe te wijzen aan elke record in de tabel in de lijst column1/column2GROEPEN, BESTELD DOOR id. Als er geen duplicaten zijn, krijgt het record rijnummer 1. Als duplicaat bestaat, worden ze genummerd door op te staan id(beginnend bij 1).

Zodra records correct zijn genummerd in de subquery, verwijdert de buitenste query alle records waarvan het rijnummer niet is 1.

QUERY:

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
)

Antwoord 17

Deze oplossing zal de duplicaten in één tafel verplaats en de uniques in een andere .

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

Antwoord 18

Om de dubbele record in een tabel te verwijderen.

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);

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);

Antwoord 19

Dit is wat ik heb gebruikt en het werkt:

create table temp_table like my_table;

t_id is mijn unieke kolom

insert into temp_table (id) select id from my_table GROUP by t_id;
delete from my_table where id not in (select id from temp_table);
drop table temp_table;

Antwoord 20

Om records met unieke kolommen te dupliceren, b.v. COL1,COL2, COL3 mag niet worden gerepliceerd (stel dat we 3 kolommen hebben gemist die uniek zijn in de tabelstructuur en er zijn meerdere dubbele vermeldingen in de tabel gemaakt)

DROP TABLE TABLE_NAME_copy;
CREATE TABLE TABLE_NAME_copy LIKE TABLE_NAME;
INSERT INTO TABLE_NAME_copy
SELECT * FROM TABLE_NAME
GROUP BY COLUMN1, COLUMN2, COLUMN3; 
DROP TABLE TABLE_NAME;
ALTER TABLE TABLE_NAME_copy RENAME TO TABLE_NAME;

Hoop zal ontwikkelaar helpen.

Antwoord 21

Verwijder dubbele rijen met de instructie 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;

Antwoord 22

Ik heb een tabel die vergeten is een primaire sleutel toe te voegen aan de id-rij. Hoewel het auto_increment op de id heeft. Maar op een dag speelt één ding het mysql-bin-logboek in de database opnieuw af, waarin enkele dubbele rijen worden ingevoegd.

Ik verwijder de dubbele rij door

selecteer de unieke dubbele rijen en exporteer ze

select T1.* from table_name T1 inner join (select count(*) as c,id from table_name group by id) T2 on T1.id = T2.id where T2.c > 1 group by T1.id;
verwijder de dubbele rijen op id
voeg de rij van de geëxporteerde gegevens in.
Voeg vervolgens de primaire sleutel toe aan id

Antwoord 23

Ik wil graag wat specifieker zijn over welke records ik verwijder, dus hier is mijn oplossing:

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
)

Antwoord 24

U kunt eenvoudig de dubbele records uit deze code verwijderen..

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

Antwoord 25

Ik moest dit doen met tekstvelden en stuitte op de limiet van 100 bytes op de index.

Ik heb dit opgelost door een kolom toe te voegen, een md5-hash van de velden te maken en de alter te doen.

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`);

Verwijder dubbele rijen in MySQL