MySQL INSERT vs UPDATE: Sintaxe, Exemplos, ON DUPLICATE KEY UPDATE e REPLACE

1. Introdução

O MySQL é um sistema de gerenciamento de banco de dados relacional popular usado em muitas aplicações web e sistemas de gerenciamento de banco de dados. Entre seus recursos, a instrução INSERT e a instrução UPDATE — usadas para adicionar e modificar dados — desempenham um papel essencial como operações fundamentais de dados. Ao compreendê-las corretamente e usá-las de forma eficiente, as operações de banco de dados se tornam mais suaves.

Neste artigo, fornecemos uma explicação detalhada do INSERT e UPDATE do MySQL, desde o uso básico até operações avançadas. O conteúdo é adequado para usuários iniciantes até intermediários, então use-o como referência.

2. Noções Básicas do INSERT

Sintaxe Básica do INSERT

A sintaxe básica de uma instrução INSERT é a seguinte.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Como exemplo, considere adicionar um novo usuário à tabela users.

INSERT INTO users (name, email, age)
VALUES ('Taro Yamada', 'taro@example.com', 30);

Este SQL insere os valores “Taro Yamada”, “taro@example.com” e “30” nas colunas name, email e age da tabela users, respectivamente.

Inserindo Múltiplas Linhas

No MySQL, você também pode inserir múltiplas linhas de dados de uma vez. Nesse caso, a sintaxe é assim.

INSERT INTO users (name, email, age)
VALUES
('Hanako Sato', 'hanako@example.com', 25),
('Ichiro Suzuki', 'ichiro@example.com', 40);

Usando este método, reduz o número de acessos ao banco de dados e pode melhorar o desempenho.

Lidando com Valores NULL

Ao usar uma instrução INSERT, você pode precisar lidar com valores NULL. Por exemplo, se age não estiver definido, escreva assim.

INSERT INTO users (name, email, age)
VALUES ('Jiro Tanaka', 'jiro@example.com', NULL);

Observe que se uma coluna tiver uma restrição NOT NULL, inserir um valor NULL resultará em um erro. Nesse caso, você deve definir um valor padrão ou especificar um valor explicitamente.

3. Noções Básicas do UPDATE

Sintaxe Básica do UPDATE

A instrução UPDATE é usada para modificar dados em registros existentes. Nesta seção, explicamos a sintaxe básica, como atualizar com condições e por que a cláusula WHERE é importante.

UPDATE table_name
SET column1 = new_value1, column2 = new_value2
WHERE condition;

Como exemplo, considere atualizar a idade de um usuário específico na tabela users.

UPDATE users
SET age = 35
WHERE name = 'Taro Yamada';

Este SQL atualiza a idade para 35 para o usuário cujo nome é “Taro Yamada” na tabela users.

Por Que a Cláusula WHERE é Importante

Se você omitir a cláusula WHERE em uma instrução UPDATE, todas as linhas na tabela serão atualizadas. Isso pode causar perda de dados não intencional, então certifique-se de especificar condições.

-- When the WHERE clause is omitted
UPDATE users
SET age = 30;

Este SQL define a idade de todos os usuários para 30.

Atualizações Condicionais

Quando especificar múltiplas condições, use AND ou OR.

UPDATE users
SET age = 28
WHERE name = 'Hanako Sato' AND email = 'hanako@example.com';

Dessa forma, você pode atualizar dados com condições mais precisas.

4. Combinando INSERT e UPDATE

Em operações de banco de dados, você pode encontrar situações em que às vezes precisa adicionar novos dados e outras vezes atualizar dados existentes. Em tais cenários, você pode processar de forma eficiente usando INSERT ... ON DUPLICATE KEY UPDATE ou a instrução REPLACE. Nesta seção, explicamos como usar cada um e o que observar.

Como Usar INSERT … ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE é eficaz quando existem restrições de chave primária ou chave única. Com esta sintaxe, você pode realizar “atualizar se existir, inserir se não existir” em uma única instrução SQL.

Sintaxe

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
column1 = new_value1, column2 = new_value2, ...;

Exemplo

Consider adding a new user to the users table. If the same email already exists, update that user’s name and age.

INSERT INTO users (email, name, age)
VALUES ('taro@example.com', 'Taro Yamada', 30)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
age = VALUES(age);

This SQL statement does the following:

1. If no record exists with email = 'taro@example.com' , insert the data.
2. If an existing record does exist, update name and age .

Notes

• If there is an AUTO_INCREMENT column, the counter increases even when a duplicate key occurs. This can cause unintended behavior, so be careful.
• By using the VALUES() function, you can reuse the values you attempted to insert for the update.

How REPLACE Works and How It Differs

The REPLACE statement completely deletes existing data (when a duplicate key is found) and then inserts the new data. Unlike INSERT ... ON DUPLICATE KEY UPDATE, the original record is deleted.

Syntax

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example

Insert data into the users table, and if email is duplicated, delete the existing data and insert the new data.

REPLACE INTO users (email, name, age)
VALUES ('taro@example.com', 'Taro Yamada', 30);

This SQL statement does the following:

1. If a record exists with email = 'taro@example.com' , delete that record.
2. Insert the new data.

Notes

• Because it performs delete + insert, it may affect triggers and foreign key constraints.
• Be aware of side effects from deletion (such as losing related data).

Performance Considerations

INSERT ... ON DUPLICATE KEY UPDATE and REPLACE each have pros and cons. For large databases or high-frequency operations, performance differences matter, so consider the following points.

Choosing the Right Use Case

• When INSERT … ON DUPLICATE KEY UPDATE is suitable
• When foreign key constraints or triggers exist and you want to avoid deletes.
• When updates occur frequently.
• When REPLACE is suitable
• When you need a complete replacement of the data.
• For simple tables not affected by foreign key constraints or triggers.

5. Practical Examples

Here, we introduce real-world use cases for MySQL INSERT and UPDATE, as well as practical usage of “INSERT … ON DUPLICATE KEY UPDATE” and “REPLACE.” This will help you understand how to apply what you have learned in actual work.

Use Case 1: Inventory Management System

In an inventory management system, product registration and stock updates happen frequently. Use INSERT to add new products, and use UPDATE or “INSERT … ON DUPLICATE KEY UPDATE” to update existing products.

Inserting and Updating Product Data

For example, assume a product table products is structured as follows.

Registering a New Product

INSERT INTO products (product_id, name, stock)
VALUES (1, 'Laptop', 50);

Updating Stock Quantity (Existing Product)

UPDATE products
SET stock = stock + 20
WHERE product_id = 1;

Insert New or Update Stock Quantity

If you want to either register a new product or update stock for an existing product, use “INSERT … ON DUPLICATE KEY UPDATE.”

INSERT INTO products (product_id, name, stock)
VALUES (1, 'Laptop', 50)
ON DUPLICATE KEY UPDATE
stock = stock + 50;

This SQL accomplishes the following:

• If no data exists for product ID 1, insert it.
• If data exists for product ID 1, add 50 to the stock quantity.

Use Case 2: User Information Management

In web applications, user registration and updates are routine. Use INSERT to register new users, and use UPDATE or “INSERT … ON DUPLICATE KEY UPDATE” to update existing user information.

User Table Structure

Registering a New User

INSERT INTO users (user_id, name, email, last_login)
VALUES (1, 'Taro Yamada', 'taro@example.com', NOW());

Atualizando Informações do Usuário

Por exemplo, quando um usuário altera seu perfil.

UPDATE users
SET name = 'Hanako Yamada', email = 'hanako@example.com'
WHERE user_id = 1;

Registrar ou Atualizar Informações

Quando um usuário faz login pela primeira vez, registre-o; se o usuário já existir, atualize o carimbo de data/hora do último login.

INSERT INTO users (user_id, name, email, last_login)
VALUES (1, 'Taro Yamada', 'taro@example.com', NOW())
ON DUPLICATE KEY UPDATE
last_login = NOW();

Caso de Uso 3: Atualizações Periódicas de Dados

Ao lidar com dados de sensores ou logs, novos dados podem ser inseridos a cada minuto ou segundo. Nesse caso, você pode inserir novos dados com INSERT ou atualizar dados existentes condicionalmente.

Inserindo Dados de Log

Aqui está uma tabela de exemplo sensor_logs para registrar dados de sensores.

Registrando Novos Dados de Sensor

INSERT INTO sensor_logs (sensor_id, temperature, last_updated)
VALUES (1, 25.5, NOW());

Atualizar ou Inserir Dados

Se o ID do sensor já existir, atualize os dados; caso contrário, insira-os.

INSERT INTO sensor_logs (sensor_id, temperature, last_updated)
VALUES (1, 25.5, NOW())
ON DUPLICATE KEY UPDATE
temperature = VALUES(temperature),
last_updated = VALUES(last_updated);

Notas e Melhores Práticas

1. Manipulação de erros: Ao usar ON DUPLICATE KEY UPDATE ou REPLACE, é importante verificar o impacto de gatilhos e restrições de chave estrangeira com antecedência.
2. Otimização de desempenho: Para dados em grande escala, use design de índices e transações para operar de forma eficiente.
3. Integridade dos dados: Especialmente com REPLACE, ocorre delete + insert, portanto você precisa de medidas para evitar o risco de perder dados relacionados.

6. Erros Comuns e Como Corrigi-los

Ao usar instruções MySQL INSERT e UPDATE, vários erros podem ocorrer. Nesta seção, explicamos erros comuns, suas causas e maneiras específicas de resolvê-los.

Exemplos de Erros Comuns

1. Erro de Entrada Duplicada

Mensagem de erro:

Error: Duplicate entry '1' for key 'PRIMARY'

Causa:

• Isso ocorre quando você tenta inserir um valor que já existe em uma coluna com chave primária ou restrição única (UNIQUE).

Como corrigir:

• Use ON DUPLICATE KEY UPDATE: Execute uma atualização quando existir uma entrada duplicada.

  INSERT INTO users (user_id, name, email)
  VALUES (1, 'Taro Yamada', 'taro@example.com')
  ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email);
  

• Verifique a existência antes de inserir: Para evitar duplicatas, verifique antecipadamente se os dados já existem.

  SELECT COUNT(*) FROM users WHERE user_id = 1;
  

2. Erro de Restrição de Chave Estrangeira

Mensagem de erro:

Error: Cannot add or update a child row: a foreign key constraint fails

Causa:

• Isso ocorre quando os dados da tabela pai referenciada não existem devido a uma restrição de chave estrangeira (FOREIGN KEY).

Como corrigir:

• Insira os dados relacionados na tabela pai.

  INSERT INTO parent_table (id, name) VALUES (1, 'Parent data');
  

• Desative temporariamente as restrições de chave estrangeira (não recomendado).

  SET FOREIGN_KEY_CHECKS = 0;
  -- Data operations
  SET FOREIGN_KEY_CHECKS = 1;
  

3. Erro Relacionado a NULL

Mensagem de erro:

Error: Column 'name' cannot be null

Causa:

• Isso ocorre quando você tenta inserir um valor NULL embora a coluna tenha a restrição NOT NULL.

Como corrigir:

• Defina um valor padrão.

  ALTER TABLE users MODIFY name VARCHAR(255) NOT NULL DEFAULT 'Unspecified';
  

• Insira um valor apropriado com INSERT.

  INSERT INTO users (name, email, age)
  VALUES ('Taro Yamada', 'taro@example.com', NULL);
  

4. Erro de Tipo de Dados

Mensagem de erro:

Error: Data truncated for column 'age' at row 1

Causa:

• Isso ocorre quando você tenta inserir ou atualizar um valor que não corresponde ao tipo de dados da coluna.

Como corrigir:

• Verifique o tipo de dados e use um valor apropriado.

  INSERT INTO users (age) VALUES (30); -- For an INT column
  

• Altere o tipo de dados da coluna (se necessário).

  ALTER TABLE users MODIFY age VARCHAR(10);
  

5. Erro Relacionado a Bloqueio de Tabela

Mensagem de erro:

Error: Lock wait timeout exceeded; try restarting transaction

Causa:

• Isso ocorre quando outra transação bloqueia a tabela e o bloqueio não é liberado dentro de um determinado tempo.

Como corrigir:

• Para evitar contenção de transações, considere ações como:
• Divida consultas para reduzir o bloqueio de tabelas.
• Crie índices apropriados para acelerar a execução das consultas.

Melhores Práticas para Desempenho e Prevenção de Erros

1. Use gerenciamento de transações

• Ao executar múltiplos INSERTs e UPDATEs, use transações para gerenciar as operações de forma confiável.

  START TRANSACTION;
  INSERT INTO orders (order_id, user_id) VALUES (1, 1);
  UPDATE users SET last_order = NOW() WHERE user_id = 1;
  COMMIT;
  

2. Otimize índices

• Definir índices apropriados em chaves primárias e chaves estrangeiras reduz o risco de erros e melhora o desempenho.

  ALTER TABLE users ADD INDEX (email);
  

3. Rollback em caso de erros

• Quando ocorre um erro, execute um rollback para manter a integridade dos dados.

  START TRANSACTION;
  -- Some operations
  ROLLBACK; -- On error
  

7. Perguntas Frequentes

Ao usar instruções INSERT e UPDATE do MySQL, muitas pessoas têm dúvidas semelhantes. Nesta seção, aprofundamos a compreensão por meio de perguntas e respostas comuns.

Q1: Qual devo usar, INSERT ou UPDATE?

Resposta:

Use INSERT para adicionar novos dados e use UPDATE para modificar dados existentes. Contudo, se a inserção de novos dados e a atualização de dados existentes forem misturadas, usar “INSERT … ON DUPLICATE KEY UPDATE” é a melhor opção.

Exemplo:

INSERT INTO users (user_id, name, email)
VALUES (1, 'Taro Yamada', 'taro@example.com')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
email = VALUES(email);

Esta sintaxe pode lidar com a inserção de novos dados e a atualização de dados existentes em uma única consulta.

Q2: Posso usar ON DUPLICATE KEY UPDATE para todos os casos de uso?

Resposta:

Não. ON DUPLICATE KEY UPDATE tem as seguintes limitações:

1. Funciona apenas quando uma chave primária ou chave única está definida. Se nenhuma estiver definida, nenhum erro ocorre, mas não se comportará como esperado.
2. Para atualizações em grande escala, o desempenho pode degradar. Nesse caso, considere usar transações ou dividir os dados.

Q3: Qual a diferença entre REPLACE e ON DUPLICATE KEY UPDATE?

Resposta:

Eles são semelhantes, mas seu comportamento difere significativamente.

Como diretriz, use ON DUPLICATE KEY UPDATE quando quiser atualizar sem excluir dados, e use REPLACE quando desejar uma substituição completa.

Q4: O que acontece se eu esquecer a cláusula WHERE?

Resposta:

Se você executar uma instrução UPDATE sem especificar uma cláusula WHERE, todos os registros da tabela serão atualizados. Isso é extremamente perigoso e pode causar alterações de dados não intencionais.

Exemplo:

-- The age of all records is updated to 30
UPDATE users
SET age = 30;

Prevenção:

• Sempre especifique uma cláusula WHERE para que apenas os dados que correspondam a condições específicas sejam atualizados.
• Como prática recomendada, execute primeiro uma instrução SELECT para confirmar os dados-alvo antes de executar o UPDATE.

  SELECT * FROM users WHERE name = 'Taro Yamada';
  UPDATE users SET age = 35 WHERE name = 'Taro Yamada';
  

Q5: Existe uma maneira de acelerar INSERT e UPDATE?

Resposta:

Você pode melhorar o desempenho usando os seguintes métodos.

1. Otimize índices: Defina índices apropriados nas colunas necessárias para acelerar as operações de busca e atualização.

   CREATE INDEX idx_email ON users(email);
   

2. Operações em lote: É mais eficiente inserir ou atualizar várias linhas de uma só vez do que processar uma linha por vez.

   INSERT INTO users (name, email, age)
   VALUES
   ('Hanako Sato', 'hanako@example.com', 25),
   ('Ichiro Suzuki', 'ichiro@example.com', 40);
   

3. Use transações: Processar várias operações em uma única transação reduz a contenção de bloqueios.

   START TRANSACTION;
   INSERT INTO orders (order_id, user_id) VALUES (1, 1);
   UPDATE users SET last_order = NOW() WHERE user_id = 1;
   COMMIT;
   

4. Evite operações desnecessárias: Verifique os dados antecipadamente para evitar atualizações ou inserções desnecessárias.

   SELECT COUNT(*) FROM users WHERE user_id = 1;
   -- Avoid unnecessary inserts/updates
   

Q6: Como posso evitar erros de INSERT ou UPDATE?

Resposta:

Para evitar erros, use as seguintes abordagens.

• Verifique os tipos de dados: Certifique‑se de que os dados que você insere/atualiza correspondam ao tipo de dado da coluna.
• Defina as restrições corretamente: Configure adequadamente chaves primárias, chaves únicas e restrições de chave estrangeira para manter a integridade dos dados.
• Implemente tratamento de erros: Adicione lógica no seu programa para lidar com erros quando eles ocorrerem.

  -- Roll back on error
  START TRANSACTION;
  INSERT INTO users (user_id, name, email) VALUES (1, 'Taro Yamada', 'taro@example.com');
  ROLLBACK; -- As needed
  

8. Resumo

Neste artigo, abordamos uma ampla gama de tópicos: os fundamentos e o uso avançado das instruções MySQL INSERT e UPDATE, casos de uso práticos, tratamento de erros e respostas às perguntas comuns. A seguir, um resumo dos pontos principais.

Principais aprendizados

1. Conceitos básicos de INSERT

• Use INSERT para inserir novos dados em uma tabela.
• Você pode inserir várias linhas, permitindo operações eficientes.
• Tenha cuidado com valores NULL e restrições NOT NULL.

2. Conceitos básicos de UPDATE

• Use UPDATE para modificar dados existentes com base em condições.
• Sempre especifique uma cláusula WHERE para evitar atualizar todas as linhas inadvertidamente.

3. Combinando INSERT e UPDATE

• INSERT ... ON DUPLICATE KEY UPDATE permite inserir novos dados e atualizar dados existentes em uma única operação.
• REPLACE exclui dados e os insere novamente, portanto, esteja atento a gatilhos (triggers) e chaves estrangeiras.

4. Exemplos práticos

• Você aprendeu como usar INSERT e UPDATE em casos de uso como gerenciamento de inventário e gerenciamento de informações de usuários.
• Também apresentamos boas práticas para processar várias operações de forma eficiente.

5. Erros e correções

• Explicamos causas e soluções para problemas comuns, como entradas duplicadas, restrições de chave estrangeira e erros de inserção de NULL.
• O uso de transações e o design de índices são importantes.

6. Perguntas frequentes

• Respondemos a perguntas comuns sobre escolher INSERT vs UPDATE, o escopo do ON DUPLICATE KEY UPDATE e otimização de desempenho.

Próximos passos

As instruções MySQL INSERT e UPDATE são fundamentais para operações de banco de dados e habilidades essenciais para o desenvolvimento de aplicações. Com base no que você aprendeu neste artigo, considere os seguintes próximos passos.

1. Aprenda gerenciamento de transações: Para executar operações de banco de dados mais avançadas, aprofunde seu entendimento sobre como usar transações.
2. Otimize o design de índices: Aprenda a projetar índices para manter o desempenho de consultas à medida que o volume de dados cresce.
3. Melhore o registro de logs para solução de problemas: Introduza a gravação e análise de logs para que você possa identificar rapidamente as causas quando erros ocorrerem.
4. Use a documentação oficial do MySQL: Para mais detalhes e recursos mais recentes, consulte a documentação oficial do MySQL .

Nota final

Esperamos que este artigo ajude você a entender INSERT e UPDATE e a usá‑los de forma eficiente. Dominar as operações básicas de dados melhorará suas habilidades de gerenciamento de banco de dados e permitirá que você lide com desenvolvimentos de aplicações mais avançados.

Continue aprofundando seu conhecimento em MySQL!