Este artículo muestra cómo encontrar filas duplicadas en una tabla de base de datos. Esta es una pregunta muy común para principiantes. La técnica básica es sencilla. También mostraré algunas variaciones, como cómo encontrar «duplicados en dos columnas» (una pregunta reciente sobre el canal de IRC #mysql).

Cómo encontrar filas duplicadas

El primer paso es definir qué hace que una fila sea un duplicado de otra fila. La mayoría de las veces esto es fácil: tienen el mismo valor en alguna columna. Tomaré esto como una definición de trabajo para este artículo, pero es posible que necesita modificar las consultas siguientes si su noción de «duplicado» es más complicada.

Para este artículo, usaré estos datos de muestra:

Las dos primeras filas tienen el mismo en la columna day, así que si considero que son duplicados, aquí hay una consulta para encontrarlos. La consulta utiliza una cláusula GROUP BY para colocar todas las filas con el mismo valor day en un «grupo» y luego contar el tamaño del grupo:

Las filas duplicadas tienen un recuento mayor que uno. Si solo desea ver las filas duplicadas, debe usar un HAVING cláusula (no una WHERE cláusula), como esta:

Esta es la técnica básica: agrupar por la columna que contiene duplicados y mostrar solo aquellos grupos que tienen más de una fila.

¿Por qué no puede usar una cláusula WHERE?

A La cláusula WHERE filtra las filas antes de que se agrupen. Una cláusula HAVING las filtra después de la agrupación. Por eso no se puede utilizar una WHERE cláusula en la consulta anterior.

Cómo eliminar filas duplicadas

Una pregunta relacionada es cómo eliminar las filas ‘duplicadas’ una vez que encontrarlos. Un t Preguntar cuando se limpian datos incorrectos es eliminar todos los duplicados menos uno, para que pueda poner los índices adecuados y las claves primarias en la tabla y evitar que los duplicados ingresen nuevamente a la tabla. Lo que debe hacer es asegurarse de que su definición sea clara. ¿Exactamente qué fila quieres mantener? ¿El primero? ¿El que tiene el mayor valor de alguna columna? Para este artículo, supongo que desea mantener la «primera» fila, la que tiene el valor más pequeño de la columna id. Eso significa que desea eliminar cada dos filas.

Probablemente la forma más fácil de hacerlo es con una tabla temporal. Especialmente en MySQL, existen algunas restricciones sobre la selección de una tabla y su actualización en la misma consulta. Puede sortear estos, como explico en mi artículo Cómo seleccionar desde un objetivo de actualización en MySQL, pero simplemente evitaré estas complicaciones y usaré una tabla temporal.

La definición exacta de la tarea es para eliminar todas las filas que tienen un duplicado, excepto la fila con el valor mínimo de id para ese grupo. Por lo tanto, debe buscar no solo las filas en las que hay más de uno en el grupo, también debe buscar la fila que desea conservar. Puede hacerlo con la función MIN(). Aquí hay algunas consultas para crear la tabla temporal y encontrar los datos que necesita para hacer DELETE:

Ahora que tiene estos datos, puede proceder a eliminarlos. las filas «malas». Hay muchas formas de hacer esto, y algunas son mejores que otras (vea mi artículo sobre problemas de muchos a uno en SQL), pero nuevamente evitaré los puntos más finos y solo le mostraré una sintaxis estándar que debería funcionar en cualquier RDBMS que admita subconsultas:

Si su RDBMS no admite subconsultas, o si es más eficiente, es posible que desee realizar una eliminación de varias tablas. La sintaxis de esto varía según el sistema, por lo que debe consultar la documentación de su sistema. Es posible que también deba hacer todo esto en una transacción para evitar que otros usuarios cambien los datos mientras está trabajando, si eso le preocupa.

Cómo encontrar duplicados en varias columnas

Alguien recientemente hizo una pregunta similar a esta en el canal de IRC #mysql:

Tengo una tabla con columnas b y c que enlazan otras dos tablas b y c, y quiero para buscar todas las filas que tienen duplicados en b o c.

Era difícil entender exactamente qué significaba esto, pero después de una conversación lo entendí: la persona quería poder poner índices únicos en las columnas b y c por separado.

Es bastante fácil encontrar filas con valores duplicados en una u otra columna, como le mostré anteriormente: solo agrupe por esa columna mn y cuente el tamaño del grupo. Y es fácil encontrar filas enteras que sean duplicados exactos de otras filas: simplemente agrúpelas por tantas columnas como necesite.Pero es más difícil identificar filas que tienen un valor b duplicado o un valor c duplicado. Tome la siguiente tabla de muestra, que es aproximadamente lo que describió la persona:

Ahora, puede ver fácilmente que hay algunas filas ‘duplicadas’ en esta tabla, pero no hay dos filas que tengan la misma tupla {b, c}. Es por eso que esto es un poco más difícil de resolver.

Consultas que no funcionan

Si agrupa por dos columnas juntas, obtendrá varios resultados dependiendo de cómo agrupe y contar. Aquí es donde el usuario de IRC se estaba quedando perplejo. A veces, las consultas encuentran algunos duplicados, pero no otros. Estas son algunas de las cosas que esta persona probó:

Esta consulta devuelve todas las filas de la tabla, con un COUNT(*) de 1, que parece ser un comportamiento incorrecto, pero en realidad no lo es. ¿Por qué? Porque el > 1 está dentro del COUNT(). Es bastante fácil pasarlo por alto, pero esta consulta es en realidad la misma que

¿Por qué? Porque (b > 1) es una expresión booleana. Eso no es lo que quieres en absoluto. Quieres

Esto devuelve cero filas, por supuesto, porque no hay tuplas {b, c} duplicadas. La persona probó muchas otras combinaciones de HAVING cláusulas y OR y AND, agrupando por una columna y contando la otra, y así sucesivamente:

Sin embargo, nada encontró todos los duplicados. Lo que creo que lo hizo más frustrante es que funcionó parcialmente, lo que hizo que la persona pensara que era casi la consulta correcta … tal vez solo otra variación la obtendría …

De hecho, es imposible hacerlo con este tipo de Consulta GROUP BY simple. ¿Por qué es esto? Esto se debe a que cuando agrupa por una columna, distribuye valores similares de la otra columna en varios grupos. Puede ver esto visualmente ordenando por esas columnas, que es lo que hace la agrupación. Primero, ordene por columna b y vea cómo están agrupados:

a	b	c
7	1	1
8	1	2
9	1	3
10	2	1
11	2	2
12	2	3
13	3	1
14	3	2
15	3	3

Cuando ordena (agrupa) por columna b, los valores duplicados en la columna c se distribuyen en diferentes grupos, por lo que no puede contarlos con COUNT(DISTINCT c) como la persona estaba tratando de hacer. Las funciones de agregación como COUNT() solo operan dentro de un grupo y no tienen acceso a las filas que están ubicadas en otros grupos. De manera similar, cuando realiza un pedido por c, los valores duplicados en la columna b se distribuyen en diferentes grupos. No es posible hacer que esta consulta haga lo que se desea.

Algunas soluciones correctas

Probablemente la solución más simple sea encontrar los duplicados para cada columna por separado y UNION juntos, así:

La columna what_col en la salida indica en qué columna se encontró el valor duplicado. Otro enfoque es usar subconsultas:

Esto es probablemente mucho menos eficiente que el enfoque UNION, y mostrará cada fila duplicada, no solo los valores que están duplicados. Otro enfoque más es realizar uniones automáticas contra subconsultas agrupadas en la cláusula FROM. Esto es más complicado de escribir correctamente, pero podría ser necesario para algunos datos complejos o para mejorar la eficiencia:

Cualquiera de estas consultas funcionará, y estoy seguro de que también hay otras formas. Si puede usar UNION, probablemente sea lo más fácil.