Practical MySQL & MariaDB

MySQL UPSERT expliqué : Guide INSERT … ON DUPLICATE KEY UPDATE avec exemples

Bases SQL

.

目次

1. Qu’est‑ce que l’UPSERT ?

Vue d’ensemble

« UPSERT » désigne une fonctionnalité de base de données qui combine les opérations « INSERT » et « UPDATE ». En d’autres termes, si les données n’existent pas encore, elles sont insérées ; si les mêmes données existent déjà, elles sont mises à jour. En utilisant cette fonctionnalité, vous pouvez effectuer des opérations efficaces tout en conservant la cohérence des données.

Dans MySQL, cette fonctionnalité est implémentée à l’aide de la syntaxe INSERT ... ON DUPLICATE KEY UPDATE. Cette option vous permet d’éviter les erreurs de clé dupliquée et de mettre à jour les enregistrements existants même lorsqu’une duplication de clé se produit.

Cas d’utilisation

  • Systèmes de gestion de la clientèle : ajouter de nouvelles données client si elles n’existent pas, et mettre à jour les informations client existantes lorsqu’elles changent.
  • Gestion des stocks de produits : ajouter de nouveaux produits tout en mettant à jour la quantité en stock des produits déjà présents.

Avantages de l’UPSERT dans MySQL

  • Évite les erreurs de clé dupliquée
  • Simplifie les requêtes SQL
  • Maintient l’intégrité des données

2. Utilisation de base de l’UPSERT dans MySQL

Dans MySQL, les opérations UPSERT sont réalisées avec la syntaxe INSERT ... ON DUPLICATE KEY UPDATE. Avec cette syntaxe, si une clé dupliquée apparaît, vous pouvez mettre à jour tout ou partie des données existantes au lieu d’insérer de nouvelles lignes.

Syntaxe de base

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

Explication :

  1. Utilisez INSERT INTO pour insérer des données.
  2. Si les données à insérer existent déjà dans la table, la clause ON DUPLICATE KEY UPDATE est exécutée et les données existantes sont mises à jour.

Exemple :

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON DUPLICATE KEY UPDATE
name = 'Taro Tanaka';

Dans l’exemple ci‑dessus, si un utilisateur avec user_id égal à 1 existe déjà, le champ name est mis à jour avec la valeur « Taro Tanaka ». Si l’utilisateur n’existe pas, un nouvel enregistrement est inséré.

3. Syntaxe SQL détaillée et exemples d’UPSERT

Mise à jour de plusieurs colonnes

Lorsqu’on utilise UPSERT, il arrive de ne vouloir mettre à jour que certaines colonnes. Dans ce cas, vous pouvez spécifier uniquement les colonnes nécessaires dans la clause ON DUPLICATE KEY UPDATE.

INSERT INTO products (product_id, name, price)
VALUES (100, 'Laptop', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Dans cet exemple, si un produit avec product_id = 100 existe déjà, seule la colonne price est mise à jour, tandis que les autres colonnes (comme name) restent inchangées.

4. Différences avec d’autres bases de données

Les bases de données autres que MySQL offrent également des fonctionnalités similaires. Par exemple, PostgreSQL et SQLite utilisent les instructions INSERT ... ON CONFLICT ou MERGE pour obtenir un comportement de type UPSERT.

Exemple PostgreSQL

INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON CONFLICT (user_id) DO UPDATE SET
name = 'Taro Tanaka';

Dans PostgreSQL et SQLite, la clause ON CONFLICT sert à contrôler le comportement lorsqu’une erreur de clé dupliquée se produit. En revanche, MySQL utilise la clause ON DUPLICATE KEY UPDATE.

Particularités propres à MySQL

  • MySQL utilise INSERT ... ON DUPLICATE KEY UPDATE ; comme la syntaxe diffère de celle des autres SGBD, il faut faire attention lors de migrations entre systèmes.

5. Techniques avancées d’UPSERT

UPSERT en masse (traitement par lots de plusieurs enregistrements)

L’UPSERT peut être exécuté non seulement pour un seul enregistrement, mais aussi pour plusieurs en même temps. Cela améliore considérablement l’efficacité des opérations de base de données.

INSERT INTO products (product_id, name, price)
VALUES
(100, 'Laptop', 50000),
(101, 'Smartphone', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);

Dans cet exemple, plusieurs enregistrements de produits sont insérés d’un coup. Si des clés dupliquées existent, seul le champ price des enregistrements concernés est mis à jour.

Utilisation de procédures stockées pour l’UPSERT

Pour optimiser le traitement des UPSERT, vous pouvez également utiliser des procédures stockées. Cela vous permet de créer une logique réutilisable au sein de la base de données, améliorant à la fois la lisibilité et la maintenabilité de votre code.

6. Pièges courants et considérations importantes

Transactions et verrous morts

Lors de l’utilisation d’UPSERT—en particulier avec de gros volumes de données—des verrous morts peuvent survenir. Si le niveau d’isolation des transactions de MySQL est réglé sur REPEATABLE READ, les verrous d’écart sont plus susceptibles de se produire.

Éviter les verrous d’écart

  • Vous pouvez réduire la probabilité de verrous morts en changeant le niveau d’isolation des transactions à READ COMMITTED.
  • Si nécessaire, envisagez de diviser une grande opération UPSERT en lots plus petits et d’exécuter plusieurs requêtes au lieu d’une seule instruction volumineuse.

7. Conclusion

La fonctionnalité UPSERT de MySQL est extrêmement utile pour rationaliser l’insertion et la mise à jour des données tout en évitant les erreurs de clé dupliquée. Cependant, la mise en œuvre d’UPSERT nécessite une réflexion attentive sur les verrous morts potentiels et les paramètres de transaction. Lorsqu’elle est utilisée correctement, elle permet des opérations de base de données plus simples et plus efficaces.