Tratar los registros duplicados en un INSERT de MySQL

Es un sentimiento desagradable el hacer una sentencia INSERT y recibir una respuesta del estilo:

ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'

Los registros se consideran duplicados cuando se intenta insertar un registro que tiene un valor igual a uno existente en un campo que posee una PRIMARY KEY o un índice UNIQUE.

Existen 3 alternativas para controlar la forma en que se resolverá la inserción de registros ante la existencia de duplicados. La elección depende de las reglas de negocio principalmente.

INSERT IGNORE

Todos los errores que ocurran durante la inserción serán ignorados y se presentarán como warnings. Se puede decir que se “mantiene el registro existente”.

IGNORE tiene mayor precedencia que el modo estricto, por lo que si ambos aplican y ocurriera un error, se ignoraría.

Ejemplo

Si la tabla de usuarios tiene un índice UNIQUE por el campo login y ya existe el usuario con login jstock:

mysql> INSERT IGNORE INTO usuarios (login, nombre, apellido, estado) VALUES('jstock', 'Juan', 'Stock', 1);
-> Query OK, 0 rows affected (0.00 sec)

se ignora el duplicado, manteniendo los valores previos del registro.

INSERT ON DUPLICATE KEY UPDATE

Si una fila a insertar fuera a causar un error de duplicación, en su lugar se realiza un UPDATE del registro que existía previamente, modificando uno o más campos a la vez (separando con coma cada asignación) según se indique.

La cantidad de filas afectadas que devuelve Mysql es:

  • 1 si se inserta la nueva fila
  • 2 si la fila existente se modifica
  • 0 si la fila existente se modifica a los valores que ya poseía

Ejemplo

Suponiendo que la tabla hits posee la cantidad de veces que se accedió a una página y tiene un índice UNIQUE por el campo pagina, al agregar un registro más para index.html:

mysql> INSERT INTO paginas (pagina,hits) VALUES ('index.hml',1) ON DUPLICATE KEY UPDATE hits=hits+1;

como ya existe un registro previo identificado, lo que sucede es que se actualiza incrementando en uno la cantidad de hits.

Si existe un campo con AUTO_INCREMENT…

al hacer este tipo de INSERT si efectivamente se crea un registro, se genera un nuevo valor para el campo en cuestión. Este se puede obtener a partir de la función LAST_INSERT_ID().

Si el resultado de la sentencia es hacer una actualización del registro, entonces no se puede obtener el id correspondiente con LAST_INSERT_ID(). Para lograr que esta función tenga sentido en este caso, agregamos en el UPDATE para el campo con AUTO_INCREMENT (supongamos que sea id):

id=LAST_INSERT_ID(id)

Así se logra que la función devuelva el mismo valor que se le pasa como argumento y sea recordado y devuelto por la siguiente invocación de LAST_INSERT_ID().

Ejemplo

mysql> INSERT INTO paginas (pagina,hits) VALUES ('index.hml',1) ON DUPLICATE KEY UPDATE id_pagina=LAST_INSERT_ID(id_pagina), hits=hits+1;

REPLACE

Funciona como INSERT, pero si se detecta un duplicado, se borra el registro existente en la tabla y se realiza la nueva inserción. Se puede decir que se “establece el registro nuevo”, quitando el previo. Al realizar un borrado se desencadenan los triggers asociados al DELETE y los registros vinculados por FOREIGN KEYS podrían borrarse también!

Los pasos que sigue esta sentencia al ejecutarse son:

  1. Intenta insertar el registro
  2. Si falla por duplicate entry de una key
    1. Borra el registro conflictivo de la tabla
    2. Intentar insertar el registro nuevamente

entonces, en caso de fallo por duplicado realiza la secuencia INSERT – DELETE – INSERT.

La cantidad de filas afectadas que devuelve Mysql es:

  • 1 si se inserta la nueva fila (no existía duplicado)
  • 2 si se detecta un duplicado: un registro borrado y uno insertado en su lugar
  • N>2 si hay duplicados debido a múltiples índices UNIQUE por ejemplo

Ejemplo

Si la tabla de usuarios tiene un índice UNIQUE por el campo login y ya existe el usuario con login jstock:

mysql> REPLACE INTO usuarios (login, nombre, apellido, estado) VALUES('jstock', 'Juan', 'Stock', 1);
-> Query OK, 2 rows affected (0.00 sec)

se borra el registro previo y se inserta el nuevo, con los valores indicados.

Si existe un campo con AUTO_INCREMENT…

al hacer el REPLACE en caso de que se detecte un duplicado, se genera un nuevo valor para el campo en cuestión en el registro insertado. Este se puede obtener a partir de la función LAST_INSERT_ID().

Anuncios

Inserciones concurrentes en tablas MyISAM de MySQL

Mysql soporta la inserción de registros en una tabla con engine MyISAM al mismo tiempo que se realizan consultas sobre la misma. Es decir, se pueden ejecutar sentencias SELECT mientras se están concretando sentencias INSERT. En un principio esto parece ser muy bueno pero tenemos que tener algunas consideraciones.

Existe una variable de configuración que controla este comportamiento: concurrent_insert. La misma puede tomar los siguientes valores a partir de Mysql 5.5.3 (entre paréntesis se indica el valor correspondiente en versiones previas):

  • NEVER (0): desactiva los INSERTs concurrentes
  • AUTO (1): activa los INSERTs concurrentes solamente para las tablas MyISAM no tengan huecos.
    • Cuando los huecos de una tabla se completan, se vuelven a activar las inserciones concurrentes en esa tabla.
    • Es el valor por defecto.
  • ALWAYS (2): activa los INSERTs concurrentes para TODAS las tablas MyISAM (aunque tengan huecos).
    • Si la tabla tiene huecos y está siendo utilizada por otro proceso (thread), las filas nuevas se agregan al final de la tabla.
    • De lo contrario, se lockea la tabla y se intenta llenar los huecos.

Ejemplo

mysql> SELECT @@GLOBAL.concurrent_insert;

-> AUTO

Además, los resultados de las inserciones podrían no ser visibles instantáneamente, dado que si existen muchas se encolan y se tratan secuencialmente, en simultaneidad con las consultas.

Basado en:

http://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.htm