Qu'est-ce que Upsert et comment le faire dans MySQL

Les bases de données sont devenues une partie essentielle de la société d'aujourd'hui. Les bases de données nous aident à stocker nos données plus efficacement et à réduire les erreurs. D'un petit magasin à une entreprise multinationale, tous utilisent des bases de données pour stocker leurs informations. Vous pouvez affirmer que les informations peuvent être stockées dans une feuille de calcul de manière organisée.

Bien que cela soit techniquement correct mais pratiquement, c'est très désastreux. La raison en est que à mesure que les données se développent, beaucoup de licenciements et de données inutiles sont stockés. Beaucoup de fois, les données peuvent même en conflit. Une telle chose peut être très nocive pour toute entreprise. La solution consiste à stocker les données dans une base de données.

Le système de gestion de la base de données ou le SGBD, en bref, est un logiciel qui permet aux utilisateurs de gérer leur base de données. Lorsque vous traitez avec d'énormes morceaux de données, une base de données est utilisée. Le système de gestion de la base de données vous offre beaucoup de fonctionnalités critiques. Upsert est l'une de ces fonctionnalités. Upsert, comme son nom, indique une combinaison de deux mots à jour et à insérer. Les deux premières lettres proviennent de la mise à jour tandis que les quatre autres proviennent de l'insertion. Upsert permet à l'auteur du langage de manipulation de données (DML) d'insérer une nouvelle ligne ou de mettre à jour une ligne existante. Upsert est une opération atomique, ce qui signifie qu'il s'agit d'une opération en une seule étape.

MySQL, par défaut, fournit une option de mise à jour de clé en double pour insérer, qui effectue cette tâche. Cependant, d'autres déclarations peuvent être utilisées pour terminer cette tâche. Il s'agit notamment de déclarations comme ignorer, remplacer ou insérer.

Vous pouvez effectuer Upsert en utilisant MySQL de trois manières.

1. Upsert using insert ignore
2. Upsert en utilisant Remplacer
3. Upsert en utilisant la mise à jour de la clé en double

Avant de nous déplacer plus loin, j'utiliserai ma base de données pour cet exemple, et nous travaillerons dans MySQL Workbench. J'utilise actuellement la version 8.0 Édition communautaire. Le nom de la base de données utilisé pour ce tutoriel est Sakila. Sakila est une base de données contenant seize tables. Nous nous concentrerons sur la table du magasin dans cette base de données. Ce tableau contient quatre attributs et deux rangées. L'attribut store_id est la clé principale.

Voyons comment les manières ci-dessus affectent ces données.

Upsert using insert ignore

INSERT INSERT fait que MySQL ignore vos erreurs d'exécution lorsque vous effectuez un insert. Donc, si vous insérez un nouvel enregistrement avec la même clé principale que l'un des enregistrements déjà dans le tableau, vous obtiendrez une erreur. Cependant, si vous effectuez cette action en utilisant INSERT IGNORE, l'erreur résultante sera supprimée.

Ici, nous essayons d'ajouter le nouvel enregistrement en utilisant l'instruction INSERT MYSQL standard.

Nous recevons l'erreur suivante.

Mais lorsque nous effectuons la même fonction en utilisant INSERT IGNORE, nous ne recevons aucune erreur. Au lieu de cela, nous recevons l'avertissement suivant et MySQL ignore cette instruction INSERT. Cette méthode est bénéfique lorsque vous ajoutez d'énormes quantités de nouveaux enregistrements à votre tableau. Donc, s'il y a des doublons, MySQL les ignorera et ajoutera les enregistrements restants à la table.

Upsert en utilisant Remplacer:

Dans certaines circonstances, vous souhaiterez peut-être mettre à jour vos enregistrements existants pour les tenir au courant. L'utilisation de l'insert standard ici vous donnera une entrée en double pour l'erreur de clé primaire. Dans cette situation, vous pouvez utiliser Remplacer pour effectuer votre tâche. Lorsque vous utilisez, remplacez les deux sur les événements suivants se déroulent.

Il y a un ancien record qui correspond à ce nouveau record. Dans ce cas, Remplacer fonctionne comme une instruction INSERT standard et insère le nouvel enregistrement dans le tableau. Le deuxième cas est que certains enregistrements précédents correspondent au nouveau record à ajouter. Ici, remplacez les mises à jour l'enregistrement existant.

La mise à jour est effectuée en deux étapes. Dans la première étape, l'enregistrement existant est supprimé. Ensuite, l'enregistrement nouvellement mis à jour est ajouté comme un insert standard. Il remplit donc deux fonctions standard, supprime et insérer. Dans notre cas, nous avons remplacé la première ligne par des données nouvellement mises à jour.

Dans l'image ci-dessous, vous pouvez voir comment le message indique «2 lignes affectées» alors que nous avons seulement remplacé ou mis à jour les valeurs d'une seule ligne. Au cours de cette action, le premier record a été supprimé, puis le nouveau record a été inséré. Par conséquent, le message indique: «2 lignes affectées."

Upsert using insert… sur la mise à jour de la clé en double:

Jusqu'à présent, nous avons examiné deux commandes Upsert. Vous avez peut-être remarqué que chaque méthode avait son manque ou ses limites si vous pouvez. La commande IGNORE, bien qu'il ait ignoré l'entrée en double, mais elle ne mettait pas à jour les enregistrements. La commande de remplacement, bien qu'elle ait été mise à jour, bien techniquement, elle n'était pas mise à jour. Il supprimait puis insérait la ligne mise à jour.

Une option plus populaire et plus efficace que les deux premières est la méthode de mise à jour de clé en double. Contrairement à Remplace, qui est une méthode destructrice, cette méthode est non destructive, ce qui signifie qu'elle ne laisse pas tomber les lignes en double en premier; Au lieu de cela, il les met directement à la mise à jour. Le premier peut causer beaucoup de problèmes ou d'erreurs, étant une méthode destructrice. Selon vos contraintes de clé étrangère, elle peut provoquer une erreur, ou dans le pire des cas, si votre clé étrangère est définie sur Cascade, il peut supprimer les lignes de l'autre table liée. Cela peut être très dévastateur. Donc, nous utilisons cette méthode non destructive car elle est beaucoup plus sûre.

Nous modifierons les enregistrements mis à jour en utilisant le remplacement de leurs valeurs d'origine. Cette fois, nous utiliserons la méthode de mise à jour de clé en double.

Remarquez comment nous avons utilisé des variables. Ceux-ci peuvent être utiles car vous n'avez pas besoin d'ajouter des valeurs dans l'instruction, encore et encore, réduisant ainsi les chances d'erreur. Ce qui suit est le tableau mis à jour. Pour le différencier de la table d'origine, nous avons changé l'attribut Last_update.

Conclusion:

Ici, nous avons appris que Upsert est une combinaison de deux mots à jour et à insérer. Il fonctionne sur le principe suivant que, si la nouvelle ligne n'a pas de doublons, insérez-la et s'il a des doublons, exécutez la fonction appropriée en fonction de l'instruction. Il existe trois méthodes pour effectuer Upsert. Chaque méthode a quelques limites. Le plus populaire est la méthode de mise à jour des clés en double. Mais selon vos besoins, toute personne ci-dessus peut vous être plus utilisée. J'espère que ce tutoriel vous est utile.