Le monde de MySQL et MariaDB

Guide UPSERT MySQL : Comment utiliser INSERT ON DUPLICATE KEY UPDATE avec des exemples

Bases du SQL
目次

1. Qu’est-ce que UPSERT ?

Aperçu

« UPSERT » désigne une opération de base de données qui combine à la fois INSERT et UPDATE. En d’autres termes, si les données n’existent pas déjà, elles seront insérées, et si elles existent, elles seront mises à jour. Cette fonctionnalité permet des opérations efficaces tout en maintenant la cohérence des données.

Dans MySQL, la syntaxe INSERT ... ON DUPLICATE KEY UPDATE fournit cette fonctionnalité. Avec elle, vous pouvez éviter les erreurs de clé en double et mettre à jour les enregistrements existants même lorsque des situations de données en double se produisent.

Cas d’utilisation

  • Systèmes de gestion des clients : Insérer de nouvelles données client si elles n’existent pas, ou mettre à jour les informations client existantes lorsqu’elles changent.
  • Gestion des stocks : Ajouter de nouveaux produits tout en mettant à jour les comptes de stock des existants.

Avantages de UPSERT dans MySQL

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

2. Utilisation de base d’UPSERT dans MySQL

Dans MySQL, les opérations UPSERT sont implémentées à l’aide de la syntaxe INSERT ... ON DUPLICATE KEY UPDATE. Avec cela, si une clé en double est trouvée, l’enregistrement existant est mis à jour au lieu d’insérer un nouveau.

Syntaxe de base

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

Explication :

  1. INSERT INTO tente d’insérer des données dans la table.
  2. Si les données existent déjà, la clause ON DUPLICATE KEY UPDATE s’exécute et met à jour l’enregistrement existant.

Exemple :

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

Dans cet exemple, si un utilisateur avec user_id = 1 existe déjà, son name sera mis à jour à ‘Taro Tanaka’. Sinon, un nouvel enregistrement sera inséré.

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

Mise à jour de plusieurs colonnes

Lors de l’utilisation d’UPSERT, vous pouvez choisir de mettre à jour uniquement certaines colonnes. Dans ces cas, spécifiez uniquement les colonnes cibles 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);

Ici, si un produit avec product_id = 100 existe déjà, seule la colonne price sera mise à jour, laissant les autres colonnes telles que name inchangées.

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

D’autres bases de données fournissent également une fonctionnalité similaire à UPSERT. Par exemple, PostgreSQL et SQLite utilisent INSERT ... ON CONFLICT ou MERGE comme équivalents.

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 est utilisée pour contrôler le comportement lorsque des erreurs de clé en double se produisent. En revanche, MySQL utilise ON DUPLICATE KEY UPDATE.

Unicité de MySQL

  • MySQL utilise INSERT ... ON DUPLICATE KEY UPDATE, ce qui diffère des autres bases de données. Une attention particulière est nécessaire lors de la migration de base de données.

5. Utilisation avancée d’UPSERT

UPSERT en masse (plusieurs enregistrements à la fois)

UPSERT peut également être appliqué à plusieurs enregistrements en une seule fois, améliorant significativement 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);

Ici, plusieurs produits sont insérés en une seule fois, et si une clé en double existe, seule la price est mise à jour.

Utilisation de procédures stockées pour UPSERT

Vous pouvez également utiliser des procédures stockées pour rationaliser les opérations UPSERT. Cela rend le code réutilisable, plus lisible et plus facile à maintenir.

6. Pièges et considérations pour UPSERT

Transactions et verrouillages morts

Lors de l’utilisation d’UPSERT avec de grands ensembles de données, des verrouillages morts peuvent se produire. Si le niveau d’isolation des transactions de MySQL est défini sur REPEATABLE READ, les verrous d’écart sont plus susceptibles de se produire.

Éviter les verrous d’écart

  • Changer le niveau d’isolation des transactions en READ COMMITTED peut réduire les risques d’interblocage.
  • Envisager de décomposer les opérations UPSERT en requêtes plus petites si nécessaire.

7. Conclusion

La fonctionnalité UPSERT de MySQL est un outil puissant pour insérer et mettre à jour les données efficacement tout en évitant les erreurs de clé dupliquée. Cependant, une considération attentive des paramètres de transaction et des interblocages potentiels est essentielle. Utilisé correctement, UPSERT peut rendre les opérations de base de données plus simples et plus efficaces.