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 →
Às vezes você não sabe se uma linha está lá. INSERT ... ON DUPLICATE KEY UPDATE a grava se não estiver e a altera se estiver, em 1 instrução e sem uma verificação antes. É a instrução por trás de todo “salvar esta configuração” e de todo contador que precisa ser criado antes de poder ser incrementado. Estes exemplos usam o banco de dados sakila_practice da lição sobre INSERT. Execute o bloco de configuração de lá novamente antes de começar, depois abra o cliente com mysql -u root -p sakila_practice.

Como funciona

Substitua TABLE_NAME pela tabela, COLUMN_LIST e VALUE_LIST pelas colunas e valores como em um insert comum, ROW_ALIAS por um nome para a linha que você está oferecendo, e COLUMN_NAME pela coluna a alterar quando a linha já está lá. A cláusula ON DUPLICATE KEY UPDATE é executada apenas quando o insert violaria uma PRIMARY KEY ou um índice UNIQUE. O alias nomeia a linha que você tentou inserir, então ROW_ALIAS.COLUMN_NAME é o valor que você ofereceu e o nome da coluna sem qualificação é o valor já armazenado.

Exemplos

  1. A colisão que isto evita

rating_count tem rating como chave primária. Grave uma linha.
Grave-a de novo com uma nova contagem e o insert falha.

  1. Insira, ou atualize o que está no caminho

Uma linha está armazenada e a instrução diz 2 rows affected. Essa contagem é a forma de a instrução dizer qual caminho ela tomou: 1 para uma linha inserida, 2 para uma linha atualizada, e 0 quando a atualização encontrou o valor já correto.
A linha foi encontrada e já tinha 179, então não havia nada para gravar. Nunca leia a contagem de linhas afetadas como uma contagem de linhas na tabela.

  1. A mesma instrução em uma linha que não está lá

1 row affected é o caminho do insert. Nada na instrução mudou além dos valores, e esse é o objetivo dela: quem a chama não precisa saber se a linha está lá.

Atualizando um resumo inteiro

As linhas podem vir de uma consulta, que é como uma tabela de resumo é atualizada em 1 instrução. A forma com alias não é permitida ali.
Em vez disso, dê à consulta um alias próprio, envolvendo-a em uma tabela derivada, e leia os valores oferecidos a partir dela.
Duplicates: 1 é a linha G, que estava armazenada como 179 e agora é 178. As 3 classificações novas respondem por 3 das 5 linhas afetadas e essa única atualização pelas outras 2. R já tinha 195, então não entrou na contagem.

Código mais antigo usa VALUES() em vez de um alias

Instruções mais antigas leem o valor oferecido com VALUES(films) no lugar do alias. Essa forma ainda funciona, e gera um aviso.
\G termina uma instrução como um ponto e vírgula faz e imprime 1 coluna por linha, que é como você lê uma mensagem larga demais para uma tabela. Escreva instruções novas com um alias, e espere encontrar VALUES() em código escrito antes.

Resumo

  • Use INSERT ... ON DUPLICATE KEY UPDATE quando a linha pode ou não estar lá.
  • Espere que a cláusula seja executada apenas em uma colisão de PRIMARY KEY ou UNIQUE.
  • Leia a contagem de linhas afetadas como 1 para um insert, 2 para uma atualização, e 0 para nenhuma mudança.
  • Nomeie a linha oferecida com AS alias e leia os valores dela como alias.column.
  • Envolva a consulta em uma tabela derivada quando as linhas vêm de um SELECT.
  • Substitua VALUES(col) pela forma com alias, que é o que o aviso 1287 pede.

Veja também

  • INSERT — a forma simples e ERROR 1062
  • INSERT SELECT — linhas de uma consulta
  • UPDATE — alterando uma linha que você sabe que está lá
  • UPSERT no MySQL — REPLACE, INSERT IGNORE, e qual escolher