Pular para o conteúdo principal

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
O MySQL não tem a palavra-chave UPSERT, mas tem três maneiras de inserir uma linha ou tratar um conflito: INSERT ... ON DUPLICATE KEY UPDATE, REPLACE INTO e INSERT IGNORE. Elas se comportam de formas muito diferentes — escolher a errada pode corromper dados silenciosamente.

INSERT … ON DUPLICATE KEY UPDATE

Quando a inserção violaria uma restrição de unicidade (chave primária ou índice único), o MySQL executa o UPDATE em vez disso:
Para referenciar o valor da inserção tentada (em vez da linha existente), use aliases de linha:
A sintaxe mais antiga VALUES(col) também funciona, mas está obsoleta — use aliases de linha em vez dela:

REPLACE INTO

O REPLACE exclui a linha em conflito e insere uma nova. Parece um upsert, mas se comporta como uma exclusão + inserção:
Isso dispara os gatilhos BEFORE DELETE e AFTER DELETE na tabela de destino e reinicia o contador AUTO_INCREMENT para a nova linha. Como a exclusão é uma exclusão real de linha, ON DELETE CASCADE e as outras ações de chave estrangeira nas tabelas que a referenciam SÃO disparadas. Todas as colunas não listadas no REPLACE recebem seus valores padrão — não os valores da linha anterior. Use REPLACE somente quando você realmente quiser descartar a linha antiga por completo.

INSERT IGNORE

O INSERT IGNORE rebaixa erros a avisos. Em um conflito de chave, ele ignora a linha; em uma incompatibilidade de tipo ou truncamento, ele grava um valor convertido em vez disso:
O problema do INSERT IGNORE é que ele suprime todos os erros, não apenas os de duplicidade — e os casos que não são de duplicidade são piores do que uma linha ignorada. INSERT IGNORE INTO page_views (page, views) VALUES ('/new', 'not_a_number') insere ('/new', 0) com apenas um aviso: uma gravação silenciosa e corrompida, em vez de nenhuma operação. Use-o somente quando você realmente não se importar com o resultado.

Comparação

Efeito Colateral do AUTO_INCREMENT

O ON DUPLICATE KEY UPDATE incrementa o contador AUTO_INCREMENT mesmo quando segue o caminho de atualização, e não o de inserção. Com o tempo, isso cria lacunas na sequência. A page_views usa page como chave primária e não tem uma coluna AUTO_INCREMENT, então, para ver o efeito, use uma tabela com uma chave substituta:
Este é o comportamento esperado. Se a sua aplicação depende de uma sequência sem lacunas, use uma tabela de contador separada ou trate a geração da sequência na aplicação.

Perguntas Frequentes

O ON DUPLICATE KEY UPDATE é atômico?

Sim — a tentativa de inserção e a atualização acontecem em uma única operação atômica. Você não precisa envolvê-lo em uma transação para evitar uma condição de corrida entre verificar a existência e inserir.

E se vários índices únicos entrarem em conflito?

Se uma inserção violar mais de uma restrição de unicidade ao mesmo tempo, o MySQL atualiza apenas uma linha — qual delas é indefinido. Evite projetos em que um único INSERT possa entrar em conflito em vários índices únicos. Se o seu esquema tiver restrições de unicidade sobrepostas, teste o comportamento com cuidado.

Posso usar ON DUPLICATE KEY UPDATE com inserções de várias linhas?

Sim:
Cada linha em conflito dispara a sua própria atualização. Com aliases de linha, use VALUES para o caso de várias linhas e AS new para o alias.

Solução de Problemas

Veja também