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é.

MySQL Duplicate force

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;
MySQL et docteur Jivaro

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.

28 commentaires pour “MySQL 4.1 et INSERT ... ON DUPLICATE KEY UPDATE”

  1. ludivine dit :

    slt moi c ludivine mon adresse c ludivine68@hotmail.fr et mon blog c flamby68.skyblog.fr gro bisou

  2. dew dit :

    Mais pourquoi moi…

  3. Kévin dit :

    Tiens, ils font du .fr maintenant chez Skyblog ?

  4. desoude dit :

    qui seme le vent… ^^

  5. ZeFroG dit :

    J’en ai marre de me faire les abdos à chaque fois que je viens ici :p

  6. alvas dit :

    objectif google :o

  7. Rik dit :

    Et la commande REPLACE, elle ne faisait pas déjà ça toute seule comme une grande ?

  8. e-t172 dit :

    Idem que pour Rik, j’utilise REPLACE qui est présent depuis MySQL 3 et qui fait exactement la même chose…

  9. grand-mister dit :

    et si on spamait mail et skyblog ludivine :D ?

  10. dew dit :

    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.

  11. amoss dit :

    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 !

  12. ZeFroG dit :

    Vous êtes méchants :p

  13. Antoine dit :

    Mais c’est tellement tentant :)

  14. Ouizzeul dit :

    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. Tony dit :

    E bé, on en apprend des choses ; )

  16. Nicolas dit :

    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 !

  17. Kévin dit :

    Tsss tsss, incitation au spam et à la débauche ;) Commentaire retiré.

  18. aspect dit :

    ha , super pratique ça ! :)

  19. razorblade dit :

    merci pour ton duplicate key, il me sauve la vie ;-)

  20. Thomas Mathey dit :

    Désolé d’être terre-à-terre mais moi ce qui me plait ce sont les images ;oD vraiment ou vas-tu chercher tout ça !

  21. web-inside dit :

    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

  22. optyler dit :

    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)

  23. zorton dit :

    optyler > c’ est peut être un agent des services secrets de Skyrock envoyé pour neutraliser dew.

  24. Korfuri dit :

    ^-^ je trouve enfin la fonction sql dont j’avais besoin :)
    Merci !

  25. optyler dit :

    zorton > J’espère pas … à ton avis, elle a une casquette Lacoste ? un survette Jaune fluo ? des tongues ?

  26. jm dit :

    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.

  27. al dit :

    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 ???

  28. zhilly dit :

    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.’;

Laisser un commentaire