Practical MySQL & MariaDB

UPSERT en MySQL explicado: Guía de INSERT … ON DUPLICATE KEY UPDATE con ejemplos

Fundamentos de SQL
目次

1. ¿Qué es UPSERT?

Visión general

«UPSERT» se refiere a una característica de bases de datos que combina las operaciones «INSERT» y «UPDATE». En otras palabras, si los datos no existen aún, se insertan; si los mismos datos ya existen, se actualizan. Al usar esta característica, puedes realizar operaciones eficientes manteniendo la consistencia de los datos.

En MySQL, esta funcionalidad se implementa mediante la sintaxis INSERT ... ON DUPLICATE KEY UPDATE. Esta característica permite evitar errores por claves duplicadas y actualizar registros existentes incluso cuando aparecen claves duplicadas.

Casos de uso

  • Sistemas de gestión de clientes: Añadir datos de un cliente nuevo si no existen, y actualizar la información del cliente cuando cambie.
  • Gestión de inventario de productos: Añadir productos nuevos mientras se actualiza la cantidad en stock de los productos ya existentes.

Ventajas de UPSERT en MySQL

  • Evita errores por claves duplicadas
  • Simplifica las consultas SQL
  • Mantiene la integridad de los datos

2. Uso básico de UPSERT en MySQL

En MySQL, las operaciones UPSERT se realizan con la sintaxis INSERT ... ON DUPLICATE KEY UPDATE. Con esta sintaxis, si ocurre una clave duplicada, puedes actualizar parte o la totalidad de los datos existentes en lugar de insertar datos nuevos.

Sintaxis básica

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON DUPLICATE KEY UPDATE
column1 = value1, column2 = value2;

Explicación:

  1. Usa INSERT INTO para insertar datos.
  2. Si los datos que se intentan insertar ya existen en la tabla, se ejecuta la cláusula ON DUPLICATE KEY UPDATE y se actualizan los datos existentes.

Ejemplo:

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON DUPLICATE KEY UPDATE
name = 'Taro Tanaka';

En el ejemplo anterior, si ya existe un usuario con user_id igual a 1, el campo name se actualiza a ‘Taro Tanaka’. Si el usuario no existe, se inserta un nuevo registro.

3. Sintaxis SQL detallada y ejemplos de UPSERT

Actualización de múltiples columnas

Al usar UPSERT, pueden presentarse casos en los que solo se deseen actualizar columnas específicas. En esas situaciones, puedes especificar únicamente las columnas necesarias en la cláusula ON DUPLICATE KEY UPDATE.

INSERT INTO products (product_id, name, price)
VALUES (100, 'Laptop', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

En este ejemplo, si ya existe un producto con product_id 100, solo se actualiza la columna price, mientras que las demás columnas (como name) permanecen sin cambios.

4. Diferencias con otras bases de datos

Otras bases de datos distintas a MySQL también ofrecen funcionalidades similares. Por ejemplo, PostgreSQL y SQLite utilizan INSERT ... ON CONFLICT o sentencias MERGE para lograr un comportamiento tipo UPSERT.

Ejemplo en PostgreSQL

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON CONFLICT (user_id) DO UPDATE SET
name = 'Taro Tanaka';

En PostgreSQL y SQLite, la cláusula ON CONFLICT se usa para controlar el comportamiento cuando ocurre un error por clave duplicada. En contraste, MySQL emplea la cláusula ON DUPLICATE KEY UPDATE.

Características específicas de MySQL

  • MySQL usa INSERT ... ON DUPLICATE KEY UPDATE; al ser una sintaxis distinta a la de otras bases de datos, se requiere especial atención al migrar entre sistemas.

5. Técnicas avanzadas de UPSERT

UPSERT masivo (procesamiento por lotes de varios registros)

UPSERT puede ejecutarse no solo para un registro individual, sino también para varios registros a la vez. Esto mejora significativamente la eficiencia de las operaciones de base de datos.

INSERT INTO products (product_id, name, price)
VALUES
(100, 'Laptop', 50000),
(101, 'Smartphone', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

En este ejemplo, se insertan varios registros de productos de una sola vez. Si existen claves duplicadas, solo se actualiza el campo price de los registros correspondientes.

Uso de procedimientos almacenados para UPSERT

Para optimizar el procesamiento de UPSERT, también puedes usar procedimientos almacenados. Esto te permite crear lógica reutilizable dentro de la base de datos, mejorando tanto la legibilidad como el mantenimiento de tu código.

6. Errores comunes y consideraciones importantes

Transacciones y bloqueos muertos

Al usar UPSERT—especialmente con grandes volúmenes de datos—pueden producirse bloqueos muertos. Si el nivel de aislamiento de transacciones de MySQL está configurado como REPEATABLE READ, los bloqueos de hueco son más probables.

Evitar los bloqueos de hueco

  • Puedes reducir la probabilidad de bloqueos muertos cambiando el nivel de aislamiento de transacciones a READ COMMITTED.
  • Si es necesario, considera dividir una operación grande de UPSERT en lotes más pequeños y ejecutar varias consultas en lugar de una única sentencia grande.

7. Conclusión

La función UPSERT de MySQL es extremadamente útil para simplificar la inserción y actualización de datos mientras se evitan errores por claves duplicadas. Sin embargo, implementar UPSERT requiere una consideración cuidadosa de los posibles bloqueos muertos y la configuración de transacciones. Cuando se usa correctamente, permite operaciones de base de datos más simples y eficientes.