MySQL 4.1 et INSERT ... ON DUPLICATE KEY UPDATE
Petite feature discrète et très simple présentant pourtant un certain avantage, l'instruction ON DUPLICATE KEY qui a vu le jour à partir de la version 4.1 de MySQL permet de procéder à la mise à jour (UPDATE) de valeurs dans une table lorsque leur insertion (INSERT) pourrait provoquer un dupliquage de clé.
Rien ne vaut un bon exemple. Avant, avec l'aide de PHP, il était commun de voir ceci :
$result=mysql_query("UPDATE table SET blabla='truc' WHERE cle='1337'");
if($result) {
if(mysql_affected_rows()==0) {
mysql_query("INSERT INTO table (cle,blabla)
VALUES (1337,'truc')");
}
}
Frustrant d'utiliser deux requêtes différentes : la première pour tenter une mise à jour d'enregistrements potentiellement existants, puis via le contrôle du nombre de rows réellement affectés (mysql_affected_rows) et une insertion si nécessaire si ce nombre est nul. Avec plusieurs centaines de milliers, voire de millions de requêtes, on pourrait se croire dans une barque jivaro sur l'Amazone, à contre-courant (pagayer, pagayer, pagayer).
Or, la réduction de tête requête fait des miracles. Elle est peu répandue car bon nombre de serveurs dissimulent encore des versions 3.x recouvertes de mousse. Partons donc à la découverte, ô combien passionnante, du trésor du grand ODKU :
INSERT INTO table (cle,blabla) VALUES (1337,'truc') ON DUPLICATE KEY UPDATE blabla='truc';
Imaginons une requête concernant la mise à jour d'un champ. On ne sait pas si ce champ est déjà présent dans un enregistrement. Celui-ci ne doit pas être créé s'il existe déjà, mais mis à jour avec la valeur spécifiée, voire additionné. Par exemple dans le cadre d'un vote. Si le vote est inexistant, il sera créé avec la valeur 10. Sinon, 10 lui sera additionné à la valeur déjà présente.
INSERT INTO table (cle,vote) VALUES (1337,10) ON DUPLICATE KEY UPDATE vote=vote+10;
Tout ceci à condition que la clé soit mise en jeu. L'événement ON DUPLICATE KEY n'agit que si l'insertion risque de provoquer un doublon sur une des clés de la table. Il est donc important plus que jamais de bien la structurer et de placer les index appropriés. Voilà donc une astuce utile dans le cas de génération de gros fichiers de requêtes SQL qui ne peuvent procéder aux vérifications précitées à l'aide d'un script (PHP ou équivalent) pour savoir s'ils doivent être constitués de requêtes INSERT ou UPDATE. En passant d'un script à un simple dump SQL à importer, les performances sont améliorées d'un facteur 20x (2000%). En savoir plus : Syntaxe de INSERT dans la doc officielle.
15 avril 2005 à 00:34
slt moi c ludivine mon adresse c ludivine68@hotmail.fr et mon blog c flamby68.skyblog.fr gro bisou
15 avril 2005 à 01:28
Mais pourquoi moi…
15 avril 2005 à 01:51
Tiens, ils font du .fr maintenant chez Skyblog ?
15 avril 2005 à 02:27
qui seme le vent… ^^
15 avril 2005 à 03:11
J’en ai marre de me faire les abdos à chaque fois que je viens ici :p
15 avril 2005 à 07:16
objectif google :o
15 avril 2005 à 09:23
Et la commande REPLACE, elle ne faisait pas déjà ça toute seule comme une grande ?
15 avril 2005 à 11:13
Idem que pour Rik, j’utilise REPLACE qui est présent depuis MySQL 3 et qui fait exactement la même chose…
15 avril 2005 à 12:46
et si on spamait mail et skyblog ludivine :D ?
15 avril 2005 à 12:47
REPLACE est différente (sinon il n’y aurait qu’une seule commande) car elle ne permet pas d’accéder aux anciennes valeurs. Elle remplace, mais ne peut procéder à un UPDATE, comme une incrémentation. C’est juste un raccourci pour INSERT+DELETE.
15 avril 2005 à 14:11
je cite :
Description du Skyblog :
coucou c moi!!g 16ans jsui chatain claire et pas blonde!mon prénom
et ludivine alias flamby je fai parti des trois drol de dame ac mayu et
pitou jaore mes pot et je sui raid dingue de mon bébé!!
Date de création : Dim. 10 avril 2005
c’est affligeant, n’allez pas voir son truc je suis resté abassourdi
@grand-mister : c’est très très tentant ! il fallait justemment que je test un script de ce genre !
15 avril 2005 à 15:08
Vous êtes méchants :p
15 avril 2005 à 17:15
Mais c’est tellement tentant :)
15 avril 2005 à 18:43
Mon dieu, les skyblog debarquent dans les vrai blog, c’est l’invasion, le combat commence
Sinon le SQL c’est mal, les algorithmes en C c’est mieux
15 avril 2005 à 21:33
E bé, on en apprend des choses ; )
15 avril 2005 à 22:31
Blog à part (muhaha), ces petites instructions REPLACE ou ON DUPLICATE KEY IGNORE/UPDATE sont un net avantage de MySQL sur ses chers concurrents Oracle, DB2 ou MS SQL Server.
J’utilise beaucoup SQL Server 2000 et il m’est arrivé récemment de pleurer face à un INSERT INTO X SELECT * FROM Y WHERE Y.KEY NOT IN (SELECT KEY FROM X)… En regardant le plan d’exécution, on voit SQL Server parcourir deux fois le même index, un premier pour l’obtention de la clé dans la requête imbriquée, la deuxième pour la vérification de la contrainte de clé primaire lors de l’insert.
Moralité, avec un bête INSERT INTO X SELECT * FROM Y ON DUPLICATE KEY IGNORE, on divise par deux la durée de la requête… Sauf que les "grands" n’ont pas cela. Enfin une innovation de MySQL qui mériterait de se retrouver ailleurs !
16 avril 2005 à 00:44
Tsss tsss, incitation au spam et à la débauche ;) Commentaire retiré.
6 mai 2005 à 13:11
ha , super pratique ça ! :)
24 mai 2005 à 10:36
merci pour ton duplicate key, il me sauve la vie ;-)
2 juillet 2005 à 11:13
Désolé d’être terre-à-terre mais moi ce qui me plait ce sont les images ;oD vraiment ou vas-tu chercher tout ça !
17 août 2005 à 14:18
pour reprendre ce que disai dew, si vous lisez la doc mysql vous verrez qu’effectivement en terme de performances, le replace c’est pas tip top…
le duplicate key c’est vraiment une bonne solution, il etait temps! xD
27 septembre 2005 à 11:09
Je me demande ce que viens faire une <acronym title="ayant un blog hébergé sur skyblog.fr">skyblogueuse</acronym> qui ne fait pas de prog sur un blog comme celui-ci traitant de mysql …
sympa le tuto sinon ;o)
31 octobre 2005 à 00:50
optyler > c’ est peut être un agent des services secrets de Skyrock envoyé pour neutraliser dew.
16 mars 2006 à 13:56
^-^ je trouve enfin la fonction sql dont j’avais besoin :)
Merci !
17 avril 2006 à 23:10
zorton > J’espère pas … à ton avis, elle a une casquette Lacoste ? un survette Jaune fluo ? des tongues ?
13 mars 2007 à 19:40
Une pensée pour les débutants ;)
function sql_update($table,$cle,$champs,$val_cle,$val_champs) {
$q_update = ‘INSERT INTO `’.$table.’` (‘.$cle.’,’.$champs.’) ‘;
$q_update.= ‘VALUES (\ ».$val_cle.’\’,\ ».$val_champs.’\’) ‘;
$q_update.= ‘ON DUPLICATE KEY UPDATE ‘.$champs.’=\ ».$val_champs.’\ »;
$q=mysql_query($q_update) or die($q_update . " – " . mysql_error());
}
sql_update(‘latable’,’lacle’,’lechamps’,’lavaleurdelacle’,’lavaleurduchamps’);
la colonne ‘lacle’ doit être spécifiée en PRIMARY dans ‘latable’
merci.
19 novembre 2007 à 18:39
je ne comprends pas vraiment comment se servir de cette fonction ON DUPLICATE KEY
car il semble évident que la plupart du temps, on laisse à MySQL le soin de gérer les cles, ou id, unique en AUTO_INCREMENT, non ?
comment donc est ce possible de spécifier une cle pour un enregistrement à insérer dans un champs de type unique et auto increment ???
21 novembre 2008 à 04:29
je vu le blog ici
je testé mais depuis que j’ai ajouté duplicate key pour change l’heure de ma values access_time ni inest ni l’update de l’heure fonction.
si qqn sait d’où vient mon erreur?
svp
//create and execute the query
$sql = “INSERT INTO yah_unlimited (data_key,data_value,agent_id,data_group,access_time) VALUES (’$key’, ‘$value’, ‘$group’, ‘$owner_id’, NOW()) ON DUPLICATE KEY UPDATE access_time = NOW()”;
$result = mysql_query($sql) or die(mysql_error());
echo ‘Store successful.’;