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

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.