Guia de UPSERT no My: Como Usar INSERT ON DUPLICATE KEY UPDATE com Exemplos

1. O que é UPSERT?

Visão Geral

“O UPSERT” refere-se a uma operação de banco de dados que combina tanto INSERT quanto UPDATE. Em outras palavras, se os dados ainda não existirem, eles serão inseridos, e se já existirem, serão atualizados. Esse recurso permite operações eficientes enquanto mantém a consistência dos dados.

No MySQL, a sintaxe INSERT ... ON DUPLICATE KEY UPDATE fornece essa funcionalidade. Com ela, você pode evitar erros de chaves duplicadas e atualizar registros existentes mesmo quando situações de dados duplicados ocorrem.

Casos de Uso

Sistemas de Gerenciamento de Clientes : Inserir novos dados de clientes se eles não existirem, ou atualizar informações de clientes existentes quando elas mudam.
Gerenciamento de Estoque : Adicionar novos produtos enquanto atualiza as contagens de estoque de itens existentes.

Benefícios do UPSERT no MySQL

Evita erros de chaves duplicadas
Simplifica consultas SQL
Mantém a integridade dos dados

2. Uso Básico do UPSERT no MySQL

No MySQL, as operações UPSERT são implementadas usando a sintaxe INSERT ... ON DUPLICATE KEY UPDATE. Com isso, se uma chave duplicada for encontrada, o registro existente é atualizado em vez de inserir um novo.

Sintaxe Básica

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

Explicação:

INSERT INTO tenta inserir dados na tabela.
Se os dados já existirem, a cláusula ON DUPLICATE KEY UPDATE é executada e atualiza o registro existente.

Exemplo:

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

Neste exemplo, se um usuário com user_id = 1 já existir, o name dele será atualizado para ‘Taro Tanaka’. Caso contrário, um novo registro será inserido.

3. Sintaxe SQL Detalhada e Exemplos para UPSERT

Atualizando Múltiplas Colunas

Ao usar UPSERT, você pode escolher atualizar apenas certas colunas. Nesses casos, especifique apenas as colunas de destino na cláusula ON DUPLICATE KEY UPDATE.

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

Aqui, se um produto com product_id = 100 já existir, apenas a coluna price será atualizada, deixando outras colunas, como name, inalteradas.

4. Diferenças com Outros Bancos de Dados

Outros bancos de dados também fornecem funcionalidade semelhante ao UPSERT. Por exemplo, PostgreSQL e SQLite usam INSERT ... ON CONFLICT ou MERGE como equivalentes.

Exemplo no PostgreSQL

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

No PostgreSQL e SQLite, a cláusula ON CONFLICT é usada para controlar o comportamento quando erros de chaves duplicadas ocorrem. Em contraste, o MySQL usa ON DUPLICATE KEY UPDATE.

Singularidade do MySQL

O MySQL usa INSERT ... ON DUPLICATE KEY UPDATE, o que difere de outros bancos de dados. Atenção especial é necessária durante a migração de bancos de dados.

5. Uso Avançado do UPSERT

UPSERT em Lote (Múltiplos Registros de Uma Vez)

O UPSERT também pode ser aplicado a múltiplos registros de uma só vez, melhorando significativamente a eficiência nas operações de banco de dados.

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

Aqui, múltiplos produtos são inseridos de uma vez, e se uma chave duplicada existir, apenas o price é atualizado.

Usando Procedimentos Armazenados para UPSERT

Você também pode usar procedimentos armazenados para simplificar as operações UPSERT. Isso torna o código reutilizável, mais legível e fácil de manter.

6. Armadilhas e Considerações para UPSERT

Transações e Deadlocks

Ao usar UPSERT com grandes conjuntos de dados, deadlocks podem ocorrer. Se o nível de isolamento de transação do MySQL estiver definido como REPEATABLE READ, locks de gap são mais propensos a acontecer.

Evitando Locks de Gap

Alterar o nível de isolamento da transação para READ COMMITTED pode reduzir os riscos de deadlock.
Considere dividir as operações UPSERT em consultas menores quando necessário.

7. Conclusão

A funcionalidade UPSERT do MySQL é uma ferramenta poderosa para inserir e atualizar dados de forma eficiente, evitando erros de chave duplicada. No entanto, é essencial considerar cuidadosamente as configurações de transação e os possíveis deadlocks. Quando usada corretamente, a UPSERT pode tornar as operações de banco de dados mais simples e eficazes.