Přidání hodnoty do unikátního číselníku
Školení, která pořádám 
Řekněme, že máme číselník, ve kterém může být každá hodnota uložena pouze jednou (je nad ní tedy unikátní klíč). Jak postupovat, když do tohoto číselníku chceme přidat hodnotu a získat její ID ať už tam dříve byla nebo ne? Možnosti jsou v zásadě dvě:
Do číselníku se nejprve podíváme a pokud tam hodnotu nenajdeme, tak ji vložíme. Tyto dvě operace je samozřejmě nutné provést atomicky (tedy v transakci nebo se zamknutou tabulkou).
<?php mysql_query("LOCK TABLES ciselnik WRITE"); list($id) = mysql_fetch_row(mysql_query("SELECT id FROM ciselnik WHERE hodnota = '$hodnota'")); if (!isset($id)) { mysql_query("INSERT INTO ciselnik (hodnota) VALUES ('$hodnota')"); $id = mysql_insert_id(); } mysql_query("UNLOCK TABLES"); ?>
Druhá možnost je do číselníku hodnotu nejprve zkusit vložit a když se to nepovede kvůli duplicitě unikátního klíče, tak si ji vyžádat.
<?php if (mysql_query("INSERT INTO ciselnik (hodnota) VALUES ('$hodnota')")) { $id = mysql_insert_id(); } elseif (mysql_errno() == 1062) { $id = mysql_result(mysql_query("SELECT id FROM ciselnik WHERE hodnota = '$hodnota'"), 0); } ?>
Oba tyto přístupy jsou nešikovné v tom, že databázi kladou přinejmenším dva dotazy a je s nimi spojené psaní několika řádek kódu. V MySQL ale existuje ještě jedno řešení, které si vystačí s jedním dotazem:
<?php mysql_query("INSERT INTO ciselnik (hodnota) VALUES ('$hodnota') ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)"); $id = mysql_insert_id(); ?>
Toto řešení využívá jednak konstrukci ON DUPLICATE KEY a jednak funkci LAST_INSERT_ID s parametrem. Řešení spočívá v tom, že v případě konfliktu unikátního klíče se sloupec id nastaví na původní hodnotu, ta se ale zároveň uloží tak, aby ji vrátilo následné volání mysql_insert_id. Jediná nevýhoda tohoto přístupu je v tom, že u tabulek typu InnoDB se zvedne tabulkový čítač auto_increment (aniž by se někde použil).
Přijďte si o tomto tématu popovídat na školení Návrh a používání MySQL databáze.
Diskuse
Libor:
Mimořádně užitečná finta - díky za ni.
Ondrej Ivanic:
treba si len pozerat changelogy a potom ziadne finty neprekvapia, alebo bez velkeho trapenia zistim ze chyba je mimo mna...http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-x.html
a podobne pre PHP a Apache, ... a skratka vsetko co pouzivam
bukaJ:
Nějak jsem nepochopil myšlenku tohoto příspěvku.
Jouza:
Řešení spočívá v tom, že v případě konfliktu unikátního klíče se sloupec id nastaví na původní hodnotu.Spojeni puvodni hodnota asi neni uplne nejstastnejsi. Pod puvodni hodnota bych si predstavil id co sem zkousel vlozit, coz tady asi nebude uplne ono. Cekal bych spis ze se to updatne na autoincrement nebo tak neco. Ale co ja vim ja netusim jak to funguje doopravdy ;)
Jakub Vrána 
:
Funkce LAST_INSERT_ID(@expr) vrací @expr. Je to teď už jasnější? ID se skutečně nastaví na svou původní hodnotu (neboli se nezmění).
Jakub Vrána : 
finc:
Já bych problém viděl v tom, že jsem příliš svázán s MySQL. Raději si vyhodím výjimku a provedu podle code zapis či nikoli.
Je to také dost o návrhu databáze. Pokud budu jen trochu šikovnej, tak k tomuto problému dostat nemusím.
finc: Jakub Vrána :
Záleží na tom, co děláš. Jestli děláš univerzální systém, který má fungovat na všech možných konfiguracích, tak si MySQL vychytávky dovolit nemůžeš. Pokud děláš aplikaci na míru, tak si to dovolit můžeš.
U takovýchto aplikací jsem se naučil brát výběr databáze jako něco neměnného stejně jako výběr programovacího jazyka. I kód jde psát tak, aby šel spustit pod Perlem i PHP zároveň, ale dělá se to opravdu jen výjimečně a obvykle to ničemu neprospěje.
Ukaž nám svou šikovnost a předveď třeba řešení problému, kdy si chceš ukládat ke všem zobrazeným stránkám User-Agenta.
Jakub Vrána : finc:
Mám to chápat jako statistiku návštěvnosti? Asi bych uložil: id (auto_increment), id_stranky, user_agent, cas. Tam nevidím žádnou unikátnost. Záleží jak chceš dané údaje ukládat.
finc: Jakub Vrána :
Třeba jako statistiku návštěvnosti. Ty bys ukládal User-Agenta u každého záznamu jako text? Řeknu ti, proč to není vhodné:
1. User-Agentů je jen omezené množství a jsou poměrně dlouhé, takže ukládání u každého záznamu zvlášť je zbytečné plýtvání.
2. Můžeš potřebovat přehled všech User-Agentů. Dělat to dotazem do tabulky s obrovským množstvím záznamů není příliš vhodné.
3. Uložením User-Agenta jako varchar budou mít záznamy proměnlivou délku, což se negativně projeví při jejich mazání (zaplňováním děr po smazaných záznamech s proměnlivou délkou dochází k fragmentaci).
U nějakého pidiměřeníčka to můžeš hodit za hlavu, u větších objemů naopak potřebuješ trochu šikovnosti :-).
Jakub Vrána : Ondrej Ivanic:
Praveze cim viac pristupov tym sa viac oplati mat vsetko v jenom riadku, ale treba zmenit ine veci resp. zmenit pohlad na system.Je to uplne v pohode robit insert do tabulky pri kazdom pristupe a vkladat komplet udaje (cas, ip, user agent,...) Ak mame db ktora vie robit pokrocilejsie features tak mozem spravit aby parna hodina sa ukladala do jedneho tablespace aneparna do druheho. Vzdy bude jeden taky do ktoreho sa nebude insertovat, alebo bude hodinu stary a budem nad nim robit agregovane selekty podla toho co potrebujem a vysledok budem ukladat do dalsej tabulky nad ktorou zase budem moc robit ine agregacie. spracovane raw data mozem strcit do archivnej tabulky aby som vedel robit rozne offline statistiky.
- hlavna tabulka bude obsahovat aktualne a hodinu stare data
- vedlajsia tabulka bude obsahovat agregovane data napr za 1hod (napr. pocet pristupov na danu stranu z danej ip za danu hodinu)
- dalsie tabulky budu len agregovat data z vedlajsej tabulky za vhodne obdobia (8h, 16h, 24h, tyzden, 2 tyzdne, mesiac, kvartal, rok)
- archivna tabukla v ktorej budu vsetky stare data z hlavnej tabulky
este by sa dalo co to o tom popisat...
Jakub Vrána :
Povídání o agregování dat je pěkné, ale souvislost s probíraným tématem vidím jen velmi volnou. Já jsem uvedl tři důvody, proč se data s charakterem číselníku vyplatí ukládat do číselníku. Jaké jsou důvody proti? Vidím jediný - méně dotazů do databáze.
Jakub Vrána : Ondrej Ivanic:
Je to jedno ci je to ciselni, ale ine cudo sposob ukladania je potrebne zvazit v kazdej aplikacii. V agregovanych datach a archivnej tabulke to uz ma zmysel davat ako ciselnik, ale pri tych prvych by som to tak nerobil.To ze nieco vyzera ako macka a mnauka ako macka neznamena ze je to macka :)
finc:
Podle mého také záleží, jak budeš User-Agenta ukládat. Pokud budeš mít vytvořený číselník, tak je to jasné. Asi bych šel cestou: zjištění, zda existuje => akční dotaz. Vím, že mi to bude fungovat i na jiném DB.
V každé aplikaci to může být úplně jinak. Když budu ukládat jen ID, tak ten číselník bych měl nějak spravovat. Vznikne mi spousty verzí User-Agenta, které nakonec mohou být společné. Bylo by jistě zajímavé, jak bych poté řešil výpis statistik nad grupovanými daty.
Pak už se můžeme bavit o tom, jak User-Agenta rozparsovat a jak ukládat samotné údaje pro statistiku.
finc: Jakub Vrána :
"Vím, že mi to bude fungovat i na jiném DB."
Tím se dostáváme zpět k původnímu tématu této diskuse - pokud to nezbytně potřebuješ, tak fajn, pokud ne, tak lze využít elegance mnou popisovaného řešení.
Jakub Vrána : 
dgx:
Pro tyto případy jsem si oblíbil REPLACE. Samozřejmě je nutné, aby v tabulce nebylo víc unikátních indexů než primární, ale to by měl číselník splňovat.
Pro SQlite existuje podobná klauzule ON CONFLICT http://www.sqlite.org/lang_conflict.html
dgx: Jakub Vrána :
Obávám se, že REPLACE neudělá to, co potřebujeme. Tabulka má tvar (id serial, hodnota unique). Po provedení REPLACE s hodnotou, která už v tabulce je, se starý řádek smaže a vloží se místo něj nový - samozřejmě s jiným ID.
Pokud by tabulka měla tvar (id serial, hodnota index), tak bychom duplicity museli hlídat sami.
Jakub Vrána : cita:
mozna pokladam zly dotaz, ale co v pripade ze stejnou hodnotu mam cez foreign key natazenou do jine tabulky a najednou mi "zmizne" zaznam na ktery bylo odkazy (on zamozrejme nezmizne, protze constrait foreign key to ohlida), ale v takovem pripad tohle solution selze, pokad ne zajima me jak to je vyreseny?Diskuse je zrušena z důvodu spamu.
© 2005-2024 