Usuń zduplikowane wiersze w MySQL

Jeśli nie chcesz zmieniać właściwości kolumn, możesz użyć poniższego zapytania.

Ponieważ masz kolumnę, która ma unikalne identyfikatory (np. auto_increment columns), możesz użyć jej do usunięcia duplikatów:

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

W MySQL można go jeszcze bardziej uprościć za pomocą null-safe equal operator (aka ""):

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

MySQL ma ograniczenia dotyczące odwoływania się do tabeli, z której usuwasz. Możesz obejść to za pomocą tymczasowej tabeli, na przykład:

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

Z sugestii Kostanosa w komentarzach:
Jedynym wolnym zapytaniem powyżej jest DELETE, w przypadkach, gdy masz bardzo dużą bazę danych. To zapytanie może być szybsze:

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

Jeśli IGNORE instrukcja nie będzie działać jak w moim przypadku, możesz użyć poniższej instrukcji:

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;

Jest inne rozwiązanie:

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

Usuwanie duplikatów w tabelach MySQL jest częstym problemem, który jest genaraly wynikiem brakującego ograniczenia, aby uniknąć tych duplikatów przed ręką. Ale ten powszechny problem zwykle wiąże się z konkretnymi potrzebami... które wymagają szczególnego podejścia. Podejście powinno być różne w zależności na przykład od wielkości danych, zduplikowanego wpisu ,który powinien być przechowywany( na ogół pierwszy lub ostatni), czy istnieją indeksy do przechowywania, czy też chcemy wykonać dodatkowe działanie na zduplikowanych danych.

Istnieją również pewne specyfiki dotyczące samego MySQL, takie jak brak możliwości odwołania się do tej samej tabeli na podstawie przyczyny FROM podczas wykonywania aktualizacji tabeli (spowoduje to błąd MySQL #1093). To ograniczenie można przezwyciężyć za pomocą wewnętrznego zapytania z tymczasową tabelą(jak sugerowano w niektórych podejściach powyżej). Ale to wewnętrzne zapytanie nie będzie działać szczególnie dobrze, gdy mamy do czynienia z dużymi źródłami danych.

Jednak istnieje lepsze podejście do usuwania duplikaty, które są zarówno wydajne, jak i niezawodne, i które można łatwo dostosować do różnych potrzeb.

Ogólna idea polega na stworzeniu nowej tabeli tymczasowej, zwykle dodając unikalne ograniczenie, aby uniknąć dalszych duplikatów, oraz wstawianiu danych z poprzedniej tabeli do nowej, dbając o duplikaty. To podejście opiera się na prostych zapytaniach Insert MySQL, tworzy nowe ograniczenie, aby uniknąć dalszych duplikatów i pomija potrzebę użycia wewnętrznego zapytania do wyszukiwania duplikaty i tymczasową tabelę, która powinna być przechowywana w pamięci (w ten sposób pasująca również do dużych źródeł danych).

W ten sposób można to osiągnąć. Mając do dyspozycji tabelę z następującymi kolumnami:

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

Aby usunąć wiersze z duplikatem kolumny ssn i zachować tylko pierwszą znalezioną pozycję, można wykonać następujący proces:

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

Wyjaśnienie techniczne

• linia # 1 tworzy nową tabelę tmp_eployee z dokładnie taka sama struktura jak w tabeli
• linia # 2 dodaje unikalne ograniczenie do nowej tabeli tmp_eployee , aby uniknąć dalszych duplikatów
• linia # 3 skanuje oryginalną tabelę employee według id, wstawiając nowe wpisy pracownika do nowej tabeli tmp_eployee , ignorując zduplikowane wpisy
• linia # 4 zmienia nazwy tabel, tak aby nowa tabela zawierała wszystkie wpisy bez duplikatów, a kopia zapasowa poprzednich danych przechowywana jest w tabeli backup_employee

⇒ dzięki temu rejestry 1,6 M zostały zamienione na 6K w czasie krótszym niż 200s.

Chetan , Po tym procesie, można szybko i łatwo usunąć wszystkie duplikaty i utworzyć unikalne ograniczenie, uruchamiając:

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;

Oczywiście proces ten można dalej modyfikować, aby dostosować go do różnych potrzeb podczas usuwania duplikatów. Kilka przykładów za mną.

✔ wariacja na temat zachowania ostatniego wpisu zamiast pierwszego

Czasami musimy zachować ostatni zduplikowany wpis zamiast pierwszego.

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;

W 2007 roku, po raz pierwszy w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce, w Polsce]}

✔ zmiana do wykonywania niektórych zadań na duplikatach, na przykład utrzymywanie liczby znalezionych duplikatów

Czasami musimy wykonać jakieś dalsze przetwarzanie na znalezionych zduplikowanych wpisów(np. zliczanie duplikatów).

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;

• w linii # 3 powstaje nowa kolumna n_duplikaty
• On line # 4, The INSERT INTO ... Przy aktualizacji duplikatu klucza zapytanie służy do wykonania dodatkowej aktualizacji Po znalezieniu duplikatu (w tym przypadku zwiększenie licznika) INSERT INTO ... Przy aktualizacji duplikatów klucza zapytanie może być używane do wykonywania różnych typów aktualizacji dla znalezionych duplikatów.

✔ Zmiana regenerująca auto-Przyrostowy identyfikator pola

Czasami używamy pola auto-incremental i, aby utrzymać indeks tak zwarty, jak to możliwe, możemy skorzystać z usunięcia duplikatów, aby odtworzyć pole auto-incremental w nowej tabeli tymczasowej.

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;

• w linii # 3, zamiast zaznaczania wszystkich pól w tabeli, pole id jest pomijane, aby silnik DB generował nowe automatycznie

✔ dalsze zmiany

Wiele dalszych modyfikacji jest również wykonalnych w zależności od pożądanego zachowania. Jako przykład, następujące zapytania będą używać drugiej tabeli tymczasowej, aby, oprócz 1) zachować ostatni wpis zamiast pierwszego; i 2) zwiększyć licznik znalezionych duplikatów; również 3) zregenerować Auto-Przyrostowy identyfikator pola, zachowując kolejność wprowadzania, jak było na poprzednich danych.

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;

Mam taki snipet dla SQLServer ale myślę, że można go używać w innych DBMS z niewielkimi zmianami:

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

Zapomniałem ci powiedzieć, że to zapytanie nie usuwa wiersza o najniższym id zduplikowanych wierszy. Jeśli to działa, spróbuj tego zapytania:

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

Proste i szybkie dla wszystkich przypadków:

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

Szybszym sposobem jest wstawianie odrębnych wierszy do tymczasowej tabeli. Używając delete, usunięcie duplikatów z tabeli 8 milionów wierszy Zajęło mi kilka godzin. Używając insert i distinct, zajęło to zaledwie 13 minut.

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;  

Ciągle odwiedzam tą stronę w google "Usuń duplikaty z mysql" ale dla mnie rozwiązania nie działają bo mam InnoDB mysql

Ten kod działa lepiej w każdej chwili

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 = nazwa tabeli, którą musisz wyczyścić

TableToclean_temp = tymczasowa tabela utworzona i usunięta

To rozwiązanie spowoduje Przeniesienie duplikatów do jednej tabeli , A uników do innej.

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

Rozwiązanie, które jest proste do zrozumienia i działa bez klucza podstawowego:

1) Dodaj nową kolumnę logiczną

alter table mytable add tokeep boolean;

2) Dodaj ograniczenie na zduplikowanych kolumnach i nowej kolumnie

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

3) Ustaw kolumnę logiczną na true. To powiedzie się tylko w jednym z powielonych wierszy ze względu na nowe ograniczenie

update ignore mytable set tokeep = true;

4) Usuń wiersze, które nie zostały oznaczone jako tokeep

delete from mytable where tokeep is null;

5) upuść dodaną kolumnę

alter table mytable drop tokeep;

Sugeruję, aby zachować ograniczenie, które dodałeś, dzięki czemu nowe duplikaty są zapobiegane w przyszłości.

Usuń zduplikowane wiersze za pomocą instrukcji DELETE JOIN MySQL udostępnia instrukcję Usuń Dołącz, której można użyć do szybkiego usuwania zduplikowanych wierszy.

Następująca instrukcja usuwa zduplikowane wiersze i zachowuje najwyższy id:

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

Jeśli masz dużą tabelę z ogromną liczbą rekordów, powyższe rozwiązania nie będą działać lub nie zajmą zbyt dużo czasu. Wtedy mamy inne rozwiązanie

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

Chciałbym być trochę bardziej szczegółowy, co do tego, które rekordy usuwam, więc oto moje rozwiązanie:

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
)

Możesz łatwo usunąć zduplikowane rekordy z tego kodu..

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

Musiałem to zrobić z polami tekstowymi i natknąłem się na limit 100 bajtów na indeksie.

Rozwiązałem to dodając kolumnę, robiąc hash md5 pól i robiąc zmianę.

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