MySQL INSERT vs UPDATE : syntaxe, exemples, ON DUPLICATE KEY UPDATE et REPLACE

1. Introduction

MySQL est un système de gestion de base de données relationnelle populaire utilisé dans de nombreuses applications web et systèmes de gestion de bases de données. Parmi ses fonctionnalités, l’instruction INSERT et l’instruction UPDATE — utilisées pour ajouter et modifier des données — jouent un rôle essentiel en tant qu’opérations de données fondamentales. En les comprenant correctement et en les utilisant efficacement, les opérations de base de données deviennent plus fluides.

Dans cet article, nous fournissons une explication détaillée de l’INSERT et de l’UPDATE MySQL, de l’utilisation de base aux opérations avancées. Le contenu convient aux débutants jusqu’aux utilisateurs intermédiaires, veuillez donc l’utiliser comme référence.

2. Bases de l’INSERT

Syntaxe de base de l’INSERT

La syntaxe de base d’une instruction INSERT est la suivante.

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

Par exemple, considérons l’ajout d’un nouvel utilisateur à la table users.

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

Cette instruction SQL insère les valeurs « Taro Yamada », « taro@example.com » et « 30 » dans les colonnes name, email et age de la table users, respectivement.

Insertion de plusieurs lignes

En MySQL, vous pouvez également insérer plusieurs lignes de données en une seule fois. Dans ce cas, la syntaxe ressemble à ceci.

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

Cette méthode réduit le nombre d’accès à la base de données et peut améliorer les performances.

Gestion des valeurs NULL

Lors de l’utilisation d’une instruction INSERT, vous devrez peut-être gérer des valeurs NULL. Par exemple, si age n’est pas défini, écrivez-le comme ceci.

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

Notez que si une colonne a une contrainte NOT NULL, l’insertion d’une valeur NULL entraînera une erreur. Dans ce cas, vous devez définir une valeur par défaut ou spécifier une valeur explicitement.

3. Bases de l’UPDATE

Syntaxe de base de l’UPDATE

L’instruction UPDATE est utilisée pour modifier des données dans des enregistrements existants. Dans cette section, nous expliquons la syntaxe de base, la mise à jour avec des conditions, et pourquoi la clause WHERE est importante.

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

Par exemple, considérons la mise à jour de l’âge d’un utilisateur spécifique dans la table users.

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

Cette instruction SQL met à jour l’âge à 35 pour l’utilisateur dont le nom est « Taro Yamada » dans la table users.

Pourquoi la clause WHERE est importante

Si vous omettez la clause WHERE dans une instruction UPDATE, toutes les lignes de la table seront mises à jour. Cela peut causer une perte de données non intentionnelle, assurez-vous donc de spécifier des conditions.

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

Cette instruction SQL définit l’âge de tous les utilisateurs à 30.

Mises à jour conditionnelles

Lors de la spécification de plusieurs conditions, utilisez AND ou OR.

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

De cette façon, vous pouvez mettre à jour des données avec des conditions plus précises.

4. Combinaison de l’INSERT et de l’UPDATE

Dans les opérations de base de données, vous pourriez rencontrer des situations où vous devez parfois ajouter de nouvelles données et d’autres fois mettre à jour des données existantes. Dans de tels scénarios, vous pouvez traiter efficacement en utilisant INSERT ... ON DUPLICATE KEY UPDATE ou l’instruction REPLACE. Dans cette section, nous expliquons comment utiliser chacune et à quoi faire attention.

Comment utiliser INSERT … ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE est efficace lorsque des contraintes de clé primaire ou de clé unique existent. Avec cette syntaxe, vous pouvez effectuer « mise à jour si elle existe, insertion si elle n’existe pas » en une seule instruction SQL.

Syntaxe

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

Exemple

.Envisagez d’ajouter un nouvel utilisateur à la table users. Si le même email existe déjà, mettez à jour le name et l’age de cet utilisateur.

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

Cette instruction SQL fait ce qui suit :

1. Si aucun enregistrement n’existe avec email = 'taro@example.com', insérez les données.
2. Si un enregistrement existant existe, mettez à jour le name et l’age.

Remarques

• S’il y a une colonne AUTO_INCREMENT, le compteur augmente même lorsqu’une clé dupliquée se produit. Cela peut entraîner un comportement inattendu, alors soyez prudent.
• En utilisant la fonction VALUES(), vous pouvez réutiliser les valeurs que vous avez tenté d’insérer pour la mise à jour.

Fonctionnement de REPLACE et ses différences

L’instruction REPLACE supprime complètement les données existantes (lorsqu’une clé dupliquée est trouvée) puis insère les nouvelles données. Contrairement à INSERT ... ON DUPLICATE KEY UPDATE, l’enregistrement original est supprimé.

Syntaxe

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

Exemple

Insérez des données dans la table users, et si l’email est dupliqué, supprimez les données existantes et insérez les nouvelles données.

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

Cette instruction SQL fait ce qui suit :

1. Si un enregistrement existe avec email = 'taro@example.com', supprimez cet enregistrement.
2. Insérez les nouvelles données.

Remarques

• Comme il effectue une suppression + insertion, cela peut affecter les déclencheurs et les contraintes de clé étrangère.
• Soyez conscient des effets secondaires de la suppression (comme la perte de données liées).

Considérations de performance

INSERT ... ON DUPLICATE KEY UPDATE et REPLACE ont chacun leurs avantages et inconvénients. Pour les bases de données volumineuses ou les opérations à haute fréquence, les différences de performance sont importantes, il faut donc prendre en compte les points suivants.

Choisir le bon cas d’utilisation

• Lorsque INSERT … ON DUPLICATE KEY UPDATE est approprié
• Lorsque des contraintes de clé étrangère ou des déclencheurs existent et que vous souhaitez éviter les suppressions.
• Lorsque les mises à jour sont fréquentes.
• Lorsque REPLACE est approprié
• Lorsque vous avez besoin d’un remplacement complet des données.
• Pour les tables simples non affectées par les contraintes de clé étrangère ou les déclencheurs.

5. Exemples pratiques

Ici, nous présentons des cas d’utilisation réels pour MySQL INSERT et UPDATE, ainsi que l’utilisation pratique de « INSERT … ON DUPLICATE KEY UPDATE » et « REPLACE ». Cela vous aidera à comprendre comment appliquer ce que vous avez appris dans le travail réel.

Cas d’utilisation 1 : Système de gestion des stocks

Dans un système de gestion des stocks, l’enregistrement des produits et les mises à jour des stocks se produisent fréquemment. Utilisez INSERT pour ajouter de nouveaux produits, et utilisez UPDATE ou « INSERT … ON DUPLICATE KEY UPDATE » pour mettre à jour les produits existants.

Insertion et mise à jour des données produit

Par exemple, supposons qu’une table produit products soit structurée comme suit.

Enregistrement d’un nouveau produit

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

Mise à jour de la quantité en stock (Produit existant)

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

Insérer un nouveau ou mettre à jour la quantité en stock

Si vous souhaitez soit enregistrer un nouveau produit, soit mettre à jour le stock d’un produit existant, utilisez « INSERT … ON DUPLICATE KEY UPDATE ».

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

Ce SQL réalise ce qui suit :

• Si aucune donnée n’existe pour le produit ID 1, insérez-la.
• Si des données existent pour le produit ID 1, ajoutez 50 à la quantité en stock.

Cas d’utilisation 2 : Gestion des informations utilisateur

Dans les applications web, l’enregistrement et la mise à jour des utilisateurs sont courants. Utilisez INSERT pour enregistrer de nouveaux utilisateurs, et utilisez UPDATE ou « INSERT … ON DUPLICATE KEY UPDATE » pour mettre à jour les informations d’utilisateur existantes.

Structure de la table utilisateur

Enregistrement d’un nouvel utilisateur

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

Mise à jour des informations utilisateur

Par exemple, lorsqu’un utilisateur modifie son profil.

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

Enregistrer ou mettre à jour les informations

Lorsqu’un utilisateur se connecte pour la première fois, enregistrez-le ; si l’utilisateur existe déjà, mettez à jour l’horodatage de la dernière connexion.

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

Cas d’utilisation 3 : Mises à jour périodiques des données

Lorsqu’on travaille avec des données de capteurs ou de journaux, de nouvelles données peuvent être insérées chaque minute ou chaque seconde. Dans ce cas, vous insérez de nouvelles données avec INSERT ou mettez à jour les données existantes de manière conditionnelle.

Insertion de données de journal

Voici une table d’exemple sensor_logs pour enregistrer les données des capteurs.

Enregistrement de nouvelles données de capteur

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

Mettre à jour ou insérer des données

Si l’ID du capteur existe déjà, mettez à jour les données ; sinon, insérez-les.

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);

Remarques et meilleures pratiques

1. Gestion des erreurs : Lors de l’utilisation de ON DUPLICATE KEY UPDATE ou REPLACE, il est important de vérifier à l’avance l’impact des déclencheurs et des contraintes de clé étrangère.
2. Optimisation des performances : Pour des données à grande échelle, utilisez la conception d’index et les transactions pour fonctionner efficacement.
3. Intégrité des données : En particulier avec REPLACE, une suppression + insertion se produit, il faut donc prendre des mesures pour éviter le risque de perte de données liées.

6. Erreurs courantes et comment les corriger

Lors de l’utilisation des instructions MySQL INSERT et UPDATE, diverses erreurs peuvent survenir. Dans cette section, nous expliquons les erreurs courantes, leurs causes et les moyens spécifiques de les résoudre.

Exemples d’erreurs courantes

1. Erreur d’entrée dupliquée

Message d’erreur :

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

Cause :

• Cela se produit lorsque vous essayez d’insérer une valeur qui existe déjà dans une colonne avec une clé primaire ou une contrainte UNIQUE.

Comment corriger :

• Utiliser ON DUPLICATE KEY UPDATE : Effectuer une mise à jour lorsqu’une entrée dupliquée existe.

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

• Vérifier l’existence avant d’insérer : Pour éviter les doublons, vérifiez à l’avance si les données existent déjà.

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

2. Erreur de contrainte de clé étrangère

Message d’erreur :

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

Cause :

• Cela se produit lorsque les données de la table parent référencée n’existent pas en raison d’une contrainte de clé étrangère (FOREIGN KEY).

Comment corriger :

• Insérez les données liées dans la table parent.

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

• Désactivez temporairement les contraintes de clé étrangère (non recommandé).

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

3. Erreur liée à NULL

Message d’erreur :

Error: Column 'name' cannot be null

Cause :

• Cela se produit lorsque vous essayez d’insérer une valeur NULL alors que la colonne possède une contrainte NOT NULL.

Comment corriger :

• Définissez une valeur par défaut.

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

• Insérez une valeur appropriée avec INSERT.

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

4. Erreur de type de données

Message d’erreur :

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

Cause :

• Cela se produit lorsque vous essayez d’insérer ou de mettre à jour une valeur qui ne correspond pas au type de données de la colonne.

Comment corriger :

• Vérifiez le type de données et utilisez une valeur appropriée.

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

• Modifiez le type de données de la colonne (si nécessaire).

  ALTER TABLE users MODIFY age VARCHAR(10);
  

5. Erreur liée au verrouillage de table

Message d’erreur :

Error: Lock wait timeout exceeded; try restarting transaction

Cause :

• Cela se produit lorsqu’une autre transaction verrouille la table et que le verrou n’est pas libéré dans un certain délai.

Comment corriger :

• Pour éviter la contention des transactions, envisagez des actions telles que :
• Diviser les requêtes pour réduire le verrouillage des tables.
• Créer des index appropriés pour accélérer l’exécution des requêtes.

Meilleures pratiques pour la performance et la prévention des erreurs

1. Utiliser la gestion des transactions

• Lors de l’exécution de plusieurs INSERT et UPDATE, utilisez des transactions pour gérer les opérations de manière fiable.

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

2. Optimiser les index

• Définir des index appropriés sur les clés primaires et les clés étrangères réduit le risque d’erreur et améliore les performances.

  ALTER TABLE users ADD INDEX (email);
  

3. Rollback en cas d’erreurs

• Lorsqu’une erreur se produit, effectuez un rollback pour maintenir l’intégrité des données.

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

7. FAQ

Lors de l’utilisation des instructions MySQL INSERT et UPDATE, de nombreuses personnes ont des questions similaires. Dans cette section, nous approfondissons la compréhension à travers des questions fréquentes et leurs réponses.

Q1 : Lequel devrais-je utiliser, INSERT ou UPDATE ?

Réponse :

Utilisez INSERT pour ajouter de nouvelles données, et utilisez UPDATE pour modifier des données existantes. Cependant, si l’ajout de nouvelles données et la mise à jour de données existantes sont mélangés, l’utilisation de « INSERT … ON DUPLICATE KEY UPDATE » est la meilleure option.

Exemple :

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

Cette syntaxe peut gérer l’insertion de nouvelles données et la mise à jour de données existantes en une seule requête.

Q2 : Puis-je utiliser ON DUPLICATE KEY UPDATE pour chaque cas d’utilisation ?

Réponse :

Non. ON DUPLICATE KEY UPDATE présente les limitations suivantes :

1. Il ne fonctionne que lorsqu’une clé primaire ou une clé unique est définie. Si aucune n’est définie, aucune erreur n’est générée, mais le comportement ne sera pas celui attendu.
2. Pour des mises à jour à grande échelle, les performances peuvent se dégrader. Dans ce cas, envisagez d’utiliser des transactions ou de scinder les données.

Q3 : Quelle est la différence entre REPLACE et ON DUPLICATE KEY UPDATE ?

Réponse :

Ils sont similaires, mais leur comportement diffère considérablement.

En règle générale, utilisez ON DUPLICATE KEY UPDATE lorsque vous souhaitez mettre à jour sans supprimer les données, et utilisez REPLACE lorsque vous souhaitez un remplacement complet.

Q4 : Que se passe-t-il si j’oublie la clause WHERE ?

Réponse :

Si vous exécutez une instruction UPDATE sans spécifier de clause WHERE, tous les enregistrements de la table seront mis à jour. Cela est extrêmement dangereux et peut entraîner des modifications de données non intentionnelles.

Exemple :

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

Prévention :

• Spécifiez toujours une clause WHERE afin que seules les données correspondant à des conditions spécifiques soient mises à jour.
• En bonne pratique, exécutez d’abord une instruction SELECT pour confirmer les données cibles avant d’exécuter l’UPDATE.

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

Q5 : Existe-t-il un moyen d’accélérer INSERT et UPDATE ?

Réponse :

Vous pouvez améliorer les performances en utilisant les méthodes suivantes.

1. Optimiser les index : Définissez des index appropriés sur les colonnes requises pour accélérer les opérations de recherche et de mise à jour.

   CREATE INDEX idx_email ON users(email);
   

2. Opérations par lot : Il est plus efficace d’insérer ou de mettre à jour plusieurs lignes à la fois plutôt que de traiter une ligne à la fois.

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

3. Utiliser les transactions : Le traitement de plusieurs opérations dans une seule transaction réduit la contention des verrous.

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

4. Éviter les opérations inutiles : Vérifiez les données au préalable pour éviter les mises à jour ou insertions inutiles.

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

Q6 : Comment éviter les erreurs d’INSERT ou d’UPDATE ?

Réponse :

Pour éviter les erreurs, utilisez les approches suivantes.

• Vérifier les types de données : Assurez‑vous que les données que vous insérez/mettez à jour correspondent au type de la colonne.
• Définir correctement les contraintes : Configurez correctement les clés primaires, les clés uniques et les contraintes de clé étrangère afin de maintenir l’intégrité des données.
• Mettre en œuvre la gestion des erreurs : Ajoutez une logique dans votre programme pour gérer les erreurs lorsqu’elles surviennent.

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

8. Résumé

Dans cet article, nous avons couvert un large éventail de sujets : les bases et l’utilisation avancée des instructions MySQL INSERT et UPDATE, des cas d’utilisation pratiques, la gestion des erreurs et les réponses aux questions fréquentes. Vous trouverez ci‑dessous un récapitulatif des points clés.

Points clés

1. Notions de base sur INSERT

• Utilisez INSERT pour insérer de nouvelles données dans une table.
• Vous pouvez insérer plusieurs lignes, ce qui permet des opérations efficaces.
• Faites attention aux valeurs NULL et aux contraintes NOT NULL.

2. Notions de base sur UPDATE

• Utilisez UPDATE pour modifier des données existantes selon des conditions.
• Spécifiez toujours une clause WHERE afin d’éviter de mettre à jour toutes les lignes par inadvertance.

3. Combiner INSERT et UPDATE

• INSERT ... ON DUPLICATE KEY UPDATE permet d’insérer de nouvelles données et de mettre à jour des données existantes en une seule opération.
• REPLACE supprime les données puis les réinsère, il faut donc tenir compte des déclencheurs (triggers) et des clés étrangères.

4. Exemples pratiques

• Vous avez appris à utiliser INSERT et UPDATE dans des cas d’utilisation tels que la gestion d’inventaire et la gestion des informations utilisateur.
• Nous avons également présenté les meilleures pratiques pour traiter efficacement plusieurs opérations.

5. Erreurs et correctifs

• Nous avons expliqué les causes et les solutions aux problèmes courants tels que les entrées dupliquées, les contraintes de clé étrangère et les erreurs d’insertion de NULL.
• L’utilisation des transactions et la conception d’index sont importantes.

6. FAQ

• Nous avons répondu aux questions fréquentes concernant le choix entre INSERT vs UPDATE, la portée de ON DUPLICATE KEY UPDATE et l’optimisation des performances.

Prochaines étapes

Les instructions MySQL INSERT et UPDATE sont fondamentales pour les opérations de base de données et constituent des compétences essentielles pour le développement d’applications. En vous basant sur ce que vous avez appris dans cet article, envisagez les étapes suivantes.

1. Apprendre la gestion des transactions : Pour réaliser des opérations de base de données plus avancées, approfondissez votre compréhension de l’utilisation des transactions.
2. Optimiser la conception des index : Apprenez à concevoir des index afin de maintenir les performances des requêtes à mesure que le volume de données augmente.
3. Améliorer la journalisation pour le dépannage : Mettez en place l’enregistrement et l’analyse des logs afin d’identifier rapidement les causes lorsqu’une erreur survient.
4. Utiliser la documentation officielle de MySQL : Pour plus de détails et les dernières fonctionnalités, consultez la documentation officielle de MySQL .

Note finale

Nous espérons que cet article vous aidera à comprendre INSERT et UPDATE et à les utiliser efficacement. Maîtriser les opérations de données de base améliorera vos compétences en gestion de bases de données et vous permettra de gérer des développements d’applications plus avancés.

Continuez à approfondir vos connaissances MySQL !