Extraer y Eliminar Duplicados en MySQL: Guía Práctica de SQL desde lo Básico hasta lo Avanzado

1. Introducción

Al operar una base de datos, no es raro encontrarse con problemas como “los mismos datos se registran varias veces” o “información que debería ser única se duplica”. En entornos que utilizan bases de datos relacionales como MySQL, extraer y gestionar datos duplicados es una tarea esencial para mantener la precisión y calidad de los datos.

Por ejemplo, en tablas centrales de sistemas empresariales como la información de miembros, datos de productos e historial de pedidos, existe el riesgo de que los mismos datos se registren dos veces debido a errores de operación del usuario o fallos del sistema. Si se deja así, puede provocar una menor precisión en la agregación y el análisis, o convertirse en la causa de errores y problemas inesperados.

Para resolver estos problemas de “datos duplicados”, primero extraiga qué registros están duplicados, y luego organice y elimine los registros duplicados según corresponda. Sin embargo, con solo sentencias SELECT estándar en MySQL, a menudo es difícil encontrar de manera eficiente los “datos duplicados”, lo que requiere técnicas de escritura SQL más avanzadas y consejos prácticos.

En este artículo, tomando como tema “Métodos para extraer datos duplicados en MySQL”, explicaremos en detalle desde sentencias SQL básicas hasta ejemplos de aplicación práctica, consideraciones de rendimiento y manejo de errores comunes. Nuestro objetivo es proporcionar información lo más práctica posible, útil no solo para principiantes en bases de datos, sino también para ingenieros que escriben SQL a diario en el trabajo.

2. Fundamentos: Detectar duplicados usando solo columnas clave

El método más básico para extraer datos duplicados en MySQL es identificar los casos en los que varios registros comparten el mismo valor en columnas específicas (columnas clave). Esta sección explica sentencias SQL representativas para extraer valores clave con duplicados, junto con ejemplos de ejecución.

2-1. Extracción de duplicados usando GROUP BY y cláusulas HAVING

Los fundamentos de la detección de duplicados implican agrupar por las columnas especificadas en la cláusula GROUP BY y luego filtrar con la cláusula HAVING para los grupos que contienen 2 o más registros. A continuación se muestra 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 duplicados en direcciones de correo electrónico de miembros

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

Al ejecutar esta consulta se mostrará una lista de esas direcciones de correo electrónico y el recuento de duplicados (count) para los casos en que la misma dirección de correo electrónico se registre varias veces.

2-2. Extracción de duplicados para múltiples columnas usando GROUP BY

Si desea detectar duplicados basados en combinaciones de dos o más columnas, puede especificar múltiples columnas en GROUP BY usando el mismo enfoque.

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

Con este método, puede detectar datos duplicados donde múltiples condiciones coinciden exactamente, como “mismo nombre y fecha de nacimiento” o “mismo ID de producto y fecha de pedido”.

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

Si desea conocer la imagen completa de cuántos duplicados existen, puede usar una subconsulta para calcular el número total de duplicados.

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 los números “excesos” para cada grupo duplicado.

De esta manera, combinando GROUP BY y HAVING, puede extraer datos duplicados de forma simple y eficiente en MySQL.

3. Extraer todos los registros con claves duplicadas

En el capítulo anterior, presentamos un método para listar solo los “valores de clave duplicados”. Sin embargo, en escenarios del mundo real, es común querer “examinar todo el contenido de los registros que están duplicados”. Por ejemplo, para examinar los detalles de la información de usuarios registrados como duplicados o el contenido de los datos de productos de forma individual.

En este capítulo, explicaremos patrones prácticos de SQL para extraer todos los registros con claves duplicadas.

3-1. Extracción de Registros Duplicados Usando Subconsultas

El método más básico es obtener una lista de valores de claves duplicadas mediante una subconsulta y luego recuperar todos los registros coincidentes.

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 Duplicadas

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

La ejecución de esta consulta extraerá todas las filas de la tabla “users” donde las direcciones de correo están duplicadas (incluidos ID, fecha de registro, etc.).

3-2. Extracción Eficiente Usando la Cláusula EXISTS

Para conjuntos de datos grandes o cuando se centra en el rendimiento, también es eficaz usar la cláusula EXISTS. IN y EXISTS son similares, pero dependiendo del volumen de datos y las condiciones de índice, una puede funcionar más rápido en ciertos casos.

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 Duplicados (Cláusula 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 Rendimiento

• El rendimiento de las subconsultas puede verse significativamente afectado con grandes cantidades de datos. Si los índices están configurados correctamente, tanto IN como EXISTS funcionarán a un nivel práctico.
• Sin embargo, al determinar duplicados con condiciones complejas o múltiples columnas, el SQL puede volverse pesado, así que asegúrese de verificar el funcionamiento en un entorno de prueba antes.

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 con Múltiples Columnas

Las condiciones de determinación de datos duplicados no están necesariamente limitadas a una sola columna. En el trabajo práctico, existen escenarios frecuentes donde se desea garantizar la unicidad combinando múltiples columnas. Por ejemplo, casos donde se consideran duplicados cuando varios elementos como “nombre + fecha de nacimiento” o “ID de producto + color + tamaño” son todos iguales.

En este capítulo, explicaremos en detalle cómo extraer duplicados usando múltiples columnas.

4-1. Especificar Múltiples Columnas en GROUP BY para la Detección de Duplicados

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

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

Ejemplo: Extracció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;

Con esta consulta, puede identificar casos donde las combinaciones de mismo nombre y misma fecha de nacimiento están registradas múltiples veces.

4-2. Extracción de Todos los Registros Duplicados para Múltiples Columnas

Si necesita todos los detalles de registro que tienen combinaciones de claves duplicadas, use una subconsulta para extraer los pares duplicados y luego extraiga todas las filas que coincidan con esas combinaciones.

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

Ejemplo: Todos los Registros con 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
);

Con esta consulta, por ejemplo, si la combinación “Tarō Tanaka, 1990-01-01” está registrada múltiples veces, se pueden obtener todos los datos detallados.

4-3. Determinación de duplicados para coincidencias exactas (COUNT DISTINCT)

Si deseas saber cuántas filas tienen todas las columnas múltiples con exactamente los mismos valores, también puedes agregar usando COUNT(DISTINCT ...).

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

Esta consulta SQL proporciona una estimación del número de duplicados completos en la tabla.

4-4. Precauciones

• Incluso al detectar duplicados con múltiples columnas, configurar correctamente los índices puede mejorar significativamente la velocidad de búsqueda .
• Cuando hay muchas columnas o se incluyen NULLs, pueden ocurrir extracciones de duplicados no deseados, por lo que diseña las condiciones con cuidado.

De esta manera, la detección y extracción de duplicados con múltiples columnas también puede manejarse de forma flexible según las técnicas SQL.

5. Eliminación de registros duplicados (borrado)

Una vez que puedes extraer datos duplicados, el siguiente paso necesario es «eliminar los registros duplicados innecesarios». En la práctica, el proceso de mantener solo «un registro de los duplicados y eliminar el resto» se realiza comúnmente. Sin embargo, al eliminar duplicados automáticamente en MySQL, para prevenir la pérdida de datos no deseada, es necesario delimitar claramente los objetivos de eliminación.

Este capítulo explica en detalle los métodos representativos para eliminar datos duplicados de forma segura y las precauciones a tomar durante el proceso.

5-1. Eliminación de duplicados usando subconsultas + DELETE

Para conservar solo el «más antiguo» o el «más reciente» entre los registros duplicados y eliminar los demás, una sentencia DELETE que utilice una subconsulta es útil.

Ejemplo: Conservar solo el registro con el ID más pequeño (el más antiguo) y eliminar el resto

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

Esta consulta conserva solo el ID más pequeño para cada «email» (el registro registrado primero) y elimina todas las demás filas con el mismo email.

5-2. Evitar errores específicos de MySQL (Error 1093)

En MySQL, referenciar la misma tabla dentro de una subconsulta durante DELETE puede provocar el Error 1093. En tales casos, puedes evitarlo envolviendo el resultado de la subconsulta como una tabla temporal (tabla derivada).

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

Al envolver la subconsulta adicionalmente con SELECT * FROM (...) AS alias de esta manera, puedes evitar el error y realizar la eliminación de forma segura.

5-3. Eliminación de duplicados para múltiples columnas

Cuando deseas eliminar duplicados basados en una combinación de múltiples columnas, utiliza de manera similar GROUP BY en varias columnas y elimina los demás registros.

Ejemplo: Eliminar todos excepto el primer registro que se duplica en «first_name» y «birthday»

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

La eliminación de duplicados es una operación de alto riesgo que puede provocar pérdida de datos. Asegúrate de prestar atención a los siguientes puntos.

• Haz una copia de seguridad: Siempre guarda una copia de seguridad de toda la tabla o de los registros objetivo antes de la eliminación.
• Utiliza transacciones: Si es posible, utiliza las funciones de transacción para permitir un rollback inmediato de las «eliminaciones incorrectas».
• Confirma primero el número de objetivos con SELECT: Hazte el hábito de extraer y verificar con una sentencia SELECT antes de comprobar si «los objetivos de eliminación son los esperados».
• Revisa los índices: Añadir índices a las columnas utilizadas para la detección de duplicados mejora el rendimiento y la precisión.

En MySQL, puedes eliminar datos duplicados de forma segura utilizando subconsultas o tablas temporales. Realiza las operaciones de eliminación con cautela, con una verificación exhaustiva y un sistema de respaldo sólido en su lugar.

6. Consideraciones de rendimiento y estrategias de índices

Al extraer o eliminar datos duplicados en MySQL, cuanto mayor sea el volumen de datos de la tabla, más se vuelven problemáticos la velocidad de ejecución de las consultas y la carga del servidor. En particular, en sistemas a gran escala o en procesamiento por lotes, es importante diseñar SQL con conciencia de rendimiento y optimizar los índices. En este capítulo explicaremos consejos para mejorar el rendimiento y puntos clave para el diseño de índices en el procesamiento de datos duplicados.

6-1. Uso apropiado de EXISTS, IN y JOIN

La sintaxis SQL como IN, EXISTS y JOIN, utilizada al extraer datos duplicados, tiene cada una características y tendencias de rendimiento distintas.

• IN Es rápido cuando el conjunto de resultados de la subconsulta es pequeño, pero el rendimiento tiende a degradarse a medida que aumentan los resultados.
• EXISTS Detiene la búsqueda tan pronto como se encuentra un registro que cumple la condición, lo que lo hace eficaz para tablas grandes o cuando el número de coincidencias es pequeño.
• JOIN Al unir, puedes obtener mucha información de una vez, pero unir datos innecesarios puede ralentizar realmente el proceso.

Ejemplo de comparación de rendimiento

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

6-2. Importancia del diseño de índices

Para las columnas utilizadas en verificaciones de duplicados o como objetivos de eliminación, siempre establece índices. Sin índices, se produce un escaneo completo de la tabla, lo que lo hace extremadamente lento.

Ejemplo de configuración de índices

CREATE INDEX idx_email ON users(email);

Incluso al determinar duplicados con múltiples columnas, los índices compuestos son efectivos.

CREATE INDEX idx_name_birthday ON users(first_name, birthday);

Dependiendo del diseño del índice, la velocidad de lectura y la eficiencia de búsqueda pueden cambiar significativamente.
Sin embargo, crear demasiados índices puede provocar degradación de la velocidad de escritura y aumento del almacenamiento, por lo que el equilibrio es importante.

6-3. Procesamiento por lotes para grandes volúmenes de datos

• Cuando el volumen de datos está en la escala de decenas de miles a millones de registros, en lugar de procesar todo de una vez, es más seguro dividir en lotes y ejecutar.
• Para eliminaciones o actualizaciones, limita el número de registros procesados a la vez (por ejemplo, LIMIT 1000) y ejecuta en varias rondas para evitar contención de bloqueos y degradación del rendimiento.

DELETE FROM users
WHERE id IN (
  -- First 1000 records among the duplicate records extracted by the subquery
)
LIMIT 1000;

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

Utiliza EXPLAIN, que te permite verificar el plan de ejecución SQL, para analizar cómo se está ejecutando la consulta real. Puedes comprobar de antemano si los índices se están utilizando eficazmente y si se están produciendo escaneos completos de la tabla (ALL).

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

De esta manera, al tener en cuenta las estrategias de rendimiento e índices, el procesamiento de duplicados de grandes volúmenes de datos también puede realizarse de forma segura y eficiente.

7. Ejemplos avanzados: manejo de casos complejos

En entornos empresariales reales, más allá de la detección y eliminación de duplicados simples, las necesidades avanzadas como «añadir condiciones más complejas» o «querer ejecutar de forma segura en lotes» no son poco comunes. Este capítulo presenta ejemplos prácticos aplicados, incluyendo procesamiento de duplicados con múltiples condiciones y técnicas operativas que enfatizan la seguridad y la fiabilidad.

7-1. Eliminación de duplicados con múltiples condiciones

Cuando deseas eliminar solo aquellos registros duplicados que cumplan condiciones específicas, utiliza eficazmente la cláusula WHERE.

Ejemplo: Eliminar solo duplicados con la misma dirección de correo electrónico y estado «retirado»

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 o GROUP BY de esta manera, puedes reducir los registros que deseas conservar o eliminar.

7-2. Recomendaciones para procesamiento por lotes y ejecución dividida

If the data volume is huge or you want to avoid lock contention and performance degradation, make use of batch processing.

• Instead of processing all deletion targets at once, use LIMIT for split execution
• Use transaction control to rollback in case of any errors
• Risk management through backups and log output

DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    -- Extract duplicate record IDs filtered by conditions
  ) AS temp_ids
)
LIMIT 500;

Al dividir de esta manera, puedes reducir significativamente la carga en el sistema.

7-3. Manejo de Definiciones Complejas de Duplicados

Cuando las condiciones consideradas como «duplicados» difieren según el negocio, responde de manera flexible combinando subconsultas, sentencias CASE y funciones agregadas.

Ejemplo: Considerar como duplicados solo cuando «el mismo ID de producto, la misma fecha de pedido y el mismo precio»

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

Además, juicios complejos como «mantener solo el más reciente entre los duplicados» pueden lograrse usando subconsultas o ROW_NUMBER() (MySQL 8.0 y posteriores).

7-4. Mejores Prácticas para Transacciones y Copias de Seguridad

• Siempre envuelve las eliminaciones o actualizaciones en transacciones para que, si ocurre un problema a mitad de camino, puedas revertir con ROLLBACK .
• Para tablas importantes o cuando manejas muchos registros, siempre realiza una copia de seguridad con antelación. Es seguro realizar el trabajo de eliminación después de la copia de seguridad.

Al dominar estas técnicas aplicadas y avanzadas, puedes manejar el procesamiento de datos duplicados de manera flexible y segura en cualquier entorno.

8. Resumen

En este artículo, hemos explicado de manera sistemática la extracción y eliminación de datos duplicados usando MySQL, desde lo básico hasta temas avanzados. Aquí, revisemos nuevamente los puntos principales.

8-1. Revisión de los Puntos Clave del Artículo

• Detectar Datos Duplicados Es posible extraer duplicados no solo de una columna, sino también combinando múltiples columnas. La combinación de GROUP BY y HAVING COUNT(*) > 1 forma el patrón básico para toda detección de duplicados.
• Extraer Todos los Registros Duplicados Utilizando subconsultas o la cláusula EXISTS, puedes recuperar todos los registros correspondientes a valores de clave duplicados.
• Eliminar Registros Duplicados Manteniendo valores representativos con MIN(id) o MAX(id), al aplicar subconsultas + sentencias DELETE, puedes eliminar de manera segura solo los duplicados innecesarios. Evitar el error 1093 de MySQL también es un punto importante.
• Rendimiento e Índices Para procesar datos a gran escala o condiciones complejas, la configuración adecuada de índices, el procesamiento por lotes y la verificación del plan de ejecución con EXPLAIN son esenciales.
• Técnicas de Aplicación Práctica También debemos tener en cuenta puntos para evitar fallos en la práctica, como la eliminación condicional, la ejecución dividida, la gestión de transacciones y la adquisición de copias de seguridad.

8-2. Tabla de Referencia Rápida de Enfoques Recomendados por Caso de Uso

8-3. Para Prevenir Problemas Futuros

Prevenir datos duplicados «en el momento de la registración» también es importante.

• Durante el diseño de la tabla, considera utilizar restricciones únicas (UNIQUE).
• La limpieza y auditoría regulares de los datos existentes también conducen a la detección temprana de problemas operativos.

La extracción y eliminación de datos duplicados basada en MySQL es un campo que requiere un amplio rango de conocimientos y habilidades prácticas, desde los fundamentos de SQL hasta aplicaciones avanzadas. Espero que el contenido de este artículo resulte útil para el mantenimiento de datos en sitio y las operaciones del sistema.
Si tienes alguna pregunta o caso específico, consulta la FAQ o a los expertos.

9. Preguntas Frecuentes: Preguntas Comunes sobre la Extracción y Eliminación de Datos Duplicados en MySQL

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

DISTINCT es una característica para “eliminar” duplicados, pero no permite investigar “qué valores se duplican cuántas veces”. Por otro lado, combinando GROUP BY y HAVING COUNT(*) > 1, puedes extraer información detallada como “qué valores aparecen varias veces” y “cuántos duplicados hay”.

Q2. ¿Cuál debería usar, IN o EXISTS?

Para cantidades pequeñas de datos, no hay diferencia entre ambos, pero cuando las tablas son grandes o los índices son efectivos, EXISTS suele ser más rápido. Se recomienda probar ambos en tu entorno real y revisar el plan de ejecución con EXPLAIN.

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

Especificando varias columnas en GROUP BY y usando HAVING COUNT(*) > 1, puedes detectar duplicados donde “todas las columnas tienen la misma combinación”. Ejemplo: GROUP BY first_name, birthday

Q4. ¿El error 1093 ocurre en la sentencia DELETE? ¿Cómo lo manejo?

MySQL devuelve el error 1093 cuando “se referencia la misma tabla en una subconsulta dentro de una sentencia DELETE”. Para evitarlo, envuelve el resultado de la subconsulta en una tabla derivada (SELECT * FROM (...) AS alias).

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

Siempre haz una copia de seguridad antes de eliminar, y primero confirma los objetivos con una sentencia SELECT. Si es posible, utiliza transacciones y realiza eliminaciones incrementales o procesamiento por lotes según sea necesario.

Q6. ¿Cuáles son las medidas cuando las consultas son lentas debido a grandes volúmenes de datos?

Establece índices en las columnas utilizadas para la detección de duplicados, y para volúmenes de procesamiento grandes, ejecuta en lotes usando LIMIT. Revisa el plan de ejecución con EXPLAIN y asegúrate de que no se realicen escaneos completos innecesarios.

Q7. ¿Cómo puedo prevenir fundamentalmente las registraciones duplicadas?

En el momento del diseño de la tabla, establece restricciones UNIQUE o claves únicas para prevenir registraciones duplicadas de los mismos valores de antemano. Una vez que las operaciones comienzan, es bueno realizar periódicamente verificaciones de duplicados y limpieza de datos.

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

La sintaxis SQL básica (GROUP BY · HAVING · subconsultas) se puede usar comúnmente en otros RDBMS como MariaDB y PostgreSQL. Sin embargo, existen diferencias por producto en las restricciones de subconsultas durante DELETE y en las características de rendimiento, por lo que siempre es recomendable verificar con antelación.