VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
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
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
- A colisão que isto evita
rating_count tem rating como chave primária. Grave uma linha.
- Insira, ou atualize o que está no caminho
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 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.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 comVALUES(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 UPDATEquando a linha pode ou não estar lá. - Espere que a cláusula seja executada apenas em uma colisão de
PRIMARY KEYouUNIQUE. - 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 aliase leia os valores dela comoalias.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