MySQL, unir filas duplicadas

By: Javier on 3/11/2013

Hoy os voy a enseñar un pequeño truco en MySQL que permite borrar datos duplicados de una tabla forma sencilla. Para hacerlo, simplemente vamos a recurrir a una "índice temporal" sobre una tabla, que hará justamente el trabajo que deseamos. Para que se entienda mucho mejor y más rápido, lo veremos con una prueba de concepto. Partimos de la siguiente tabla test.

mysql> CREATE TABLE `test` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `a` int(11),
       `b` int(11),
       `c` int(11),
       `time` timestamp,
       PRIMARY KEY (`id`) );

El esquema es muy sencillo, pero para la demostración nos llegará. Ahora vamos a insertar unos cuentos valores, entre ellos, alguno repetido.

mysql> INSERT INTO test (a,b,c) VALUES (1,2,3),
       (1,2,3),
       (2,3,4),
       (3,4,5);
mysql> INSERT INTO test (a,b,c) VALUES (1,2,3);
mysql> SELECT * FROM test;
+----+------+------+------+---------------------+
| id | a    | b    | c    | time                |
+----+------+------+------+---------------------+
|  1 |    1 |    2 |    3 | 2013-03-04 19:41:20 |
|  2 |    1 |    2 |    3 | 2013-03-04 19:41:20 |
|  3 |    2 |    3 |    4 | 2013-03-04 19:41:20 |
|  4 |    3 |    4 |    5 | 2013-03-04 19:41:20 |
|  5 |    1 |    2 |    3 | 2013-03-04 19:41:42 |
+----+------+------+------+---------------------+
5 rows in set (0.00 sec)

Como podemos observar tenemos 5 filas con datos diferentes (el valor id, autoincremental, difiere). También para comprobar que funciona, hemos creado una nueva fila un poco más tarde que las demás, para así ver que el tiempo no influye en el resultado.

Nuestra intención es por lo tanto que aquellas filas que tengan los valores de a y b comunes se junten en una única fila. Aunque en este caso los valores son numéricos, sería aplicable a cualquier otro tipo soportado por MySQL. Para lograrlo, simplemente,

mysql> ALTER IGNORE TABLE test ADD UNIQUE INDEX(a,b);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 2  Warnings: 0

Y vemos el resultado,

mysql> SELECT * FROM test;
+----+------+------+------+---------------------+
| id | a    | b    | c    | time                |
+----+------+------+------+---------------------+
|  1 |    1 |    2 |    3 | 2013-03-04 19:41:20 |
|  3 |    2 |    3 |    4 | 2013-03-04 19:41:20 |
|  4 |    3 |    4 |    5 | 2013-03-04 19:41:20 |
+----+------+------+------+---------------------+
3 rows in set (0.00 sec)

Observamos por lo tanto que algunos valores que había en la tabla han desaparecido, concretamente los que se vieron afectados por la creación de UNIQUE INDEX de las columnas a y b. En caso de que no queramos que este índice quede, tendremos que borrarlo. Muy sencillo y puede que en algunos casos necesario.

Nota: En caso de que al ejecutar la sentencia de creación de nuevo índice os de el siguiente fallo,

mysql> ALTER IGNORE TABLE  test ADD UNIQUE INDEX(a,b);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'a'

es por que la tabla está en InnoDB y no en MyISAM. Así que antes de ejecutar dicha sentencia la cambiamos de engine, la modificamos y la volvemos a poner como nos interesa.

mysql> show create table test;
+-------+------------------------------+
| Table | Create Table                 |
+-------+------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `time` timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test ENGINE MyISAM;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> ALTER IGNORE TABLE test ADD UNIQUE INDEX(a,b);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 2  Warnings: 0

mysql> ALTER TABLE test ENGINE InnoDB;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

La entrada MySQL, unir filas duplicadas la puedes leer en Puppet Linux.