# MySQL a čeština

Pokud máte češtinu v databázi v pořádku, ale v php ne, většinou stačí po každém connectu zavolat 
mysql_query("set names utf8"); //(utf8 nahraďte za cp1250 pro stránky ve windows-1250 a latin2 pro iso-8859-2)
pro verze mysql od 5.0.7 a php od 5.2.3 je doporučováno použít 
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

Nejlepší je oficiální návod v angličtině http://dev.mysql.com/doc/refman/5.0/en/index.html
V č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 http://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 chybu

Warning: …(): supplied argument is not a valid MySQL result resource
Tato 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());
- tento kód vypíše chybovou hlášku mysql a pak pokračuje dále v provádění skriptu

Příklady řešení častých problémů


# Počet komentářů ke článku


select 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 mysql


Jde 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í" sloupce


set @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í duplicit


Vypsá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í duplicit


Nezapoměň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é vlastnosti


Hledá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
varianta se 2 parametry

# 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 sloupce

Pří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 sloupci

Pří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é tabulce

Pří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áznam

Pří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