- MySQL a čeština
- Návody k MySQL
- Jak si vypsat chybu
- Příklady řešení častých problémů
- Počet komentářů ke článku
- Číslování řádků přímo v mysql
- "Setřepání" sloupce
- Nalezení řádků s maximální (minimální) hodnotou
- Nalezení posledních pěti článků z každé kategorie
- Nalezení duplicit
- Odstranění duplicit
- Data pro další a předchozí záznam vůči $id, když jsou položky seřazeny podle jiného sloupečku než id
- Nalezení položek mající všechny hledané vlastnosti
- Jak zjistit aktuální hodnotu auto_increment počítadla? Jak v INSERT dotazu zjistit, jaká hodnota bude použita pro auto_increment sloupec?
- Časté chyby v návrhu databáze
- Špatný datový typ sloupce
- Více hodnot ve sloupci
- Různé druhy záznamů ve stejné tabulce
- Musíte pojmenovávat sloupce s indexem (jmeno1, jmeno2, jmeno3, …)
- Nová tabulka/sloupec pro každý záznam
- Recyklování primárních klíčů smazaných záznamů
# MySQL a češtinaPokud máte češtinu v databázi v pořádku, ale v php ne, většinou stačí po každém connectu zavolatmysql_query("set names utf8"); //(utf8 nahraďte za cp1250 pro stránky ve windows-1250 a latin2 pro iso-8859-2) mysql_set_charset("utf8"); //případně při užití knihovny mysqli mysqli::set_charset //voláno jako $instance_mysqli->set_charset("utf8"); Další podrobnosti naleznete na MySQL (4.1 a vyšší) a čeština minifaq toto téma také kvalitně řeší článek na Intervalu # Návody k MySQLNejlepší je oficiální návod v angličtině http://dev.mysql.com/doc/refman/5.0/en/index.htmlV češtině je dobrý seriál na http://www.linuxsoft.cz/mysql/ V češtině je i stručný přehled základní syntaxe záloha ht>tp://mm.gene.cz/ Stručné vysvětlení návrhu tabulek (normalizace) http://www.manualy.net/ Rozličné příklady v angličtině http://www.artfulsoftware.com/ # Jak si vypsat chybuWarning: …(): supplied argument is not a valid MySQL result resourceTato chybová hláška se objeví v případě, že funkci pro zpracování výsledků mysql dotazu (mysql_fetch_*, mysql_result, mysql_num_rows, …) dodáte na vstup proměnnou, která výsledek dotazu neobsahuje. To může mít několik příčin: 1) překlep v názvu proměnné - např. $vysledek = mysql_query(…); $pocet = mysql_num_rows($vzsledek); 2) dojde k chybě při provádění dotazu - na vině je v tomto případě většinou syntaktická chyba - v takovém případě je řešení velmi jednoduché - nechat si vypsat chybovou hlášku, kterou vám mySQL rádo vrátí - například následující konstrukcí: $vysledek = mysql_query($q="select nebo jiny sql prikaz…") or print("Došlo k chybě v dotazu: ".$q."<br>".mysql_error()); Příklady řešení častých problémů# Počet komentářů ke článkuselect c.*, count(k.id_clanku) pocet_komentaru from clanky c left join komentare k on c.id_clanku = k.id_clanku group by c.id_clanku # Číslování řádků přímo v mysqlJde to emulovat s pomocí proměnných. Buď ve dvou dotazech… set @radek=0 select @radek:=@radek+1 rownum, t.* from tabulka t Nebo s použitím vnořeného dotazu (4.1+)… select @radek:=@radek+1 rownum, t.* from (select @radek:=0) r, tabulka t Pokud je v dotazu použit limit, tak místo 0 samozřejmě doplňte příslušný offset (počet přeskočených řádků). # "Setřepání" sloupceset @radek=0; update odkazy set position=@radek:=@radek+1 order by position; Pozn. pokud děláte pořadí např. podle bodů a chcete mít dvě první místa, když mají stejně bodů, použijte navíc druhý update. # Nalezení řádků s maximální (minimální) hodnotou-- pokud hledáme hodnotu na unikátním sloupečku, stačí select * from tabulka order by id desc limit 1; -- najdi všechny řádky, kde je nejvyšší cena select t1.* from tabulka t1, (select max(t2.cena) cena from tabulka t2) t3 where t1.cena = t3.cena; -- pro každou kategorii najdi všechny řádky, kde je nejvyšší cena pro danou kategorii select t1.* from tabulka t1, (select t2.kategorie, max(t2.cena) cena from tabulka t2 group by t2.kategorie) t3 where t1.kategorie = t3.kategorie and t1.cena = t3.cena; # Nalezení posledních pěti článků z každé kategorie-- s využitím korelovaného poddotazu pro nalezení hranice select c2.* from clanky c2 join (select k.id_kategorie, (select id_clanku from clanky c where c.id_kategorie = k.id_kategorie order by id_clanku desc limit 4, 1) hranice from kategorie k) h on c2.id_kategorie = h.id_kategorie and c2.id_clanku >= ifnull(h.hranice, c2.id_clanku) -- s využitím uživatelských proměnných -- (pozor, při složitých dotazech se může přehodit vyhodnocování proměnných a nefunguje to ok select c.*, @n := @n * (@last_idk = c.id_kategorie) + 1 n, @last_idk := c.id_kategorie k from clanky c, (select @n := 0, @last_idk := '') t having n <= 5 order by c.id_kategorie, c.id_clanku desc # Nalezení duplicitVypsání řádků, které mají stejnou hodnotu ve sloupci sloupecek s nějakým jiným řádkem. SELECT t1.* FROM tabulka t1 JOIN (SELECT t2.sloupecek FROM tabulka t2 GROUP BY t2.sloupecek HAVING Count(*) > 1) t3 ON t1.sloupecek = t3.sloupecek ORDER BY t1.sloupecek # Odstranění duplicitNezapoměňte si data zazálohovat. Pak jsou obvyklé dva způsoby. 1) vytvořit si dočasný unikátní index, díky ingore se provede i na duplicitními sloupečky a další opakování vyhodí ALTER IGNORE TABLE tabulka ADD UNIQUE INDEX docasny123 (sloupecek); ALTER TABLE tabulka DROP INDEX docasny123; 2) použít delete using s propojením tabulky se samo sebou - potřebujete ale nějaký jedinečný sloupeček, tím si můžete i určit, zda chcete např. ponechat nejstarší nebo nejmladší hodnotu (nejvyšší id jako v příkladu) DELETE FROM t1 USING tabulka t1, tabulka t2 WHERE t1.sloupecek=t2.sloupecek AND t1.id<t2.id # Data pro další a předchozí záznam vůči $id, když jsou položky seřazeny podle jiného sloupečku než id-- předchozí select a.* from tabulka a, tabulka b where b.id = $id and (a.sloupecek < b.sloupecek or (a.sloupecek = b.sloupecek and a.id < b.id)) order by a.sloupecek desc, a.id desc limit 1; -- další select a.* from tabulka a, tabulka b where b.id = $id and (a.sloupecek > b.sloupecek or (a.sloupecek = b.sloupecek and a.id > b.id)) order by a.sloupecek, a.id limit 1; -- oba směry jedním dotazem ( select 'predchozi' smer, a.* from tabulka a, tabulka b where b.id = $id and (a.sloupecek < b.sloupecek or (a.sloupecek = b.sloupecek and a.id < b.id)) order by a.sloupecek desc, a.id desc limit 1 ) union all ( select 'dalsi' smer, a.* from tabulka a, tabulka b where b.id = $id and (a.sloupecek > b.sloupecek or (a.sloupecek = b.sloupecek and a.id > b.id)) order by a.sloupecek, a.id limit 1 ) # Nalezení položek mající všechny hledané vlastnostiHledáme id položek, které mají zároveň přiřazenou vlastnost 1, 5 a 7. Vyfiltrované záznamy zgroupujeme podle položky a díky having ještě omezíme jen na ty, kde byly všechny tři vlastnosti. select id_polozky from vazebni_tabulka where id_vlastnosti in (1, 5, 7) group by id_polozky having count(distinct id_vlastnosti) = 3 -- počet jedinečných hodnot ze závorky # Jak zjistit aktuální hodnotu auto_increment počítadla? Jak v INSERT dotazu zjistit, jaká hodnota bude použita pro auto_increment sloupec?Viz vlákno Jak získat auto_increment právě vkládaného řádku |
Časté chyby v návrhu databázeŠpatný návrh databáze může později způsobit mnoho problémů. Mezi typické příznaky špatného návrhu databáze patří situace, kdy „logicky“ víte, že v datech databáze je obsažena nějaká informace, ale je prakticky nemožné sestavit SQL dotaz pro její vrácení.I když je někdy možné špatný návrh obejít za cenu krkolomných SQL dotazů, vždy je lepší zvážit přepracování databáze. Čím později se návrh databáze přepracuje, tím je to bolestivější. # Špatný datový typ sloupcePříklad: Mám sloupec typu VARCHAR a v něm uložené datum, třeba "26. září 2010". Jak vybrat záznamy nejvýše měsíc staré?Dávejte sloupcům datový typ odpovídající tomu, co do nich chcete ukládat. Tzn. číselný typ pro čísla, datum pro datum, atp. Přehled datových typů MySQL. Potřebujete-li například datum nějak formátovat, databázové systémy s tím samozřejmě počítají- správné řešení je použít odpovídající datový typ (například DATE) a data pak formátovat při výběru (například funkcí DATE_FORMAT) # Více hodnot ve sloupciPříklad: Mám v tabulce sloupec vysledky typu VARCHAR a v něm seznam výsledků, třeba: "15,5,20,1,3,10,6,7". Jak získám deset nejvyšších výsledků v tabulce?Nikdy neskládejte seznam hodnot do jednoho sloupce, přinese to jen problémy. Správné řešení takovéto situace je obvykle vytvoření druhé tabulky, provázané s tou první, kde jednotlivé hodnoty budou řádky tabulky (v uvedeném příkladu například tabulka vysledky (id uživatele | výsledek), hodnoty by byly pro uživatele 1 v osmi řádcích - například 1|15 1|5 1|20 atd.) # Různé druhy záznamů ve stejné tabulcePříznak problému: Tabulka má mnoho sloupců a pro jednotlivé záznamy mají smysl vždy jen některé z nich.Toto obvykle znamená, že máte v jedné tabulce dva (nebo více) druhů záznamů s různou strukturou. Do tabulek se sdružují záznamy stejného druhu (se stejnou strukturou), rozdělte tedy záznamy do více tabulek tak, aby v každé byl jen jeden druh záznamů. Trochu jiný, ale podobně vypadající, problém je, pokud např. z nějaké skupiny sloupců může být vyplněný jen jeden (např. sloupce volba1, volba2, volba3, přičemž každý záznam může mít jen jeden z nich). Řešení viz pojmenovávání sloupců s indexem. # Musíte pojmenovávat sloupce s indexem (jmeno1, jmeno2, jmeno3, …)Příklad: Jak v SQL vybrat z tabulky všechny sloupce, jejichž název začíná nějakým textem?Musíte-li pojmenovávat sloupce například vysledek1, vysledek2, vysledek3, …, je nejvyšší čas se zamyslet nad návrhem databáze. Obvykle totiž jde o jinou variantu chyby s více hodnotami ve sloupci. Čili s každým záznamem tabulky souvisí několik hodnot a potřebujete je někam uložit. Správné řešení je opět vytvoření druhé tabulky provázané s tou první, kde jednotlivé hodnoty budou v řádcích. U varianty, kdy může ze skupiny sloupců být vyplněn jen jeden (např. sloupce odpovedA, odpovedB, odpovedC a každý záznam má jednu volbu) lze sloupce nahradit jedním sloupcem výčtového typu (ENUM) Poznámka, analogický problém může být i s celými tabulkami. I to je příznak špatného návrhu databáze, viz následující bod. # Nová tabulka/sloupec pro každý záznamPříklad: Jak uložit údaje o každém uživateli do „jeho“ tabulky (uživatele A do tabulky A, uživatele B do tabulky B, atd.)?I toto je špatný návrh databáze. Do databázové tabulky se nesdružují různé druhý záznamů týkajících se téhož. Právě naopak: Do tabulky se sdružuje stejný druh záznamů (tj. záznamy se stejnou strukturou), obvykle o různých entitách. U v příkladu uvedené úvahy mohou nastat dvě situace: Buď všechny záznamy jednoho uživatele mají stejnou strukturu (a reprezentují třeba stavy uživatele v různém čase, v extrémním případě je v každé tabulce jen jeden záznam), pak rozprostření do mnoha tabulek nepřináší prakticky žádné výhody, naopak se s tím hůř pracuje (v případě, že je potřeba dělat výběry přes více uživatelů). Nebo záznamy uživatele mají různou strukturu, pak máte tento problém. Správné řešení je dělit záznamy do tabulek podle druhu (struktury) záznamu. Začátečníci by měli dodržovat následující jednoduché pravidlo: - Běžná činnost aplikace (např. přidávání, úprava, mazání záznamů uživatele) může vést ke změnám záznamů v databázi (v SQL INSERT, UPDATE, DELETE), ale neměla by měnit datový model (v SQL CREATE/DROP/ALTER TABLE # Recyklování primárních klíčů smazaných záznamůPříklad: Bývají dvě varianty tohoto:1. Když smažu nějaký záznam, jak zároveň snížit ID těch následujících, aby ID byla zase popořadě? 2. Smažu záznam z tabulky a pak vložím nový, jak udělat, aby se pro ten nový místo dalšího ID z řady použilo uvolněné ID toho smazaného? Tyto úvahy se někdy mohou zdát praktické, ale ve skutečnosti jdou špatným směrem. Pamatujte, že záznam je identifikován primárním klíčem. A tak se také používá při odkazování mezi tabulkami, aplikacemi, na webu i pro webové odkazy. Smažete-li záznam a jeho primární klíč přidělíte jinému záznamu, z pohledu teorie vlastně nejde o smazání a vytvoření záznamu, ale o změnu dat pořád téhož záznamu. Proto by primární klíč měl navždy být svázaný s jedním záznamem a nikdy by se neměl přidělit jinému záznamu. Obvyklé argumenty pro recyklaci IDček: • Nechci zbytečně vyčerpávat číselnou řadu - Datový typ BIGINT v MySQL dokáže uložit číslo vyšší než 18 trilionů (= 18 miliard miliard). Tolik záznamů nejspíš nikdy potřebovat nebudete. • Používám ID pro pořadí záznamu - to je špatně. Nepoužívejte ID jako pořadí záznamu. Udělejte na to zvláštní sloupec, ale většinou to ani není nutné, stačí jen seřadit podle nějakého už existujícího sloupce • S ID popořadě se dá snadno odkázat na následující a předchozí záznam - Na odkazování přes id-1 a id+1 nespoléhejte. Pořád musíte řešit první a poslední záznam, seřazení záznamů podle správného kriteria a celkově to nestojí za problémy, které recyklace primárních klíčů přinese. |
Toto téma je uzamčeno. Odpověď nelze zaslat.
0