VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
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 oUPDATE em vez disso:
VALUES(col) também funciona, mas está obsoleta — use aliases de linha em vez dela:
REPLACE INTO
OREPLACE exclui a linha em conflito e insere uma nova. Parece um upsert, mas se comporta como uma exclusão + inserção:
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
OINSERT 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:
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
OON 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:
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:VALUES para o caso de várias linhas e AS new para o alias.
Solução de Problemas
Veja também
- Transações no MySQL — quando envolver um UPSERT em uma transação
- Deadlocks no MySQL — INSERT … ON DUPLICATE KEY UPDATE pode causar deadlocks sob concorrência
- Inserções em Massa no MySQL — carregando muitas linhas rapidamente, com ou sem tratamento de conflitos
- Erros Comuns do MySQL e Como Corrigi-los — erros de chave duplicada e o que eles significam
- MySQL INSERT ON DUPLICATE KEY UPDATE — a instrução por si só, com exemplos resolvidos