Cómo encontrar y eliminar datos duplicados en MySQL: Guía completa con ejemplos SQL

1. Introducción

Al operar una base de datos, no es raro encontrarse con problemas como “registros duplicados insertados” o “datos que deberían ser únicos aparecen varias veces”. En entornos donde se utilizan bases de datos relacionales como MySQL, extraer y gestionar datos duplicados es una tarea esencial para mantener la precisión y la calidad de los datos.

Por ejemplo, en tablas centrales del negocio como información de miembros, datos de productos e historial de pedidos, pueden insertarse registros duplicados debido a errores de usuario o fallos del sistema. Si no se corrigen, esto puede reducir la exactitud de la agregación y el análisis, y también puede generar errores inesperados o problemas operativos.

Para resolver este “problema de datos duplicados”, primero debes identificar qué registros están duplicados, y luego organizar o eliminar esos registros duplicados según la situación. Sin embargo, usar solo una sentencia SELECT estándar en MySQL suele no ser suficiente para detectar duplicados de manera eficiente. Se requieren técnicas SQL ligeramente avanzadas y enfoques prácticos.

En este artículo nos centramos en “Cómo extraer datos duplicados en MySQL”, cubriendo desde sentencias SQL básicas hasta aplicaciones prácticas, consideraciones de rendimiento y manejo de errores comunes. Tanto si eres un principiante en bases de datos como un ingeniero que escribe SQL a diario, esta guía pretende ofrecer conocimientos prácticos y orientados al campo.

2. Conceptos básicos: Detectar duplicados usando una columna clave

La forma más básica de extraer datos duplicados en MySQL es identificar casos donde “varios registros comparten el mismo valor en una columna específica (columna clave)”. En esta sección explicamos consultas SQL representativas usadas para detectar valores clave duplicados y cómo funcionan.

2-1. Detectar duplicados con GROUP BY y HAVING

La técnica fundamental para la detección de duplicados consiste en agrupar los registros por una columna específica usando la cláusula GROUP BY, y luego filtrar los grupos que contienen dos o más registros mediante la cláusula HAVING. Aquí tienes un ejemplo típico:

SELECT key_column, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY key_column
HAVING COUNT(*) > 1;

Ejemplo: Extracción de direcciones de correo electrónico de miembros duplicados

SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Al ejecutar esta consulta, si la misma dirección de correo electrónico ha sido registrada varias veces, la dirección y el número de duplicados (count) se mostrarán en los resultados.

2-2. Detectar duplicados en múltiples columnas

Si necesitas detectar duplicados basándote en una combinación de dos o más columnas, puedes especificar varias columnas en la cláusula GROUP BY usando la misma lógica.

SELECT col1, col2, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY col1, col2
HAVING COUNT(*) > 1;

Con este método, puedes detectar duplicados donde varias condiciones coinciden completamente, como “mismo nombre completo y fecha de nacimiento” o “mismo ID de producto y fecha de pedido”.

2-3. Calcular el número total de registros duplicados

Si deseas comprender la escala general de la duplicación, puedes usar una subconsulta para calcular el número total de entradas duplicadas.

SELECT SUM(duplicate_count) AS total_duplicates
FROM (
  SELECT COUNT(*) AS duplicate_count
  FROM table_name
  GROUP BY key_column
  HAVING COUNT(*) > 1
) AS duplicates;

Esta consulta suma el número de entradas duplicadas en todos los grupos de duplicados.

Combinando GROUP BY y HAVING, puedes extraer datos duplicados en MySQL de manera simple y eficiente.

3. Extracción de todos los registros que comparten claves duplicadas

En la sección anterior presentamos cómo listar solo los “valores de clave duplicados”. Sin embargo, en el trabajo real a menudo necesitas confirmar “qué registros exactos están duplicados y revisar todos sus detalles”. Por ejemplo, puede que quieras revisar los perfiles de usuario completamente duplicados o inspeccionar fila por fila los datos de productos duplicados.

En esta sección, explicamos patrones SQL prácticos para extraer todos los registros que comparten claves duplicadas.

3-1. Extracción de registros duplicados usando una subconsulta

El enfoque más básico es obtener la lista de valores de claves duplicadas en una subconsulta y luego recuperar todos los registros que coinciden con esas claves.

SELECT *
FROM table_name
WHERE key_column IN (
  SELECT key_column
  FROM table_name
  GROUP BY key_column
  HAVING COUNT(*) > 1
);

Ejemplo: Extracción de todos los registros con direcciones de correo electrónico duplicadas

SELECT *
FROM users
WHERE email IN (
  SELECT email
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
);

Al ejecutar esta consulta, se extraen todas las filas de la tabla “users” donde la dirección de correo electrónico está duplicada (incluyendo columnas como ID, fecha de registro, etc.).

3-2. Extracción eficiente usando EXISTS

Si necesita manejar conjuntos de datos grandes o le preocupa el rendimiento, usar EXISTS también puede ser eficaz. IN y EXISTS son similares, pero según el volumen de datos y el indexado, uno puede ser más rápido que el otro.

SELECT *
FROM table_name t1
WHERE EXISTS (
  SELECT 1
  FROM table_name t2
  WHERE t1.key_column = t2.key_column
  GROUP BY t2.key_column
  HAVING COUNT(*) > 1
);

Ejemplo: Registros de correo electrónico duplicados (usando EXISTS)

SELECT *
FROM users u1
WHERE EXISTS (
  SELECT 1
  FROM users u2
  WHERE u1.email = u2.email
  GROUP BY u2.email
  HAVING COUNT(*) > 1
);

3-3. Notas y consideraciones de rendimiento

• El rendimiento de la subconsulta puede verse significativamente afectado cuando el conjunto de datos es grande. Con un indexado adecuado, tanto IN como EXISTS pueden funcionar a un nivel práctico.
• Sin embargo, si necesita condiciones complejas o desea determinar duplicados a través de múltiples columnas, las consultas pueden volverse pesadas. Siempre valide el comportamiento en un entorno de pruebas primero.

De esta manera, extraer todos los registros que coinciden con claves duplicadas se puede lograr usando subconsultas o la cláusula EXISTS.

4. Detección de duplicados en múltiples columnas

Las condiciones de detección de duplicados no siempre se basan en una sola columna. En la práctica, es común requerir unicidad a través de una combinación de varias columnas. Por ejemplo, puede considerar registros como duplicados cuando coinciden “nombre completo + fecha de nacimiento”, o cuando “ID de producto + color + talla” son idénticos.

En esta sección, explicamos en detalle cómo extraer duplicados usando múltiples columnas.

4-1. Detección de duplicados con GROUP BY usando múltiples columnas

Para detectar duplicados en múltiples columnas, enumere las columnas separadas por comas en la cláusula GROUP BY. Con HAVING COUNT(*) > 1, puede extraer solo las combinaciones que aparecen dos o más veces.

SELECT col1, col2, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY col1, col2
HAVING COUNT(*) > 1;

Ejemplo: Detección de duplicados por “first_name” y “birthday”

SELECT first_name, birthday, COUNT(*) AS count
FROM users
GROUP BY first_name, birthday
HAVING COUNT(*) > 1;

Esta consulta le ayuda a identificar casos donde la combinación de “mismo nombre” y “misma fecha de nacimiento” se ha registrado varias veces.

4-2. Extracción de todos los registros para claves duplicadas de múltiples columnas

Si necesita todos los detalles de los registros para combinaciones de claves duplicadas, puede extraer los pares duplicados en una subconsulta y luego recuperar todas las filas que coincidan con esos pares.

SELECT *
FROM table_name t1
WHERE (col1, col2) IN (
  SELECT col1, col2
  FROM table_name
  GROUP BY col1, col2
  HAVING COUNT(*) > 1
);

Ejemplo: Registros completos para duplicados en “first_name” y “birthday”

SELECT *
FROM users u1
WHERE (first_name, birthday) IN (
  SELECT first_name, birthday
  FROM users
  GROUP BY first_name, birthday
  HAVING COUNT(*) > 1
);

Usando esta consulta, por ejemplo, si la combinación “Taro Tanaka / 1990-01-01” está registrada varias veces, puede recuperar todas las filas detalladas relacionadas.

4-3. Detección de duplicados exactos (COUNT DISTINCT)

Si deseas estimar “cuántas filas son duplicados exactos en varias columnas”, también puedes usar agregación con COUNT(DISTINCT ...).

SELECT COUNT(*) - COUNT(DISTINCT col1, col2) AS duplicate_count
FROM table_name;

Este SQL proporciona un recuento aproximado de filas totalmente duplicadas dentro de la tabla.

4-4. Notas

• Incluso para la detección de duplicados en múltiples columnas, un indexado adecuado puede mejorar significativamente la velocidad de la consulta.
• Si hay muchas columnas involucradas o existen valores NULL, podrías obtener resultados duplicados inesperados. Diseña tus condiciones con cuidado.

De esta manera, detectar y extraer duplicados en múltiples columnas puede manejarse de forma flexible con SQL bien diseñado.

5. Eliminación de Registros Duplicados (DELETE)

Una vez que puedes extraer datos duplicados, el siguiente paso es eliminar los duplicados innecesarios. En la práctica, un enfoque común es mantener solo un registro entre los duplicados y eliminar el resto. Sin embargo, al eliminar duplicados automáticamente en MySQL, debes delimitar cuidadosamente el objetivo de eliminación para evitar pérdida de datos no intencionada.

En esta sección, explicamos métodos seguros comunes para eliminar datos duplicados y precauciones clave.

5-1. Eliminación de Duplicados con una Subconsulta + DELETE

Si deseas mantener solo el registro “más antiguo” o “más reciente” y eliminar los demás, una sentencia DELETE con una subconsulta puede ser útil.

Ejemplo: Mantener el registro con el ID más pequeño (más antiguo) y eliminar los demás

DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id)
  FROM users
  GROUP BY email
);

Esta consulta mantiene solo el id más pequeño (el primer registro registrado) para cada correo electrónico, y elimina todas las demás filas que comparten el mismo correo.

5-2. Cómo Evitar el Error Específico de MySQL (Error 1093)

En MySQL, puedes encontrarte con el Error 1093 cuando intentas DELETE de una tabla mientras también haces referencia a la misma tabla en una subconsulta. En ese caso, puedes evitar el error envolviendo el resultado de la subconsulta como una tabla derivada (conjunto de resultados temporal).

DELETE FROM users
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(id)
    FROM users
    GROUP BY email
  ) AS temp_ids
);

Al envolver la subconsulta con SELECT * FROM (...) AS alias, puedes prevenir el error y eliminar de forma segura.

5-3. Eliminación de Duplicados para Claves de Múltiples Columnas

Si deseas eliminar duplicados basados en una combinación de varias columnas, usa GROUP BY con múltiples columnas y elimina todo excepto el registro representativo.

Ejemplo: Para duplicados por “first_name” y “birthday”, elimina todo excepto el primer registro

DELETE FROM users
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(id)
    FROM users
    GROUP BY first_name, birthday
  ) AS temp_ids
);

5-4. Medidas de Seguridad y Mejores Prácticas para la Eliminación

Eliminar duplicados es una operación de alto riesgo que puede eliminar datos de forma permanente. Asegúrate de seguir estas mejores prácticas:

• Realiza copias de seguridad : Siempre guarda una copia de seguridad de toda la tabla o de los registros objetivo antes de eliminar.
• Utiliza transacciones : Si es posible, envuelve la operación en una transacción para que puedas revertirla inmediatamente si algo falla.
• Confirma los conteos con SELECT primero : Crea el hábito de verificar “¿El objetivo de eliminación es correcto?” ejecutando primero una consulta SELECT.
• Revisa los índices : Añadir índices a las columnas usadas para la detección de duplicados mejora tanto el rendimiento como la precisión.

En MySQL, puedes eliminar de forma segura datos duplicados usando subconsultas y tablas derivadas. Siempre procede con cautela, con pruebas suficientes y una estrategia de respaldo sólida.

6. Consideraciones de Rendimiento y Estrategia de Índices

Al extraer o eliminar datos duplicados en MySQL, el tiempo de ejecución de las consultas y la carga del servidor se vuelven más problemáticos a medida que crece la tabla. Especialmente en sistemas a gran escala o trabajos por lotes, el diseño SQL consciente del rendimiento y la optimización de índices son esenciales. En esta sección, explicamos consejos para mejorar el rendimiento y puntos clave para el diseño de índices en el procesamiento de datos duplicados.

6-1. Elegir entre EXISTS, IN y JOIN

Constructos SQL como IN, EXISTS y JOIN se utilizan comúnmente para extraer datos duplicados, pero cada uno tiene características diferentes y tendencias de rendimiento.

• IN – Rápido cuando el conjunto de resultados de la subconsulta es pequeño, pero el rendimiento tiende a degradarse a medida que crece el conjunto de resultados.
• EXISTS – Detiene la búsqueda tan pronto como se encuentra un registro coincidente, por lo que a menudo es efectivo para tablas grandes o cuando las coincidencias son relativamente raras.
• JOIN – Útil para recuperar muchas piezas de información a la vez, pero puede volverse más lento si se unen datos innecesarios o falta un indexado adecuado.

Comparación de rendimiento de ejemplo

Es importante elegir la sintaxis óptima según su sistema real y el volumen de datos.

6-2. Por qué importa el diseño de índices

Para las columnas utilizadas en verificaciones de duplicados o filtros de eliminación, siempre cree índices. Sin índices, pueden ocurrir escaneos completos de la tabla y el rendimiento puede volverse extremadamente lento.

Ejemplo: Crear un índice

CREATE INDEX idx_email ON users(email);

Si detecta duplicados en múltiples columnas, un índice compuesto también es efectivo.

CREATE INDEX idx_name_birthday ON users(first_name, birthday);

El diseño de índices puede cambiar drásticamente el rendimiento de lectura y la eficiencia de búsqueda.
Nota: Agregar demasiados índices puede ralentizar las escrituras y aumentar el uso de almacenamiento, por lo que el equilibrio es importante.

6-3. Procesamiento por lotes para conjuntos de datos grandes

• Si el conjunto de datos está en el orden de decenas de miles a millones de filas, es más seguro ejecutar el procesamiento en lotes más pequeños en lugar de manejarlo todo de una vez.
• Para eliminaciones y actualizaciones, limite el número de filas procesadas por ejecución (por ejemplo, LIMIT 1000 ) y ejecútelo varias veces para reducir la contención de bloqueos y la degradación del rendimiento. DELETE FROM users WHERE id IN ( -- The first 1000 duplicate record IDs extracted by a subquery ) LIMIT 1000;

6-4. Uso de planes de ejecución (EXPLAIN)

Use EXPLAIN para analizar cómo se ejecuta una consulta. Esto le ayuda a verificar si los índices se están utilizando de manera efectiva y si está ocurriendo un escaneo completo (ALL).

EXPLAIN SELECT * FROM users WHERE email IN (...);

Al tener en cuenta el rendimiento y la estrategia de índices, puede manejar el procesamiento de duplicados de manera segura y eficiente incluso para conjuntos de datos grandes.

7. Casos de uso avanzados: Manejo de escenarios complejos

En entornos del mundo real, la detección y eliminación de duplicados a menudo son más complejas que una coincidencia simple. Puede necesitar agregar condiciones adicionales, ejecutar operaciones de manera segura en etapas o cumplir con requisitos operativos más estrictos. En esta sección, introducimos técnicas prácticas avanzadas para manejar datos duplicados de manera segura y flexible.

7-1. Eliminación condicional de duplicados

Si desea eliminar solo duplicados que cumplan con condiciones específicas, use la cláusula WHERE de manera estratégica.

Ejemplo: Eliminar solo registros duplicados con el mismo correo electrónico y status = 'withdrawn'

DELETE FROM users
WHERE id NOT IN (
  SELECT * FROM (
    SELECT MIN(id)
    FROM users
    WHERE status = 'withdrawn'
    GROUP BY email
  ) AS temp_ids
)
AND status = 'withdrawn';

Al agregar condiciones a WHERE y GROUP BY, puede controlar con precisión qué registros mantener y cuáles eliminar.

7-2. Recomendado: Procesamiento por lotes y ejecución dividida

Si el conjunto de datos es muy grande o desea evitar la contención de bloqueos y la degradación del rendimiento, use procesamiento por lotes.

• No procese todos los objetivos de eliminación a la vez—use LIMIT para una ejecución dividida
• Utilice control de transacciones y haga rollback en caso de errores inesperados
• Gestione el riesgo con copias de seguridad y registro DELETE FROM users WHERE id IN ( SELECT id FROM ( -- Extract duplicate record IDs filtered by conditions ) AS temp_ids ) LIMIT 500;

Este enfoque reduce significativamente la carga del sistema.

7-3. Manejo de definiciones complejas de duplicados

En diferentes contextos empresariales, la definición de “duplicado” varía. Puede combinar subconsultas, expresiones CASE y funciones de agregación para un manejo flexible.

Ejemplo: Considere duplicados solo cuando product_id, order_date y price sean idénticos

SELECT product_id, order_date, price, COUNT(*)
FROM orders
GROUP BY product_id, order_date, price
HAVING COUNT(*) > 1;

Para requisitos más avanzados, como “conservar solo el registro más reciente entre los duplicados”, puede usar subconsultas o ROW_NUMBER() (disponible en MySQL 8.0 y versiones posteriores).

7-4. Mejores prácticas para transacciones y copias de seguridad

• Siempre envuelva las operaciones DELETE o UPDATE en transacciones para que pueda restaurar los datos con ROLLBACK si ocurren problemas.
• Si trabaja con tablas importantes o conjuntos de datos grandes, siempre cree una copia de seguridad previamente .

Al dominar estas técnicas avanzadas, podrá manejar el procesamiento de datos duplicados de forma segura y flexible en cualquier entorno.

8. Resumen

En este artículo, explicamos sistemáticamente cómo extraer y eliminar datos duplicados en MySQL, desde los fundamentos hasta aplicaciones avanzadas. Repasemos los puntos clave.

8-1. Principales conclusiones

• Detección de datos duplicados Puede detectar duplicados no solo en una sola columna, sino también en múltiples columnas. La combinación de GROUP BY y HAVING COUNT(*) > 1 es el patrón fundamental para la detección de duplicados.
• Extracción de todos los registros duplicados Utilizando subconsultas y la cláusula EXISTS, puede obtener todos los registros que corresponden a valores de clave duplicados.
• Eliminación de registros duplicados Mediante el uso de MIN(id) o MAX(id) para conservar filas representativas y combinando subconsultas con sentencias DELETE, puede eliminar de forma segura los duplicados innecesarios. Evitar el error MySQL 1093 también es importante.
• Rendimiento e indexación Para conjuntos de datos grandes o condiciones complejas, una indexación adecuada, el procesamiento por lotes y la verificación de planes de ejecución mediante EXPLAIN son esenciales.
• Técnicas prácticas La eliminación condicional, la ejecución dividida, la gestión de transacciones y las copias de seguridad son prácticas clave para evitar errores en entornos de producción.

8-2. Referencia rápida por caso de uso

8-3. Prevención de futuros problemas de duplicados

Prevenir duplicados en el momento de la inserción es igualmente importante.

• Considere usar restricciones UNIQUE durante el diseño de la tabla.
• La limpieza regular de datos y la auditoría ayudan a detectar problemas operativos temprano.

Extraer y eliminar datos duplicados en MySQL requiere conocimientos que van desde SQL básico hasta técnicas avanzadas. Esperamos que esta guía respalde su mantenimiento de bases de datos y operaciones del sistema.
Si tiene casos específicos o más preguntas, considere consultar las FAQ o acudir a un especialista en bases de datos.

9. FAQ: Preguntas frecuentes sobre la extracción y eliminación de datos duplicados en MySQL

Q1. ¿Por qué usar GROUP BY + HAVING en lugar de DISTINCT?

DISTINCT elimina duplicados en el conjunto de resultados, pero no puede indicar cuántas veces aparece un valor. Al combinar GROUP BY y HAVING COUNT(*) > 1, puede determinar qué valores aparecen varias veces y cuántos duplicados existen.

Q2. ¿Debería usar IN o EXISTS?

Para conjuntos de datos pequeños, la diferencia es mínima. Para tablas grandes o cuando los índices son efectivos, EXISTS suele tener mejor rendimiento. Pruebe ambos enfoques en su entorno y verifique los planes de ejecución usando EXPLAIN.

Q3. ¿Cómo detecto duplicados en múltiples columnas?

Especifique varias columnas en GROUP BY y use HAVING COUNT(*) > 1 para detectar combinaciones donde todas las columnas especificadas coincidan. Ejemplo: GROUP BY first_name, birthday

Q4. Obtengo el Error 1093 al ejecutar DELETE. ¿Qué debo hacer?

MySQL genera el Error 1093 cuando se hace referencia a la misma tabla en una subconsulta dentro de una sentencia DELETE. Envuelva el resultado de la subconsulta en una tabla derivada usando SELECT * FROM (...) AS alias para evitar el error.

Q5. ¿Cómo puedo eliminar datos duplicados de forma segura?

Siempre cree una copia de seguridad antes de eliminar, verifique los objetivos con una sentencia SELECT y use transacciones cuando sea posible. La eliminación por lotes también puede ser más segura para conjuntos de datos grandes.

Q6. ¿Qué debo hacer si las consultas son lentas con grandes volúmenes de datos?

Cree índices en las columnas utilizadas para la detección de duplicados. Use procesamiento por lotes con LIMIT y revise los planes de ejecución mediante EXPLAIN para evitar escaneos completos de tabla innecesarios.

Q7. ¿Cómo puedo prevenir fundamentalmente inserciones duplicadas?

Defina restricciones UNIQUE o claves únicas durante el diseño de la tabla para evitar que se inserten valores duplicados. Además, realice verificaciones periódicas de duplicados y limpieza de datos después del despliegue.

Q8. ¿Se pueden usar los mismos métodos en MariaDB u otros SGBD?

Los constructos básicos de SQL como GROUP BY, HAVING y subconsultas también son compatibles en MariaDB y PostgreSQL. Sin embargo, las restricciones de subconsultas en DELETE y las características de rendimiento pueden variar según el producto, por lo que siempre se debe probar con anticipación.