MySQL a sql_mode vs gridy
- ondrej256
- Člen | 187
Ahoj,
už se mě vícekrát stalo, že jsem na nějakém projektu musel měnit sql_mode aby mně fungovalo zobrazení nějakého datagridu. Uvedu příklad sql dotazu, který používám v gridu:
SELECT customer_id, firstname, sum(amount) AS soucet_pohybu
FROM credit_move
GROUP BY customer_id
Tenhle dotaz ve striktnějším sql módu spadne, protože v selectu smím uvést pouze sloupec který je v GROUP BY nebo tam můžu dát agregační funkci (např. SUM), já tam mám uvedeny taky sloupec firstname, což není ani v group by ani to neni agregační funkce a tím pádem databáze takový dotaz neprovede. Takže nejjednoušší a zároveň asi nejméně správné řešení je nastavit prázdný sql_mode
set global sql_mode='';
set session sql_mode='';
Tím pádem mně všechny gridy fungují. No ale teď nevím jak bych to měl řešit správně.
Předpokládám, že z pohledu databázisty je správné řešení na sql_mode nesahat, vytáhnout si v tom dotazu jen idcko a soucet, následně dotaz obalit dalším dotazem kde provedu vytažení sloupce firstname a přidám k výsledku. Jenže to je při programování docela nepohodlný způsob, když jde jen změnit sql_mode a všechno funguje jak má.
Tak jsem se chtěl zeptat jak podobné problémy řešíte vy a a zda je velký hřích si upravit sql_mode na nějakou benevolentnější variantu
Editoval ondrej256 (17. 9. 2021 9:35)
- Michal Kumžák
- Člen | 106
Když se podíváš jaké módy máš nastavené, je mezi nimi sqlmode_only_full_group_by? Tipuji, že to dělá právě tento mód.
- Michal Kumžák
- Člen | 106
Natolik do toho nevidím, ale s bez tohoto módu se mi mysql standardně instaluje. Osobně bych si ten mód vypnul, protože je to dost restriktivní a hlavně omezující.
- SankaCoffee
- Člen | 8
Nevím jestli je to úplně OK řešení, ale v případě potřeby tento problém obcházím pomocí MAX(firstname). Databáze si na nic nestěžuje, získám co potřebuji a s tabulkami do milionu záznamů mi vše běhalo celkem svižně.
Editoval SankaCoffee (20. 9. 2021 10:41)
- Michal Kumžák
- Člen | 106
Jen se možná blbě zeptám, ten firstname máš v té samé tabulce co agreguješ? Pokud ne, tak pak bych možná pak upravil SQL dotaz asi nějak takto:
SELECT c.firstname, t.customer_id, t.soucet_pohybu
FROM customer AS c
LEFT JOIN (
SELECT customer_id, sum(amount) AS soucet_pohybu
FROM credit_move
GROUP BY customer_id) AS t ON t.customer_id=c.id
Tohle už ti ten mód pustí.
- Kamil Valenta
- Člen | 820
SankaCoffee napsal(a):
Nevím jestli je to úplně OK řešení, ale v případě potřeby tento problém obcházím pomocí MAX(firstname). Databáze si na nic nestěžuje, získám co potřebuji a s tabulkami do milionu záznamů mi vše běhalo celkem svižně.
Nebo ANY_VALUE(firstname), protože MAX() mi u nečíselných hodnot nikdy nedávalo smysl. Ale vlastně jsem nedělal test, co je rychlejší…
Editoval Kamil Valenta (20. 9. 2021 11:06)
- jiri.pudil
- Nette Blogger | 1032
ONLY_FULL_GROUP_BY chceš mít zapnutý, jako
je to ostatně by default od MySQL 5.7.5. Třeba v takovém PostgreSQL se
tohle chování ani vypnout nedá.
Pokud ti kvůli tomu padají dotazy v nějakém gridu, je to buďto bug toho
gridu, anebo špatně navržená databáze. Přiznám se, že taky moc nechápu,
proč je v tabulce credit_move nějaké křestní jméno.
Pokud tam totiž skutečně je, představ si, že máš v té tabulce takováhle data:
| customer_id | firstname | amount |
|---|---|---|
| 1 | Jan | 42 |
| 1 | Petr | 69 |
Pokud groupuješ jenom podle customer_id, pro hodnotu 1 máš
najednou dvě různé hodnoty firstname a databáze neví, kterou
z nich si má vybrat. Proto vyžaduje, abys groupoval i podle
firstname.
Pokud máš firstname v tabulce se zákazníky, jak naznačil Michal výše,
měl by ses obejít bez dalšího groupování i bez subquery. Pokud totiž
groupuješ podle primárního klíče (nebo třeba non-null unique indexu),
databáze si dokáže spočítat funkční závislosti a pozná, že pro každé
c.id může existovat jenom jedna unikátní hodnota
firstname:
SELECT c.id, c.firstname, SUM(t.amount)
FROM customer AS c
LEFT JOIN credit_move AS t ON c.id = t.customer_id
GROUP BY c.id