solo - MySQL elimina rápidamente los duplicados de la gran base de datos

sql eliminar registros repetidos dejando solo 1

Question

Tengo una base de datos MySQL grande (> Mil rows) desordenada por duplicados. Creo que podría ser de 1/4 a 1/2 de todo el DB lleno de ellos. Necesito deshacerme de ellos rápidamente (me refiero al tiempo de ejecución de la consulta). Así es como se ve:
id (índice) | texto1 | text2 | text3
La combinación text1 y text2 debe ser única; si hay duplicados, solo debe permanecer una combinación con text3 NOT NULL. Ejemplo:

1 | abc | def | NULL  
2 | abc | def | ghi  
3 | abc | def | jkl  
4 | aaa | bbb | NULL  
5 | aaa | bbb | NULL  

... se convierte en:

1 | abc | def | ghi   #(doesn''t realy matter id:2 or id:3 survives)   
2 | aaa | bbb | NULL  #(if there''s no NOT NULL text3, NULL will do)

Los nuevos identificadores pueden ser cualquier cosa, no dependen de los viejos identificadores de tablas.
He intentado cosas como:

CREATE TABLE tmp SELECT text1, text2, text3
FROM my_tbl;
GROUP BY text1, text2;
DROP TABLE my_tbl;
ALTER TABLE tmp RENAME TO my_tbl;

O SELECCIONE DISTINCT y otras variaciones.
Mientras trabajan en bases de datos pequeñas, el tiempo de ejecución de consultas en el mío es enorme (nunca llegó al final, en realidad;> 20 minutos)

¿Hay alguna forma más rápida de hacer eso? Por favor ayudame a resolver este problema.

Answer 1

Creo que esto lo hará, usando la clave duplicada + ifnull ():

create table tmp like yourtable;

alter table tmp add unique (text1, text2);

insert into tmp select * from yourtable 
    on duplicate key update text3=ifnull(text3, values(text3));

rename table yourtable to deleteme, tmp to yourtable;

drop table deleteme;

Debe ser mucho más rápido que cualquier cosa que requiera group by o distinct o una subconsulta, o incluso ordenar por. Esto ni siquiera requiere una clasificación de archivos, que va a matar el rendimiento en una gran tabla temporal. Todavía requerirá un escaneo completo sobre la tabla original, pero no hay forma de evitar eso.

Answer 2

Encontré este simple código de 1 línea para hacer exactamente lo que necesitaba:

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

Tomado de: http://mediakey.dk/~cc/mysql-remove-duplicate-entries/

Answer 3

No tengo mucha experiencia con MySQL. Si tiene funciones analíticas, intente:

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 )

la cláusula where opcional hace que los medios que tendrá que ejecutar múltiples veces, uno para cada letra, etc. Crear un índice en texto1?

Antes de ejecutar esto, confirme que "desc texto" ordenará nulls último en MySQL.

Answer 4

Para tablas grandes con pocos duplicados, puede evitar copiar toda la tabla en otro lugar. Una forma es crear una tabla temporal que contenga las filas que desea conservar (para cada clave con duplicados) y luego eliminar duplicados de la tabla original.

Un ejemplo se da here .

Answer 5

Sé que este es un hilo antiguo, pero tengo un método un tanto desordenado que es mucho más rápido y personalizable, en términos de velocidad diría 10 segundos en lugar de 100 segundos (10: 1).

Mi método requiere todas esas cosas desordenadas que intentabas evitar:

• Agrupar por (y Tener)
• grupo concat con ORDER BY
• 2 tablas temporales
• usando archivos en el disco!
• de alguna manera (php?) borrar el archivo después

Pero cuando hablas de MILLONES (o en mi caso de Decenas de millones) vale la pena.

de todos modos, no es mucho porque los comentarios están en portugués, pero aquí está mi muestra:

EDITAR : si recibo comentarios, explicaré más a fondo cómo funciona :)

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;

Answer 6

Si puede crear una tabla nueva, hágalo con una clave única en los campos text1 + text2. Luego inserte en la tabla ignorando errores (usando la sintaxis INSERT IGNORE):

select * from my_tbl order by text3 desc

• Creo que el orden por text3 desc pondrá los NULL al último, pero revisa eso.

Los índices en todas esas columnas podrían ayudar mucho, pero crearlos ahora podría ser bastante lento.

Answer 7

eliminar duplicados sin eliminar claves externas

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

Answer 8

puede eliminar todas las entradas duplicadas mediante esta simple consulta. que seleccionará todos los registros duplicados y los eliminará.

 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

Answer 9

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
)

Esto consulta todos los registros, grupos por campos de distinción y órdenes por ID (significa que seleccionamos el primer registro de texto nulo nulo). Luego seleccionamos los id''s de ese resultado (estos son buenos ids ... no serán borrados) y borramos todos los ID que NO SON.

Cualquier consulta como esta que afecte a toda la tabla será lenta. Solo necesita ejecutarlo y dejarlo desplegar para que pueda evitarlo en el futuro.

Después de haber hecho esta "corrección", aplicaría INDICE ÚNICO (texto1, texto2) a esa tabla. Para evitar la posibilidad de duplicados en el futuro.

Si desea ir a la ruta "crear una nueva tabla y reemplazar la anterior". Puede usar la instrucción de selección muy interna para crear su declaración de inserción.

Específico de MySQL (supone que la nueva tabla se llama my_tbl2 y tiene exactamente la misma estructura):

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

Ver MySQL INSERTAR ... SELECCIONAR para más información.