Encontrar valores duplicados en MySQL

Tengo una tabla con una columna varchar, y me gustaría encontrar todos los registros que tienen valores duplicados en esta columna. ¿Cuál es la mejor consulta que puedo usar para encontrar los duplicados?

Haga un SELECT con una cláusula GROUP BY . Digamos que el nombre es la columna en la que desea duplicar:

 SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1; 

Esto devolverá un resultado con el valor del nombre en la primera columna y un recuento de cuántas veces ese valor aparecerá en el segundo.

 SELECT varchar_col FROM table GROUP BY varchar_col HAVING count(*) > 1; 
 SELECT * FROM mytable mto WHERE EXISTS ( SELECT 1 FROM mytable mti WHERE mti.varchar_column = mto.varchar_column LIMIT 1, 1 ) 

Esta consulta devuelve registros completos, no solo las distintas varchar_column .

Esta consulta no usa COUNT(*) . Si hay muchos duplicados, COUNT(*) es costoso y no necesita todo el COUNT(*) , solo necesita saber si hay dos filas con el mismo valor.

Tener un índice en varchar_column , por supuesto, acelerará esta consulta en gran medida.

Basándose en la respuesta de levik para obtener los ID de las filas duplicadas, puede hacer un GROUP_CONCAT si su servidor lo admite (esto devolverá una lista de identificadores separados por comas).

 SELECT GROUP_CONCAT(id), name, COUNT(*) c FROM documents GROUP BY name HAVING c > 1; 
 SELECT * FROM `dps` WHERE pid IN (SELECT pid FROM `dps` GROUP BY pid HAVING COUNT(pid)>1) 

Suponiendo que su tabla se llama TableABC y la columna que desea es Col y la clave principal para T1 es Key.

 SELECT a.Key, b.Key, a.Col FROM TableABC a, TableABC b WHERE a.Col = b.Col AND a.Key <> b.Key 

La ventaja de este enfoque sobre la respuesta anterior es que da la clave.

Para encontrar cuántos registros son duplicados en la columna de nombre en Employee, la siguiente consulta es útil;

 Select name from employee group by name having count(*)>1; 
 SELECT t.*,(select count(*) from city as tt where tt.name=t.name) as count FROM `city` as t where ( select count(*) from city as tt where tt.name=t.name ) > 1 order by count desc 

Reemplace la ciudad con su Mesa. Reemplace el nombre con su nombre de campo

Mi última consulta incorporó algunas de las respuestas que ayudaron, combinando group by, count y GROUP_CONCAT.

 SELECT GROUP_CONCAT(id), `magento_simple`, COUNT(*) c FROM product_variant GROUP BY `magento_simple` HAVING c > 1; 

Esto proporciona la identificación de ambos ejemplos (separados por comas), el código de barras que necesito y cuántos duplicados.

Cambie la tabla y las columnas en consecuencia.

Vi el resultado anterior y la consulta funcionará bien si necesita verificar el valor de una sola columna que están duplicados. Por ejemplo, correo electrónico.

Pero si necesita verificar con más columnas y desea verificar la combinación del resultado, esta consulta funcionará bien:

 SELECT COUNT(CONCAT(name,email)) AS tot, name, email FROM users GROUP BY CONCAT(name,email) HAVING tot>1 (This query will SHOW the USER list which ARE greater THAN 1 AND also COUNT) 
 SELECT t.*, (SELECT COUNT(*) FROM city AS tt WHERE tt.name=t.name) AS count FROM `city` AS t WHERE (SELECT count(*) FROM city AS tt WHERE tt.name=t.name) > 1 ORDER BY count DESC 

Lo siguiente encontrará todos los product_id que se usan más de una vez. Solo obtiene un registro único por cada product_id.

 SELECT product_id FROM oc_product_reward GROUP BY product_id HAVING count( product_id ) >1 

Código tomado de: http://chandreshrana.blogspot.in/2014/12/find-duplicate-records-based-on-any.html

No veo ningún abordaje JOIN, que tenga muchos usos en términos de duplicados.

Este progtwig te da resultados reales duplicados.

 SELECT t1.* FROM table as t1 LEFT JOIN table as t2 ON t1.name=t2.name and t1.id!=t2.id WHERE t2.id IS NOT NULL ORDER BY t1.name 
 SELECT DISTINCT a.email FROM `users` a LEFT JOIN `users` b ON a.email = b.email WHERE a.id != b.id; 
 CREATE TABLE tbl_master (`id` int, `email` varchar(15)); INSERT INTO tbl_master (`id`, `email`) VALUES (1, 'test1@gmail.com'), (2, 'test2@gmail.com'), (3, 'test1@gmail.com'), (4, 'test2@gmail.com'), (5, 'test5@gmail.com'); QUERY : SELECT id, email FROM tbl_master WHERE email IN (SELECT email FROM tbl_master GROUP BY email HAVING COUNT(id) > 1) 

Tomando aún más la respuesta de @ maxyfc , necesitaba encontrar todas las filas que fueron devueltas con los valores duplicados, para poder editarlas en MySQL Workbench :

 SELECT * FROM table WHERE field IN ( SELECT field FROM table GROUP BY field HAVING count(*) > 1 ) ORDER BY field 

Para eliminar filas duplicadas con múltiples campos, primero cancánalos a la nueva clave única que se especifica para las únicas filas distintas, luego use el comando “agrupar por” para eliminar filas duplicadas con la misma nueva clave única:

 Create TEMPORARY table tmp select concat(f1,f2) as cfs,t1.* from mytable as t1; Create index x_tmp_cfs on tmp(cfs); Create table unduptable select f1,f2,... from tmp group by cfs; 

Una contribución muy tardía … en caso de que ayude a alguien a pasar al otro lado de la línea … Tuve la tarea de encontrar pares de transacciones coincidentes (en realidad ambas caras de las transferencias de cuenta a cuenta) en una aplicación bancaria, para identificar cuáles fueron los ‘de’ y ‘para’ para cada transacción de transferencia entre cuentas, así que terminamos con esto:

 SELECT LEAST(primaryid, secondaryid) AS transactionid1, GREATEST(primaryid, secondaryid) AS transactionid2 FROM ( SELECT table1.transactionid AS primaryid, table2.transactionid AS secondaryid FROM financial_transactions table1 INNER JOIN financial_transactions table2 ON table1.accountid = table2.accountid AND table1.transactionid <> table2.transactionid AND table1.transactiondate = table2.transactiondate AND table1.sourceref = table2.destinationref AND table1.amount = (0 - table2.amount) ) AS DuplicateResultsTable GROUP BY transactionid1 ORDER BY transactionid1; 

El resultado es que DuplicateResultsTable proporciona filas que contienen transacciones coincidentes (es decir, duplicadas), pero también proporciona las mismas identificaciones de transacción en reversa la segunda vez que coincide con el mismo par, por lo que el SELECT externo está allí para agrupar por la primera ID de transacción, que se realiza utilizando LEAST y GREATEST para asegurarse de que los dos transactionid estén siempre en el mismo orden en los resultados, lo que hace que GROUP sea ​​más seguro para el primero, eliminando así todas las coincidencias duplicadas. Recorrió casi un millón de registros e identificó más de 12,000 coincidencias en poco menos de 2 segundos. Por supuesto, el transactionid es el índice principal, lo que realmente ayudó.

 Select column_name, column_name1,column_name2, count(1) as temp from table_name group by column_name having temp > 1 
 SELECT ColumnA, COUNT( * ) FROM Table GROUP BY ColumnA HAVING COUNT( * ) > 1 

Prefiero usar funciones con ventana (MySQL 8.0+) para encontrar duplicados porque puedo ver toda la fila:

 WITH cte AS ( SELECT * ,COUNT(*) OVER(PARTITION BY col_name) AS num_of_duplicates_group ,ROW_NUMBER() OVER(PARTITION BY col_name ORDER BY col_name2) AS pos_in_group FROM table ) SELECT * FROM cte WHERE num_of_duplicates_group > 1; 

DB Fiddle Demo