Alternativa a UPDATE ON DUPLICATE KEY DELETE en MySQL

Lamentablemente, la sentencia expuesta en el título de esta publicación no existe en Mysql. No es posible borrar el registro luego de un UPDATE que genere un duplicado, en la misma sentencia.

La alternativa disponible es utilizar UPDATE IGNORE y luego ejecutar DELETE sobre los registros que fueron ignorados.

Ejemplo

Supongamos que existe la tabla ‘insumos_rubros’ con la asociación entre cada insumo y los rubros a los que pertenece, la cual posee una clave primaria compuesta por los 2 campos: id_insumo y id_rubro.

Al intentar cambiar todos los insumos de un rubro a otro se puede generar un registro duplicado, dado que algunos de ellos podrían haber estado asociados previamente al rubro destino:

mysql> UPDATE insumos_rubros SET id_rubro = 26 WHERE id_rubro = 13;
ERROR 1062 (23000): Duplicate entry '9-26' for key 'PRIMARY'

Rápidamente se nos viene a la mente un intento de solución:

mysql> UPDATE insumos_rubros SET id_rubro = 26 WHERE id_rubro = 13
    -> ON DUPLICATE KEY DELETE;

el cual hubiera generado un error de sintaxis debido a ‘ON DUPLICATE KEY DELETE’.

Aplicando la alternativa de UPDATE IGNORE y luego DELETE sobre los registros que fueron ignorados

mysql> UPDATE IGNORE insumos_rubros SET id_rubro = 26 WHERE id_rubro = 13;
Query OK, 65 rows affected (0.00 sec)

mysql> DELETE FROM insumos_rubros WHERE id_rubro = 13;
Query OK, 3 rows affected (0.00 sec)

logramos el objetivo deseado.

Basado en:

Anuncios

Utilizar los valores indicados en INSERT dentro de ON DUPLICATE KEY UPDATE en MySQL

Una de las alternativas para tratar los registros duplicados en un INSERT de MySQL es utilizar INSERT … ON DUPLICATE KEY UPDATE.  En ciertos casos es necesario actualizar los registros duplicados a partir de los valores que se han indicado en el INSERT, especialmente en una sentencia INSERT de múltiples registros (BULK INSERT).

Para ello existe la función VALUES(columna) que aplicada dentro de la cláusula UPDATE, en el caso de duplicate key, referencia al valor de ‘columna’ que habría sido insertado si no hubiera ocurrido el conflicto.

Ejemplo

Suponiendo que la tabla ‘stock’ posee la cantidad de cada item que se vende en una tienda y tiene un índice UNIQUE por el campo ‘insumo’, al agregar registros:

mysql> INSERT INTO stock (insumo,cant) VALUES ('pañuelos',4), ('corbatas',2)
     -> ON DUPLICATE KEY UPDATE cant=cant+VALUES(cant);

en caso de que ya existiera uno previo para cierto item, se incrementa su stock en la cantidad indicada.

Atención: La función VALUES() solo tiene significado dentro de sentencias INSERT... UPDATE y retorna NULL en cualquier otro caso.

Tratamiento de mayúsculas y minúsculas en MySQL

Según Wikipedia, la sensibilidad a mayúsculas y minúsculas, se explica de la siguiente manera:

Que algo sea sensible a las mayúsculas y minúsculas, abreviado a veces como sensible a mayúsculas,​ y dicho también directamente en inglés case sensitive, es una expresión usada en informática que se aplica a los textos en los que tiene alguna relevancia escribir un carácter en mayúsculas o minúsculas.

Lo contrario se denomina insensible a las mayúsculas y minúsculas, abreviado a veces como insensible a mayúsculas​ y dicho también directamente en inglés case insensitive.

 

Por defecto, MySQL hace un tratamiento diferente para las mayúsculas en los nombres de las bases de datos y tablas según el sistema operativo como veremos a continuación.

Lo más sencillo y recomendable para facilitar el uso, la portabilidad y evitar problemas es adoptar una convención consistente: crear y consultar bases de datos y tablas usando nombres en minúsculas.

Muchas veces no se puede aplicar esta recomendación “de palabra”, por lo que se debe recurrir a una variable de configuración.

 

Variable lower_case_table_names

Esta variable se define dentro del archivo de configuración my.cnf (Linux) o my.ini (Windows). Determina cómo MySQL almacena en disco y consulta los nombres de bases de datos y tablas. El almacenamiento en disco se refiere al proceso de creación, con las instrucciones CREATE DATABASE y CREATE TABLE, respectivamente.

  • lower_case_table_names=0 (case sensitive en la creación y consulta)
    • los nombres se guardarán en disco tal y como se han especificado en la creación
    • los nombres en las consultas deben respetar las mayúsculas
    • NO de se debería establecer en sistemas operativos que sean case-insensitive! (Windows u OS X)
  • lower_case_table_names=1 (case insensitive en la creación y consulta)
    • los nombres se guardarán en disco siempre en minúsculas, independientemente de como se escriban
    • los nombres en las consultas son convertidos a minúsculas (también aplica a los aliases)
  • lower_case_table_names=2 (case sensitive en la creación – case insensitive en la consulta)
    • los nombres se guardarán en disco tal y como se han especificado en la creación
    • los nombres en las consultas son convertidos a minúsculas (también aplica a los aliases)
    • Solo funciona en sistemas operativos que no sean case-sensitive!
    • Los nombres de tablas InnoDB son una excepción y se almacenan en minúsculas, como en el caso de lower_case_table_names=1

A continuación, los valores por defecto para cada sistema operativo:

  • Linux: lower_case_table_names=0
  • Windows: lower_case_table_names=1
  • OS X: lower_case_table_names=2

 

¿Qué valor de lower_case_table_names usar?

En el caso de usar MySQL en un solo tipo de sistema operativo, se puede mantener el valor por defecto de lower_case_table_names.

Los conflictos aparecen al empezar a transferir tablas hacia otros sistemas que difieran en la sensibilidad a mayúsculas:

En Linux podrían existir las siguientes tablas: usuarios, USUARIOS y Usuarios.

Al importar un dump que las contuviera en Windows, la primera se crea sin problemas, pero la siguiente genera un error indicando que la tabla ya existe. Esto sucede porque para Windows las 3 tienen el mismo nombre.

A fin de evitar este tipo de problemas se puede optar por:

  1. Usar lower_case_table_names=1 en todos los sistemas (mi recomendación)
    • La “desventaja” es que no se verán listados con sus nombres originales, si los mismos estaban especificados con mayúsculas en la creación
  2. Usar lower_case_table_names=0 en Linux y lower_case_table_names=2 en Windows.
    • Esta alternativa es propensa a fallar cuando se hagan consultas que funcionan en Windows porque los nombres se pasan a minúsculas automáticamente, pero en Linux generan errores porque no se utilizaron las mayúsculas tal cual se especificaron en la creación.
    • Si se usan tablas InnoDB no queda más remedio que usar la opción 1.

 

Basado en:

http://rm-rf.es/mayusculas-y-minusculas-en-mysql/

Concatenar cadenas de caracteres en MySQL

La función más común para juntar un conjunto de cadenas de caracteres en MySQL es CONCAT(cadena1, cadena2, …)

Algunas consideraciones:

  • Si los argumentos que recibe son “no binarios”, la cadena resultante es no binaria
  • Si al menos uno de los argumentos es una cadena binaria, la salida es binaria.
  • Un número se convierte a su forma de texto no binaria
  • Si alguno de los argumentos es NULL, retorna NULL
mysql> SELECT CONCAT('Lalo', ' ' , 'Landa');
'Lalo Landa'
mysql> SELECT CONCAT('Lalo', NULL , 'Landa');
NULL

El uso más común es para unir nombre de campos de las tablas incluidas en la consulta:

mysql> SELECT CONCAT(apellido, ', ' , nombre) FROM usuarios;
'Gómez, Mario'

Cuando es necesario indicar un separador para unir más de 2 cadenas, lo recomendable es usar la función CONCAT_WS(separador, cadena1, cadena2, …)

Es una forma especial de CONCAT(). El WS al final proviene de  “With Separator”, lo que en conjunto sería “Conctatenar con Separador”. El primer argumento que recibe es el separador que se agregará entre cada una de las cadenas indicadas a continuación.

Algunas consideraciones:

  • Si el separador es NULL, el resultado es NULL
  • Si alguno de las cadenas es NULL, la misma se omite
mysql> SELECT CONCAT_WS(' ', 'Lalo' , 'Landa');
'Lalo Landa'
mysql> SELECT CONCAT_WS(' ', 'Lalo', NULL , 'Landa');
'Lalo Landa'

Concatenando múltiples campos:

mysql> SELECT CONCAT(' ', titulo, nombre, apellido) FROM usuarios;
'Ing. Sergio Morales'

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 ‘paginas_hits’ posee la cantidad de veces que se accedió a una página y tiene un índice UNIQUE por el campo ‘pagina’, al intentar agregar un registro para ‘index.html’:

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

si ya existiera uno previo, lo que sucede es que se actualiza dicho registro incrementando en uno la cantidad de hits.

Es posible que en el cambio a realizar tengas que utilizar los valores indicados en INSERT dentro de ON DUPLICATE KEY UPDATE

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. Intenta 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().

Palabras reservadas en MySQL

Particulamente cuando deseamos utilizar ciertas palabras para nombrar tablas y campos, recibimos un mensaje de error del estilo

ERROR 1064 (42000): You have an error in your SQL syntax

esto se debe a que esas palabras son reservadas para Mysql por lo que no se pueden utilizar directamente y deben ser indicadas entre comillas del tipo backticks `.

Las palabras reservadas van cambiando con las versiones de Mysql por lo que deberán investigar cuales aplican a la versión que poseen.

Ejemplo:

mysql > CREATE TABLE schemas (id INT, name VARCHAR(50) );

producirá un error de sintaxis.

Para evitarlo debemos indicar el nombre de tabla entre `

mysql > CREATE TABLE `schemas` (id INT, name VARCHAR(50) );

 

Cuando se trata de campos también es viable utilizar nombres calificados, indicando:

nombre_tabla.nombre_campo

Tipos de join e información extra en EXPLAIN de MySQL

Cuando analizamos consultas a la base de datos con EXPLAIN, ciertas columnas que nos devuelve proveen mucha información, pero hay que saber leerlas. En este caso voy a hablar de los tipos de join entre las tablas y algunos de los extras.

Tipos de Join

Se muestran en la columna “Type”. El orden en que se detallan corresponde a la preferencia relativa entre los tipos de join, de mejor (system) a peor (ALL). Ej: es preferible que el tipo de join sea eq_ref en lugar de ref_or_null, que a su vez es mejor que range, el cual se prefiere antes que ALL.

  1. system: la tabla posee una sola fila
  2. const: la tabla matchea a lo sumo con 1 fila
  3. eq_ref: una fila se lee de esta tabla por cada combinación de filas de las tablas previas
  4. ref: toda las filas que matchean un valor del índice de esta tabla se leen de esta tabla por cada combinación de filas de las tablas previas
  5. fulltext: se usa un índice fulltext
  6. ref_or_null: similar a ref pero se buscan valores NULL también
  7. index_merge: es una optimización que utiliza varios índices y luego une los resultados
  8. unique_subquery: similar a ref en subqueries dentro de un IN cuando tiene índices unique
  9. index_subquery: similar a unique_subquery cuando tiene índices NO unique
  10. range: se obtienen las filas en un rango, usando un índice
  11. index: similar a ALL pero escanea el índice (puede estar con el Extra “using index”)
  12. ALL: consulta todos los registros de la tabla (full table scan) por cada combinación de filas de las tablas previas

Extras

Se muestran en la columna “Extra”. Pueden aparecer solos o combinados entre sí o con otros extras.

  • Using filesort: se hace una pasada adicional para determinar como obtener las filas ordenadas
    • es común cuando se utiliza un GROUP BY u ORDER BY, y no se puede ordenar con el índice
  • Using where: se usa el WHERE para restringir las filas que matchean contra la siguiente tabla
    • se podría decir que es casi necesario que aparezca para tipos de join: ALL e index
  • Using index: la información es obtenida solamente consultando el índice
    • con Using where: el índice se usa además para buscar valores de los campos en el índice
    • sin Using where: el índice se usa solo para evitar leer filas de la tabla
  • Using temporary: se crea una tabla temporal para generar el resultado
    • es común cuando se utiliza un GROUP BY

Basado en:

http://dev.mysql.com/doc/refman/5.7/en/explain-output.html