MySQL: Pokud záznam neexistuje, vytvoř nový. Jinak původní aktualizuj

by Zdeněk Večeřa 7 Comments

Typický příklad. Máme tabulku, kam ukládáme nějaké záznamy o činnosti uživatele. Tentokrát třeba návštěvu vlákna ve fóru, spolu s časem a počtem příspěvků ve vláknu.

Pokud takový záznam neexistuje (vázaný na ID uživatele a ID vlákna), tak vytvoříme nový záznam (INSERT), jinak provedeme aktualizaci záznamu (UPDATE), tedy čas a počet příspěvků.

Nabízí se několik možností, jak tento proces provést.

Možnost A

  1. Zjistit, jestli záznam existuje (SELECT COUNT(*)…)
  2. Pokud existuje, provést aktualizaci (UPDATE)
  3. Pokud neexistuej, vložit nový (INSERT)

Všetři tři kroky uzavřeme do stransakce (viz Jakub Vrána).

Možnost B

  1. Provést aktualizaci (UPDATE)
  2. Zjistit počet ovlivněných řádků (mysql_affected_rows())
  3. Pokud „nula“ (záznam neexistuje), tak provedeme vložení nového (INSERT)

Zdrojový kód pro možnost A

for($pocet_prispevku = 0 ; $pocet_prispevku < 1000; $pocet_prispevku++ ){

$sql=
"SELECT COUNT(*)
FROM `forum_vlakno_precteno`
WHERE `user_id`='".gpc_addslashes(5)."' AND `forum_vlakno_id`='".gpc_addslashes(15)."' LIMIT 1";
$result=mysql_query($sql);
if(!$result){ log_sql_error(__FILE__,__LINE__,__FUNCTION__,__METHOD__,$login->get_user('id'),'',$sql,mysql_errno(),mysql_error()); }

if( mysql_result($result,0) > 0 ){

$sql=
"UPDATE `forum_vlakno_precteno` SET "
."`pocet_prispevku`='".gpc_addslashes($pocet_prispevku)."',"
."`datetime_posledni_navsteva`=NOW()"
." WHERE `user_id`='".gpc_addslashes(5)."' AND `forum_vlakno_id`='".gpc_addslashes(15)."' LIMIT 1";
$result=mysql_query($sql);
$ovlivneno_radku=mysql_affected_rows();
if(!$result){ log_sql_error(__FILE__,__LINE__,__FUNCTION__,__METHOD__,$login->get_user('id'),'',$sql,mysql_errno(),mysql_error()); }


}else{
$sql=
"INSERT INTO `forum_vlakno_precteno` (`user_id`,`forum_kategorie_id`,`forum_vlakno_id`,`pocet_prispevku`,`datetime_posledni_navsteva`) VALUES ("
."'".gpc_addslashes(5)
."','".gpc_addslashes(10)
."','".gpc_addslashes(15)
."','".gpc_addslashes($pocet_prispevku)
."', NOW());";
$result=mysql_query($sql);
$forum_prispevek_id = mysql_insert_id();
if(!$result){ log_sql_error(__FILE__,__LINE__,__FUNCTION__,__METHOD__,$login->get_user('id'),'',$sql,mysql_errno(),mysql_error()); }

}
}

Zdrojový kód pro možnost B

for($pocet_prispevku = 0 ; $pocet_prispevku < 1000; $pocet_prispevku++ ){
$sql=
"UPDATE `forum_vlakno_precteno` SET "
."`pocet_prispevku`='".gpc_addslashes($pocet_prispevku)."',"
."`datetime_posledni_navsteva`=NOW()"
." WHERE `user_id`='".gpc_addslashes(5)."' AND `forum_vlakno_id`='".gpc_addslashes(15)."' LIMIT 1";
$result=mysql_query($sql);
$ovlivneno_radku=mysql_affected_rows();
if(!$result){ log_sql_error(__FILE__,__LINE__,__FUNCTION__,__METHOD__, $login->get_user('id'),'',$sql,mysql_errno(),mysql_error()); }

if($ovlivneno_radku==0){


$sql=
"INSERT INTO `forum_vlakno_precteno` (`user_id`,`forum_kategorie_id`,`forum_vlakno_id`,`pocet_prispevku`,`datetime_posledni_navsteva`) VALUES ("
."'".gpc_addslashes(5)
."','".gpc_addslashes(10)
."','".gpc_addslashes(15)
."','".gpc_addslashes($pocet_prispevku)
."', NOW());";
$result=mysql_query($sql);
$forum_prispevek_id = mysql_insert_id();
if(!$result){ log_sql_error(__FILE__,__LINE__,__FUNCTION__,__METHOD__, $login->get_user('id'),'',$sql,mysql_errno(),mysql_error()); }

}
}

Která varianta je rychlejší?

Jak je vidět, oba kusy kódu jsem spustil 1000 ×.

Varianta A se průměrně provedla za: 46.9017 sekund
Varianta B se průměrně provedla za:  23.4192 sekund

Varianta B je tedy o poznání rychlejší.

Pozn.: Zdrojáky jsou vytržené z aplikace, mírně jsem jen upravil proměnné, proto udivující části jako např. gpc_addslashes(10).

Možnost využít INSERT … ON DUPLICATE KEY UPDATE

MySQL od verze 4.1 nabízí kontrukci, která udělá to stejné v jednom dotazu. Potíž je ale v tom, že je třeba záznam vázat na unikátní index nebo primární klíč, což v uvedeném příkladě není možné.

Přesto konstrukce INSERT … ON DUPLICATE KEY UPDATE je zajímavá varianta. Pokud by se pro ni našlo využití, zajisté bude efektivnější, než varianta B, uvedená v tomto článku.

Více o konstrukci INSERT … ON DUPLICATE KEY UPDATE naleznete v oficiální dokumentaci.

Comments ( 7 )

  1. Hrach
    a co replace?
  2. Zdeněk Veřeřa
    Hrach: To nepůjde ze stejného důvodu jako konstrukce "INSERT … ON DUPLICATE KEY UPDATE". Ani v tomto případě nemám unikátní index nebo primární klíč.
  3. Hrach
    hm, už dvakrat jsem sem postoval dvě url, ale nějaký spam filtr je asi odchytl... takže ještě jednou s nějakým textem: http://dev.mysql.com/doc/refman/5.0/en/replace.html http://php.vrana.cz/replace.php
  4. Hrach
    ted vidim, ze si uz me komentoval... no, proletel sem to zbezne ten clanek; kazdopadne vztamo k nadpisu "MySQL: Pokud záznam neexistuje, vytvoř nový. Jinak původní aktualizuj" se hodi tuto variantu uvest.
  5. Zdeněk Veřeřa
    Akismet WordPressu tě označil za spam ;))
  6. Jakub Vrána
    Oba přístupy jsou špatně. U prvního přístupu by mohlo dojít k nějaké změně mezi SELECT a INSERT/UPDATE. Příkazy by bylo potřeba uzavřít do transakce. U druhého přístupu je problém s tím, že MySQL nemění řádky, pokud se nastavují stejné hodnoty, jaké v tabulce už jsou. Díky použití NOW() se toto riziko sice minimalizuje, ale spolehnout se na to nedá. Nevidím jediný důvod, proč by nad sloupci (user_id, forum_vlakno_id) nemohl být unikátní index. Pak by se dal použít ON DUPLICATE KEY UPDATE.
  7. Zdeněk Veřeřa
    Článek jsem upravil, díky.

Leave a reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Tato stránka používá Akismet k omezení spamu. Podívejte se, jak vaše data z komentářů zpracováváme..