traer - Eliminar filas duplicadas en MySQL
select mysql registros duplicados (17)
Tengo una tabla con los siguientes campos:
id (Unique)
url (Unique)
title
company
site_id
Ahora, necesito eliminar las filas que tienen el mismo title, company and site_id . Una forma de hacerlo será utilizando el siguiente SQL junto con un script ( PHP ):
SELECT title, site_id, location, id, count( * )
FROM jobs
GROUP BY site_id, company, title, location
HAVING count( * ) >1
Después de ejecutar esta consulta, puedo eliminar duplicados utilizando una secuencia de comandos del lado del servidor.
Pero, quiero saber si esto se puede hacer sólo mediante la consulta SQL.
Elimine filas duplicadas utilizando la declaración DELETE JOIN MySQL le proporciona la instrucción DELETE JOIN que puede usar para eliminar filas duplicadas rápidamente.
La siguiente declaración elimina 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;
Esta solución moverá los duplicados a una tabla y los únicos a 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
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 ...
La eliminación de duplicados en las tablas de MySQL es un problema común, que generalmente es el resultado de una restricción que falta para evitar esos duplicados de antemano. Pero este problema común generalmente viene con necesidades específicas ... que requieren enfoques específicos. El enfoque debe ser diferente según, por ejemplo, el tamaño de los datos, la entrada duplicada que se debe conservar (generalmente la primera o la última), si hay que mantener índices o si queremos realizar algún análisis adicional. Acción sobre los datos duplicados.
También hay algunas especificidades en MySQL, como no poder hacer referencia a la misma tabla en una causa DESDE al realizar una ACTUALIZACIÓN de tabla (generará el error de MySQL # 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 datos grandes.
Sin embargo, existe un mejor enfoque para eliminar duplicados, que es a la vez eficiente y confiable, y que se puede adaptar fácilmente a diferentes necesidades.
La idea general es crear una nueva tabla temporal, generalmente agregando una restricción única para evitar duplicados adicionales, e INSERTAR los datos de su tabla anterior en la nueva, mientras cuida los duplicados. Este enfoque se basa en consultas sencillas de MySQL INSERT, crea una nueva restricción para evitar duplicados adicionales, y omite la necesidad de usar una consulta interna para buscar duplicados y una tabla temporal que debe mantenerse en la memoria (por lo tanto, también se ajustan las fuentes de datos grandes).
Así es como se puede lograr. Dado que tenemos un empleado de mesa, con las siguientes columnas:
employee (id, first_name, last_name, start_date, ssn)
Para eliminar las filas con una columna ssn duplicada 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;
Explicacion tecnica
- La línea # 1 crea una nueva tabla tmp_eployee con exactamente la misma estructura que la tabla de empleados
- La línea # 2 agrega una restricción ÚNICA a la nueva tabla tmp_eployee para evitar más duplicados
- La línea # 3 explora la tabla de empleados original por ID, insertando nuevas entradas de empleados en la nueva tabla tmp_eployee , mientras ignora las entradas duplicadas
- La línea # 4 renombra las tablas, de modo que la nueva tabla de empleados contiene todas las entradas sin los duplicados, y una copia de respaldo de los datos anteriores se mantiene en la tabla backup_employee
⇒ Usando este enfoque, los registros 1.6M se convirtieron en 6k en menos de 200s.
, siguiendo este proceso, podría 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 puede modificarse aún más para adaptarlo a diferentes necesidades al eliminar duplicados. Algunos ejemplos siguen.
✔ 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 las últimas ID obtengan prioridad sobre el resto
✔ Variación para realizar algunas tareas en los duplicados, por ejemplo, manteniendo un recuento en los duplicados encontrados
A veces necesitamos realizar un 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, la consulta INSERT INTO ... ON DUPLICATE KEY UPDATE se utiliza para realizar una actualización adicional cuando se encuentra un duplicado (en este caso, un contador). La consulta INSERT INTO ... ON DUPLICATE KEY UPDATE puede ser Se utiliza para realizar diferentes tipos de actualizaciones para los duplicados encontrados.
✔ Variación para regenerar la identificación de campo auto-incremental
Algunas 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 de identificación se omite para que el motor DB genere uno nuevo automáticamente.
✔ variaciones adicionales
Muchas modificaciones adicionales también son factibles dependiendo del comportamiento deseado. Como ejemplo, las siguientes consultas utilizará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) regenera la identificación 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;
La forma más rápida es insertar filas distintas en una tabla temporal. Usando la opción de borrar, me tomó algunas horas eliminar los duplicados de una tabla de 8 millones de filas. Utilizando inserto y distinto, solo tardó 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;
Me gusta ser un poco más específico en cuanto a qué registros borro, 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
)
MySQL tiene restricciones para referirse a la tabla de la que está eliminando. Puedes solucionar esto 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 DELETE, 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
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;
}
Sencillo 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);
Si la declaración IGNORE no funciona como en mi caso, puede usar la siguiente declaració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;
Si no desea modificar las propiedades de la columna, puede utilizar la consulta a continuación.
Como tiene una columna que tiene ID únicos (por ejemplo, columnas auto_increment ), 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, puedes simplificarlo aún más con el operador igual de seguro NULL (también conocido como "operador de nave espacial" ):
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`;
Si tiene una tabla grande con un gran número de registros, 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;
Sigo visitando esta página cada vez que busco en Google "eliminar duplicados de mysql", pero para mi las soluciones de THEIGNORE no funcionan porque tengo tablas de mysql de 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 necesitas limpiar
tableToclean_temp = una tabla temporal creada y eliminada
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é decirle 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)
Tuve que hacer esto con los campos de texto y encontré el límite de 100 bytes en el índice.
Resolví esto agregando una columna, haciendo un hash md5 de los campos y haciendo el cambio.
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`);
Una forma realmente fácil de hacer esto es agregar un índice UNIQUE en las 3 columnas. Cuando escriba la instrucción ALTER , incluya la IGNORE clave IGNORE . Al igual que:
ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (site_id, title, company);
Esto eliminará todas las filas duplicadas. Como un beneficio adicional, los futuros INSERTs que son duplicados producirán un error. Como siempre, es posible que desee realizar una copia de seguridad antes de ejecutar algo como esto ...
Una solución que es fácil de entender y funciona sin clave principal:
1) agregar una nueva columna booleana
alter table mytable add tokeep boolean;
2) agregar una restricción en las columnas duplicadas Y la nueva columna
alter table mytable add constraint preventdupe unique (mycol1, mycol2, tokeep);
3) establece la columna booleana en verdadero. Esto solo tendrá éxito en una de las filas duplicadas debido a la nueva restricción
update ignore mytable set tokeep = true;
4) eliminar filas que no han sido marcadas como tokeep
delete from mytable where tokeep is null;
5) soltar la columna añadida
alter table mytable drop tokeep;
Le sugiero que mantenga la restricción que agregó, para evitar nuevos duplicados en el futuro.