Eliminar filas duplicadas en MySQL

Si no desea alterar las propiedades de la columna, puede usar la consulta a continuación.

Dado que tiene una columna que tiene identificadores únicos (por ejemplo, auto_increment columnas), puede usarla para eliminar los duplicados:

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

En MySQL, se puede simplificar aún más con el 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`;

MySQL tiene restricciones para referirse a la tabla de la que está borrando. Usted puede trabajar alrededor de eso con una tabla temporal, como:

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

De la sugerencia de Kostanos en los comentarios:
La única consulta lenta anterior es ELIMINAR, para los casos en los que tiene una base de datos muy grande. Esta consulta podría ser más rápida:

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

Si la instrucción IGNORE no funciona como en mi caso, puedes usar la siguiente instrucción:

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;

Hay otra solución :

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

Eliminar duplicados en tablas MySQL es un problema común, que es generalmente el resultado de una restricción faltante para evitar esos duplicados de antemano. Pero este problema común generalmente viene con necesidades específicas... eso requiere enfoques específicos. El enfoque debe ser diferente dependiendo, por ejemplo, del tamaño de los datos, la entrada duplicada que se debe mantener (generalmente la primera o la última), si hay índices que se deben mantener, o si queremos realizar cualquier acción sobre los datos duplicados.

También hay algunas especificidades en MySQL en sí, como no ser capaz de hacer referencia a la misma tabla en una causa de al realizar una actualización de tabla (que va a elevar MySQL error #1093). Esta limitación se puede superar mediante el uso de una consulta interna con una tabla temporal (como se sugiere en algunos enfoques anteriores). Pero esta consulta interna no funcionará especialmente bien cuando se trata de fuentes de big data.

Sin embargo, existe un mejor enfoque para eliminar duplicados, que son eficientes y confiables, y que se pueden adaptar fácilmente a diferentes necesidades.

La idea general es crear una nueva tabla temporal, generalmente agregando una restricción única para evitar más duplicados, e INSERTAR los datos de su tabla anterior en la nueva, mientras se ocupa de los duplicados. Este enfoque se basa en simples consultas de INSERCIÓN de MySQL, crea una nueva restricción para evitar más duplicados y omite la necesidad de usar una consulta interna para buscar duplicados y una tabla temporal que debe mantenerse en memoria (por lo tanto, también se adapta a las fuentes de big data).

Así es como se puede lograr. Dado que tenemos una tabla empleado , con las siguientes columnas:

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

Para eliminar las filas con una columna duplicada ssn , y mantener solo la primera entrada encontrada, se puede seguir el siguiente proceso:

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

Explicación técnica

• La línea #1 crea una nueva tabla tmp_eployee con exactamente la misma estructura que el empleado tabla
• La línea #2 agrega una restricción ÚNICA a la nueva tabla tmp_eployee para evitar más duplicados
• La línea # 3 escanea la tabla original employee por id, insertando nuevas entradas de empleado en la nueva tabla tmp_eployee , mientras ignora las entradas duplicadas
• La línea #4 cambia el nombre de las tablas, de modo que la nueva tabla employee contiene todas las entradas sin los duplicados, y copia de seguridad de los datos anteriores se mantiene en la tabla backup_employee

⇒ Usando este enfoque, 1.6 M registros se convirtieron en 6k en menos de 200s.

Chetan , siguiendo este proceso, puede eliminar rápida y fácilmente todos sus duplicados y crear una restricción ÚNICA ejecutando:

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;

Por supuesto, este proceso se puede modificar aún más para adaptarlo a diferentes necesidades al eliminar duplicados. Algunos ejemplos seguir.

✔ Variación para mantener la última entrada en lugar de la primera

A veces necesitamos mantener la última entrada duplicada en lugar de la primera.

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;

• En la línea # 3, la cláusula ORDER BY id DESC hace que los últimos ID tengan prioridad sobre el resto

✔ Variación para realizar algunas tareas en los duplicados, por ejemplo, mantener un recuento de los duplicados encontrados

A Veces necesitamos realizar algún procesamiento adicional en las entradas duplicadas que se encuentran (como mantener un recuento de los duplicados).

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;

• En la línea # 3, se crea una nueva columna n_duplicates
• En la línea #4, el INSERTAR EN ... ON DUPLICATE KEY UPDATE consulta se utiliza para realizar una actualización adicional cuando se encuentra un duplicado (en este caso, el aumento de un contador) La INSERTAR EN ... ON DUPLICATE KEY UPDATE consulta se puede utilizar para realizar diferentes tipos de actualizaciones para los duplicados encontrados.

✔ Variación para regenerar el id de campo auto-incremental

A veces usamos un campo auto-incremental y, para mantener el índice lo más compacto posible, podemos aprovechar la eliminación de los duplicados para regenerar el campo auto-incremental en la nueva tabla temporal.

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;

• En la línea #3, en lugar de seleccionar todos los campos de la tabla, el campo id se omite para que el motor de base de datos genere uno nuevo automáticamente

✔ Otras variaciones

Muchas modificaciones adicionales también son factibles dependiendo del comportamiento deseado. Como ejemplo, las siguientes consultas usarán una segunda tabla temporal para, además de 1) mantener la última entrada en lugar de la primera; y 2) aumentar un contador en los duplicados encontrados; también 3) regenerar el id de campo auto-incremental mientras se mantiene el orden de entrada como estaba en los datos anteriores.

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;

Tengo este snipet de consulta para SQLServer pero creo que se puede usar en otros DBMS con pequeños cambios:

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

Olvidé decirte que esta consulta no elimina la fila con el id más bajo de las filas duplicadas. Si esto funciona para usted intente esta consulta:

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

Simple y rápido para todos los casos:

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

La forma más rápida es insertar filas distintas en una tabla temporal. Usando eliminar, me tomó unas horas eliminar duplicados de una tabla de 8 millones de filas. Usando insert y distinct, tomó solo 13 minutos.

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;  

Sigo visitando esta página cada vez que google "eliminar duplicados formulario mysql", pero para mis soluciones theIGNORE no funcionan porque tengo un tablas mysql InnoDB

Este código funciona mejor en cualquier momento

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 = el nombre de la tabla que necesita limpiar

TableToclean_temp = una tabla temporal creada y eliminada

Esta solución mover los duplicados en una tabla y el uniques en otra.

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

Una solución que es simple de entender y funciona sin clave primaria:

1) añadir una nueva columna booleana

alter table mytable add tokeep boolean;

2) agregue una restricción a las columnas duplicadas Y la nueva columna

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

3) establezca la columna booleana en true. Esto tendrá éxito solo en una de las filas duplicadas debido a la nueva restricción

update ignore mytable set tokeep = true;

4) eliminar las filas que no han sido marcadas como tokeep

delete from mytable where tokeep is null;

5) suelta la columna añadida

alter table mytable drop tokeep;

Le sugiero que mantenga la restricción que ha agregado, para evitar nuevos duplicados en el futuro.

Eliminar filas duplicadas mediante la instrucción DELETE JOIN MySQL le proporciona la instrucción DELETE JOIN que puede usar para eliminar filas duplicadas rápidamente.

La siguiente instrucción elimina las filas duplicadas y mantiene el id más alto:

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

Si tiene una tabla grande con un gran número de registros, entonces las soluciones anteriores no funcionarán o tomarán demasiado tiempo. Entonces tenemos una solución diferente

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

Me gusta ser un poco más específico en cuanto a qué registros elimino, así que aquí está mi solución:

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
)

Puede eliminar fácilmente los registros duplicados de este código..

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

Tuve que hacer esto con campos de texto y me encontré con el límite de 100 bytes en el índice.

Resolví esto agregando una columna, haciendo un hash md5 de los campos y haciendo el 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`);