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.
