O que é UpSert e como fazê -lo em MySql

Os bancos de dados se tornaram uma parte essencial da sociedade de hoje. Os bancos de dados nos ajudam a armazenar nossos dados com mais eficiência e reduzir erros. De uma pequena loja a uma empresa multinacional, todos usam bancos de dados para armazenar suas informações. Você pode argumentar que as informações podem ser armazenadas em uma planilha de maneira organizada.

Embora isso seja tecnicamente correto, mas praticamente, isso é muito desastroso. O motivo é que, à medida que os dados crescem, muitos despedimentos e dados inúteis são armazenados. Muitas vezes, os dados podem até entrar em conflito. Tal coisa pode ser muito prejudicial para qualquer negócio. A solução está armazenando os dados em um banco de dados.

Sistema de gerenciamento de banco de dados ou DBMS, em resumo, é um software que permite aos usuários gerenciar seu banco de dados. Ao lidar com grandes pedaços de dados, um banco de dados é usado. O sistema de gerenciamento de banco de dados fornece muitos recursos críticos. Upsert é um desses recursos. Upsert, como o nome, indica uma combinação de duas palavras atualizando e inserindo. As duas primeiras letras são da atualização, enquanto os quatro são de inserção. UPSERST permite que o autor de Manipulação de Dados (DML) insira uma nova linha ou atualize uma linha existente. UpSert é uma operação atômica, o que significa que é uma operação de etapa única.

MySQL, por padrão, fornece na opção de atualização de chave duplicada para inserir, que executa esta tarefa. No entanto, outras declarações podem ser usadas para concluir esta tarefa. Estes incluem declarações como ignorar, substituir ou inserir.

Você pode executar o UpSert usando o MySQL de três maneiras.

1. A upsert usando inserção ignorar
2. Upsert usando substituição
3. UpSert usando na atualização de chave duplicada

Antes de avançarmos, estarei usando meu banco de dados para este exemplo, e estaremos trabalhando no MySQL Workbench. Atualmente estou usando a versão 8.0 Comunidade Edição. O nome do banco de dados usado para este tutorial é Sakila. Sakila é um banco de dados contendo dezesseis tabelas. Vamos nos concentrar na mesa da loja neste banco de dados. Esta tabela contém quatro atributos e duas linhas. O atributo store_id é a chave primária.

Vamos ver como as maneiras acima afetam esses dados.

A upsert usando inserção ignorar

Inserir ignorar faz com que o MySQL ignore seus erros de execução ao executar uma inserção. Então, se você estiver inserindo um novo recorde com a mesma chave primária que um dos registros já na tabela, você receberá um erro. No entanto, se você executar esta ação usando inserção ignora, o erro resultante será suprimido.

Aqui tentamos adicionar o novo registro usando a instrução MySQL Inserir padrão.

Recebemos o seguinte erro.

Mas quando desempenhamos a mesma função usando o inserto ignorar, não recebemos erro. Em vez disso, recebemos o seguinte aviso e o MySQL ignora esta declaração de inserção. Este método é benéfico quando você está adicionando enormes quantidades de novos registros à sua tabela. Portanto, se houver algumas duplicatas, o MySQL as ignorará e adicionará os registros restantes à tabela.

Upsert usando substituir:

Em algumas circunstâncias, você pode atualizar seus registros existentes para mantê -los atualizados. Usar a inserção padrão aqui fornecerá uma entrada duplicada para o erro de chave primária. Nesta situação, você pode usar substituir para executar sua tarefa. Quando você usa, substitua os dois nos seguintes eventos ocorrem.

Há um registro antigo que corresponde a este novo registro. Nesse caso, a substituição funciona como uma declaração de inserção padrão e insere o novo registro na tabela. O segundo caso é que algum recorde anterior corresponde ao novo recorde a ser adicionado. Aqui substitua as atualizações do registro existente.

A atualização é feita em duas etapas. Na primeira etapa, o registro existente é excluído. Em seguida, o registro recém -atualizado é adicionado como uma inserção padrão. Por isso executa duas funções padrão, excluir e inserir. No nosso caso, substituímos a primeira linha por dados recém -atualizados.

Na figura abaixo, você pode ver como a mensagem diz "2 linhas afetadas" enquanto apenas substituímos ou atualizamos os valores de uma única linha. Durante esta ação, o primeiro registro foi excluído e o novo recorde foi inserido. Portanto, a mensagem diz: “2 fileiras afetadas.”

Upsert usando inserção ... na Atualização de chave duplicada:

Até agora, analisamos dois comandos upsert. Você deve ter notado que cada método teve seu déficit ou limitações se puder. O comando ignore, embora ignorasse a entrada duplicada, mas não estava atualizando nenhum registro. O comando substituir, embora estivesse atualizando, bem tecnicamente, não estava atualizando. Estava excluindo e depois inserindo a linha atualizada.

Uma opção mais popular e eficaz do que os dois primeiros é o método de atualização de chaves on duplicado. Ao contrário de substituir, que é um método destrutivo, esse método não é destrutivo, o que significa que não solta as linhas duplicadas primeiro; Em vez disso, ele os atualiza diretamente. O primeiro pode causar muitos problemas ou erros, sendo um método destrutivo. Dependendo de suas restrições de chave estrangeira, isso pode causar um erro ou, no pior cenário, se sua chave estrangeira estiver definida como cascata, poderá excluir as linhas da outra tabela vinculada. Isso pode ser muito devastador. Então, usamos esse método não destrutivo, pois é muito mais seguro.

Vamos alterar os registros atualizados usando substituir para seus valores originais. Desta vez, usaremos o método de atualização de chaves on duplicado.

Observe como usamos variáveis. Isso pode ser útil porque você não precisa adicionar valores na declaração, repetidamente, reduzindo assim as chances de erro. A seguir está a tabela atualizada. Para diferenciá -lo da tabela original, mudamos o atributo last_update.

Conclusão:

Aqui aprendemos que o UpSert é uma combinação de duas palavras atualizando e inserindo. Ele funciona com o seguinte princípio de que, se a nova linha não tiver nenhuma duplicata, insira -a e se tiver duplicatas desempenhar a função apropriada de acordo com a declaração. Existem três métodos para executar o UpSert. Cada método tem alguns limites. O mais popular é o método de atualização de chave on duplicado. Mas, dependendo de seus requisitos, qualquer um dos métodos acima pode ser mais útil para você. Espero que este tutorial seja útil para você.