Cómo eliminar filas duplicadas en MySQL

Introducción

Hay varios casos en los que puede encontrar filas duplicadas en su base de datos MySQL. Esta guía lo guiará a través del proceso de eliminación de valores de fila duplicados en MySQL.

Requisitos previos

• Un sistema con MySQL instalado
• Una cuenta de usuario root de MySQL
• Acceso a una ventana de terminal/línea de comandos (Ctrl-Alt-T, Buscar> Terminal)

Configuración de la base de datos de prueba

Si ya tiene una base de datos MySQL para trabajar, pase a la siguiente sección.

De lo contrario, abra una ventana de terminal y escriba lo siguiente:

mysql –u root –p

Cuando se le solicite, ingrese la raíz contraseña para su instalación de MySQL. Si tiene una cuenta de usuario específica, use esas credenciales en lugar de root.

El aviso del sistema debería cambiar a:

mysql>

Crear base de datos de prueba

Puede crear una nueva tabla en una base de datos existente. Para hacerlo, busque la base de datos adecuada enumerando todas las instancias existentes con:

SHOW DATABASES;

Alternativamente, puede crear una nueva base de datos ingresando el siguiente comando:

CREATE DATABASE IF NOT EXISTS testdata;

Para comenzar a trabajar en sus nuevos testdata uso de la base de datos:

USE testdata;

Añadir tabla y datos

Una vez en la base de datos, agregue una tabla con los datos a continuación usando el siguiente comando:

CREATE TABLE dates (
id INT PRIMARY KEY AUTO_INCREMENT,
day VARCHAR(2) NOT NULL,
month VARCHAR(10) NOT NULL,
year VARCHAR(4) NOT NULL

);

INSERT INTO dates (day,month,year)
VALUES (’29’,’January’,’2011’),
(’30’,’January’,’2011’),
(’30’,’January’,’2011’),
(’14’,’February,’2017’),
(’14’,’February,’2018’),
(‘23’,’March’,’2018’),
(‘23’,’March’,’2018’),
(‘23’,’March’,’2019’),
(‘29’,’October’,’2019’),
(‘29’,’November’,’2019’),
(‘12’,’November’,’2017’),
(‘17’,’August’,’2018’),
(‘05’,’June’,’2016’);

Mostrar el contenido de la tabla de fechas

Para ver una visualización de todas las fechas que ingresó, ordenadas por año, escriba:

SELECT * FROM dates ORDER BY year;

La salida debe mostrar una lista de fechas en el orden apropiado.

Mostrar filas duplicadas

Para averiguar si hay filas duplicadas en la base de datos de prueba, use el comando:

SELECT
     day, COUNT(day),
     month, COUNT(month),
     year, COUNT(year)
FROM 
     dates
GROUP BY
     day,
     month,
     year
HAVING 
     COUNT(day) > 1
     AND COUNT(month) > 1
     AND COUNT(year) > 1;

El sistema mostrará cualquier valor que esté duplicado. En este caso, debería ver:

Este formato funciona para seleccionar varias columnas. Si tiene una columna con un identificador único, como una dirección de correo electrónico en una lista de contactos o una sola columna de fecha, simplemente puede seleccionar de esa columna.

Eliminar filas duplicadas

Antes de utilizar cualquiera de los métodos mencionados a continuación, recuerde que debe trabajar en una base de datos existente. Usaremos nuestra base de datos de muestra:

USE testdata;

Opción 1:eliminar filas duplicadas mediante INNER JOIN

Para eliminar filas duplicadas en nuestra tabla MySQL de prueba, use MySQL JOINS e ingrese lo siguiente:

delete t1 FROM dates t1
INNER  JOIN dates t2
WHERE
    t1.id < t2.id AND
    t1.day = t2.day AND
    t1.month = t2.month AND
    t1.year = t2.year;

También puede usar el comando de Mostrar duplicados Filas para verificar la eliminación.

Opción 2:eliminar filas duplicadas usando una tabla intermedia

Puedes crear una tabla intermedia y utilícelo para eliminar filas duplicadas. Esto se hace transfiriendo solo las filas únicas a la tabla recién creada y eliminando la original (con las filas duplicadas restantes).

Para hacerlo, siga las instrucciones a continuación.

1. Cree una tabla intermedia que tenga la misma estructura que la tabla de origen y transfiera las filas únicas que se encuentran en el origen:

CREATE TABLE [copy_of_source] SELECT DISTINCT [columns] FROM [source_table];

Por ejemplo, para crear una copia de la estructura de la tabla de muestra dates el comando es:

CREATE TABLE copy_of_dates SELECT DISTINCT id, day, month, year FROM dates;

2. Una vez hecho esto, puede eliminar la tabla de origen con el comando soltar y cambiar el nombre de la nueva:

DROP TABLE [source_table];

ALTER TABLE [copy_of_source] RENAME TO [source_table];

Por ejemplo:

DROP TABLE dates;

ALTER TABLE copy_of_dates RENAME TO dates;

Opción 3:eliminar filas duplicadas mediante ROW_NUMBER()

Importante: Este método solo está disponible para MySQL versión 8.02 y después. Compruebe la versión de MySQL antes de intentar este método.

Otra forma de eliminar filas duplicadas es con ROW_NUMBER() función.

SELECT *. ROW_NUMBER () Over (PARTITION BY [column] ORDER BY [column]) as [row_number_name];

Por lo tanto, el comando para nuestra tabla de muestra sería:

SELECT *. ROW_NUMBER () Over (PARTITION BY id ORDER BY id) as row_number;

Los resultados incluyen un row_number columna. Los datos están particionados por id y dentro de cada partición hay números de fila únicos. Los valores únicos están etiquetados con el número de fila 1 , mientras que los duplicados son 2 , 3 , y así sucesivamente.

Por lo tanto, para eliminar filas duplicadas, debe eliminar todo excepto los marcados con 1. Esto se hace ejecutando DELETE consulta con el row_number como filtro.

Para eliminar filas duplicadas, ejecute:

DELETE FROM [table_name] WHERE row_number > 1;

En nuestro ejemplo fechas tabla, el comando sería:

DELETE FROM dates WHERE row_number > 1;

El resultado le indicará cuántas filas se han visto afectadas, es decir, cuántas filas duplicadas se han eliminado.

Puede verificar que no haya filas duplicadas ejecutando:

SELECT * FROM [table_name];

Por ejemplo:

SELECT * FROM dates;