MySQL szybko usuń duplikaty z dużej bazy danych

Znalazłem ten prosty 1-liniowy kod, aby zrobić dokładnie to, czego potrzebowałem:

ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);

Wzięte z: http://mediakey.dk/~CC / mysql-remove-duplicate-entries/

DELETE FROM dups
WHERE id NOT IN(
    SELECT id FROM (
        SELECT DISTINCT id, text1, text2
            FROM dups
        GROUP BY text1, text2
        ORDER BY text3 DESC
    ) as tmp
)

To odpytuje wszystkie rekordy, grupuje po polach rozróżnienia i porządkuje po ID (czyli wybieramy pierwszy rekord nie null text3). Następnie wybieramy id z tego wyniku (są to dobre ID...nie zostaną usunięte) i usuń wszystkie identyfikatory, które nie są tymi.

Każde zapytanie takie jak to wpływające na całą tabelę będzie powolne. Wystarczy go uruchomić i pozwolić mu się rozwinąć, aby można było temu zapobiec w przyszłości.

Po wykonaniu tej "poprawki" zastosuję unikalny indeks (text1, text2) do tego stołu. Aby zapobiec możliwości duplikatów w przyszłości.

Jeśli chcesz przejść do trasy "Utwórz nową tabelę i zastąp starą". Możesz użyć instrukcji very inner select do utworzenia instrukcji insert.

Specyficzne dla MySQL (zakłada, że nowa tabela ma nazwę my_tbl2 i ma dokładnie taką samą strukturę):

INSERT INTO my_tbl2
    SELECT DISTINCT id, text1, text2, text3
            FROM dups
        GROUP BY text1, text2
        ORDER BY text3 DESC

Zobacz MySQL INSERT ... Wybierz , aby uzyskać więcej informacji.

Usuwanie duplikatów bez usuwania kluczy obcych

create table tmp like mytable;
ALTER TABLE tmp ADD UNIQUE INDEX(text1, text2, text3, text4, text5, text6);
insert IGNORE into tmp select * from mytable;
delete from mytable where id not in ( select id from tmp);

Jeśli możesz utworzyć nową tabelę, zrób to za pomocą unikalnego klucza na polach text1 + text2. Następnie wstaw do tabeli ignorowanie błędów (używając składni INSERT IGNORE):

select * from my_tbl order by text3 desc

• myślę, że kolejność przez text3 desc spowoduje, że null będzie ostatni, ale sprawdź to jeszcze raz.

Indeksy na wszystkich tych kolumnach mogą bardzo pomóc, ale tworzenie ich teraz może być dość powolne.

W przypadku dużych tabel z kilkoma duplikatami należy unikać kopiowania całej tabeli w inne miejsce. Jednym ze sposobów jest utworzenie tymczasowej tabeli zawierającej wiersze, które chcesz zachować (dla każdego klucza z duplikatami), a następnie usunięcie duplikatów z oryginalnej tabeli.

Przykład jest podany TUTAJ .

Nie mam dużego doświadczenia z MySQL. Jeśli ma funkcje analityczne spróbuj:

delete from my_tbl
 where id in (
     select id 
       from (select id, row_number()
                            over (partition by text1, text2 order by text3 desc) as rn
               from my_tbl
               /* optional: where text1 like 'a%'  */
             ) as t2
       where rn > 1
     )

Opcjonalna klauzula where sprawia, że będziesz musiał uruchomić ją kilka razy, po jednym dla każdej litery itp. Utworzyć indeks na text1?

Przed uruchomieniem, upewnij się, że "text desc" będzie sortować null jako ostatni w MySQL.

Wiem, że to stary wątek, ale mam nieco niechlujny sposób, który jest znacznie szybszy i konfigurowalny, pod względem szybkości powiedziałbym 10sec zamiast 100sec (10: 1).

Moja metoda wymaga tego wszystkiego brudnego rzeczy, których starałeś się uniknąć:

• Group by (and Having)
• Grupa Konkatedra z zamówieniem przez
• 2 tabele tymczasowe
• używanie plików na dysku!
• jakoś (php?) usuwanie pliku po

Anyway its not much bo komentarz jest po portugalsku ale oto moja próbka:

EDIT : jak dostanę Komentarze to wyjaśnię dalej Jak to działa:)

START TRANSACTION;

DROP temporary table if exists to_delete;

CREATE temporary table to_delete as (
    SELECT
        -- escolhe todos os IDs duplicados menos os que ficam na BD
        -- A ordem de escolha dos IDs é dada por "ORDER BY campo_ordenacao DESC" em que o primeiro é o que fica
        right(
            group_concat(id ORDER BY campos_ordenacao DESC SEPARATOR ','),
            length(group_concat(id ORDER BY campos_ordenacao DESC SEPARATOR ',')) 
                - locate(",",group_concat(id ORDER BY campos_ordenacao DESC SEPARATOR ','))
        ) as ids,

        count(*) as c

    -- Tabela a eliminar duplicados
    FROM teste_dup

    -- campos a usar para identificar  duplicados
    group by test_campo1, test_campo2, teste_campoN
    having count(*) > 1 -- é duplicado
);

-- aumenta o limite desta variável de sistema para o máx 
SET SESSION group_concat_max_len=4294967295;

-- envia os ids todos a eliminar para um ficheiro
select group_concat(ids SEPARATOR ',') from to_delete INTO OUTFILE 'sql.dat';

DROP temporary table if exists del3;
create temporary table del3 as (select CAST(1 as signed) as ix LIMIT 0);

-- insere os ids a eliminar numa tabela temporaria a partir do ficheiro
load data infile 'sql.dat' INTO TABLE del3
LINES TERMINATED BY ',';

alter table del3 add index(ix);

-- elimina os ids seleccionados
DELETE teste_dup -- tabela 
from teste_dup -- tabela

join del3 on id=ix;

COMMIT;

Możesz usunąć wszystkie zduplikowane wpisy za pomocą tego prostego zapytania. spowoduje to zaznaczenie wszystkich zduplikowanych rekordów i usunięcie ich.

 DELETE i1 
FROM TABLE i1
LEFT JOIN TABLE i2
  ON i1.id = i2.id
 AND i1.colo = i2.customer_invoice_id
 AND i1.id < i2.id
WHERE i2.customer_invoice_id IS NOT NULL