Manejo de datos duplicados en MySQL
En algunas tablas de datos MySQL pueden existir registros duplicados; en ciertos casos permitimos la existencia de datos duplicados, pero a veces también necesitamos eliminar estos datos duplicados.
En este capítulo presentaremos cómo evitar que aparezcan datos duplicados en las tablas y cómo eliminar los datos duplicados de las tablas.
Prevenir la aparición de datos duplicados en la tabla
Puedes establecer el campo especificado en la tabla de datos MySQL comoPRIMARY KEY (clave primaria)oUNIQUE (único)índice para garantizar la unicidad de los datos.Probemos un ejemplo: la siguiente tabla no tiene índice ni clave primaria, por lo que permite múltiples registros duplicados.
CREATE TABLE person_tbl
(
first_name CHAR(20),
last_name CHAR(20),
sex CHAR(10)
);
Si quieres configurar que los datos de los campos first_name y last_name en la tabla no puedan repetirse, puedes establecer el modo de clave primaria doble para configurar la unicidad de los datos. Si configuras una clave primaria doble, el valor predeterminado de esa clave no puede ser NULL, se puede establecer como NOT NULL. Como se muestra a continuación:
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), PRIMARY KEY (last_name, first_name) );
Si configuramos un índice único, al insertar datos duplicados, la sentencia SQL no se ejecutará correctamente y lanzará un error.
La diferencia entre INSERT IGNORE INTO e INSERT INTO es que INSERT IGNORE INTO ignora los datos que ya existen en la base de datos. Si la base de datos no tiene los datos, se insertan los nuevos; si los datos ya existen, se omite ese registro. De esta manera se pueden conservar los datos existentes en la base de datos y lograr el propósito de insertar datos en los huecos.
El siguiente ejemplo utiliza INSERT IGNORE INTO; después de ejecutarlo no se producirán errores ni se insertarán datos duplicados en la tabla:
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
-> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
-> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)
INSERT IGNORE INTO, al insertar datos, después de establecer la unicidad del registro, si se insertan datos duplicados, no devolverá un error, solo devolverá una advertencia. Mientras que REPLACE INTO, si existe un registro con la misma clave primaria o única, primero lo elimina y luego inserta el nuevo registro.
Otra forma de establecer la unicidad de los datos es agregar un índice UNIQUE, como se muestra a continuación:
CREATE TABLE person_tbl ( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sex CHAR(10), UNIQUE (last_name, first_name) );
Contar datos duplicados
A continuación contaremos el número de registros duplicados de first_name y last_name en la tabla:
mysql> SELECT COUNT(*) as repetitions, last_name, first_name
-> FROM person_tbl
-> GROUP BY last_name, first_name
-> HAVING repetitions > 1;
La consulta anterior devolverá el número de registros duplicados en la tabla person_tbl. En general, para consultar valores duplicados, realice las siguientes operaciones:
- Determine qué columna contiene valores que pueden duplicarse.
- En la lista de selección de columnas, use COUNT(*) para enumerar esas columnas.
- Enumere las columnas en la cláusula GROUP BY.
- En la cláusula HAVING, establezca que el número de duplicados sea mayor que 1.
Filtrar datos duplicados
Si necesita leer datos no duplicados, puede usar la palabra clave DISTINCT en la sentencia SELECT para filtrar los datos duplicados.
mysql> SELECT DISTINCT last_name, first_name
-> FROM person_tbl;
También puede usar GROUP BY para leer datos no duplicados en la tabla de datos:
mysql> SELECT last_name, first_name
-> FROM person_tbl
-> GROUP BY (last_name, first_name);
Eliminar datos duplicados
Si desea eliminar los datos duplicados de la tabla, puede usar la siguiente sentencia SQL:
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex); mysql> DROP TABLE person_tbl; mysql> ALTER TABLE tmp RENAME TO person_tbl;
Por supuesto, también puede agregar INDEX (índice) y PRIMARY KEY (clave primaria) a la tabla de datos como un método simple para eliminar registros duplicados en la tabla. El método es el siguiente:
mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);
Otras extensiones