Vychytávky v MySQL (diskuse)

[Přemek Vyhnal]

Příloha:

• mysql.pdf (103055 bytů)

Zápisek s lepším formátováním v PDF v příloze.

[Daniel Kvasnička ml.]

Yep, foreign keys jsou super. Usetri to plno nudneho kodu v samotne aplikaci. To i ten fulltext ozelim.

[Messa]

Škoda, že foreign keys nejdou deklarovat rovnou u deklarace sloupce v create table, např. "CREATE TABLE osoba (id INT PRIMARY KEY); CREATE TABLE pes (id INT PRIMARY KEY, majitel INT REFERENCES osoba);". V PostgreSQL to funguje, ale MySQL to nejen že neumí, ale ani se nijak netváří, že to neumí. Tak se taky nespalte

[default]

Všechny databáze, které používám, toto umí, ale nepoužívám to. Důvod je jednoduchý: když máte dvě tabulky, které jsou na sobě závislé, nelze je takto vytvořit sekvenčně. Vždy se u jedné musí udělat dodatečný ALTER TABLE … ADD CONSTRAINT…. Takže pro jednoduchost a přehlednost dělám vše přes ALTER TABLE a žiju si spokojeně. I pro sestavování deployment skriptů a jejich správu je toto řešení pro mě lepší

[kaja47]

leda vypnot kontrolu závislosí na začítku skrtipu pomocí SET FOREIGN_KEY_CHECKS = 0; a na konci jí zase zapnout, to by pak mohlo jít.

[Kyosuke]

Zajímavé, jak jsou někteří lidé selektivní. Stěžují si na to, že se nikdo nestará o Python psaný v Javě, ale o cizích klíčích, bez kterých podle doktora Codda databáze vůbec nemá nárok říkat si RDBMS, prohlásí jen to, že "jsou super a ušetří spoustu nudného kódu", a určitě by se neštítil používat ji i bez nich, jako by se nic nedělo.

[Luk]

No, někteří lidé se ani na úroveň cizích klíčů nedostanou - jako třeba tvůrce "databáze" popsané v části Kalvárie údržby kódu

[Dalibor Smolík]

Jo. Cizí klíče - občas se to hodí . Někdy potřebuji zrušit fakturu, která se skládá z tabulky pro společné údaje a z tabulky pro zboží, řešil jsem to skriptem PHP, který nejprve provede výmaz v tabulce pro zboží a pak v tabulce pro společné údaje, když jsem zjistil, že se to dá provést mnohem elegantněji

[Michal Kubeček]

Tohle není ani tak záležitost foreign keys, ale spíš toho, že si napíšete proceduru nebo trigger nebo máte v jazyce vychytávky typu 'on delete cascade' (což vlastně v podstatě je zase jen trigger). Smysl foreign keys je v tom, že engine sám se stará o to, aby se vám v databázi z jakéhokoli důvodu (třeba chybně napsané klientské aplikace) neobjevily položky bez faktury.

[Dalibor Smolík]

Jasně, díky za osvětlení - u mně se položky bez faktury nemohou vyskytnout, protože v tabulce fakturace - zboží je jedno pole s uvedením čísla faktury, stejné pole je i v tabulce pro společné údaje pro fakturu. Když v této tabulce pro společné údaje zruším fakturu, měly by zmizet automaticky všechny řádky s uvedením tohoto čísla faktury ve druhé tabulce. A to by foreign key mohl řešit (stejně tak asi i transakce) .. Jak jsem psal, mám to řešené pouze systémem příkazů ve skriptu PHP.

[happy barney]

práveže v db bez foreign keys, tak taký záznam (omylom) existovať môže. stačí jednoduchý update či chyba v skripte (v prípade php a mysql môže byť ten skript aj správny)

[Michal Kubeček]

Tohle řeší spíš ten trigger (nebo 'on delete cascade'). Smysl foreign key je hlavně v tom, že vám tam ty položky odkazující na neexistující fakturu nemohou vzniknout ani v případě, že se je tam úmyslně pokusíte vytvořit (pokud nebudete hodně důmyslný a ten constraint nevypnete). Tedy ani ne tak vy, ale hlavně chybně napsaná klientská aplikace. Na takové narušení konzistence dat v databázi se totiž může přijít až po delší době a pak už je často extrémně těžké (až nemožné) to dát dohromady.

[Michal Kubeček]

Vždycky jsem si říkal, jaké jsem měl ohromné štěstí, že první databáze, kterou jsem potkal, nebyla MySQL. :-)

[Kyosuke]

Myslím, že u mě to byl Informix, ale ne ze strany uživatele. Jako uživatel jsem kouknul na MySQL, ale po spatření textů typu "transakce nepotřebujeme, ale zato jsme brutálně rychlí, a vůbec, atomické updaty řádků většině lidí stačí a zbytek se ošetří v aplikaci" spojených s naprosto otřesným překladem jsem odhodil jak onu knihu, tak i MySQL a nakonec objevil Firebird 1.0.3 a knihu Pavla Císaře. (A všechno završil tím, že jsem si sedl a popovídal s Jimem Starkeym u piva, když byl v Praze. )

[Ladicek]

"transakce nepotřebujeme, ale zato jsme brutálně rychlí, a vůbec, atomické updaty řádků většině lidí stačí a zbytek se ošetří v aplikaci"

Tu knihu psal někdo od eBaye?

[Kyosuke]

Nějaký střelený Polák. Tohle to bylo. Tehdy jsem byl ještě mladej a blbej (dneska už jsem jenom blbej ), tak jsem nevěděl, že 1) kupovat překlady a 2) kupovat knihy typu "Naučte se X za Y dní" (případně až extrémy typu "Naučte se SAP R/3 za patnáct minut" ) je fakt spíš ruská ruleta než promyšlená koupě. Teď už kupuju jen hodnotnou literaturu.

[deda.jabko]

foxpro a debefka s indexy na tebe! to by sis pak jinak vazil mysql! ;-]

btw. muj prvni db-stroj se kterym jsem valcil byl interbase... az se divim, co vsechno s tim slo pred deseti lety delat a co z toho uz jde i v mysql

[Kyosuke]

Spíš tak před patnácti, ne? Nebo aspoň Interbase před deseti lety a Interbase před patnácti mi přišly historicky tak nějak skoro stejné, on se Borland nijak zvlášť ve vývoji nepřetrhl, aspoň pokud je mi známo.

[deda.jabko]

no, tak to nedokazu posoudit, protoze pred patnacti lety jsem jeste mastil programy na osmibitech. ale pred deseti lety uz jsem mastil programy v delphi a s interbaskou.

[Michal Kubeček]

Nejen vývoj, ale i marketing… Skoro bych řekl, že se Interbase snažili nechat tiše upadnout v zapomnění a o kvalitě projektu svědčí fakt, že se jim to přes veškerou snahu nepodařilo. Teď sice zase nějaký vývoj probíhá, ale připadá mi, že je to spíš natruc Firebirdu než ze zájmu o produkt samotný.

Ale co čekat od firmy, která každých pár let změní název a ještě častěji strategii a vytrvale se snaží vrhat energii jakýmkoli směrem kromě toho, v čem se jim daří?

[Michal Kubeček]

foxpro a debefka s indexy na tebe! to by sis pak jinak vazil mysql! ;-]

No, bereme-li to až takhle, tak první byla dBase III. Ale měl jsem na mysli spíš Interbase 6.0 beta.

interbase... az se divim, co vsechno s tim slo pred deseti lety delat a co z toho uz jde i v mysql

Ve skutečnosti je to ještě horší - spousta z těch věcí, které se v MySQL objevily s velkou slávou v posledních letech, byla v Interbase (která se tehdy ještě nejmenovala Interbase) před více než dvaceti lety…

[Kyosuke]

To by sedlo, Hot Backup v MySQL teprv bude (možná ), pokud vím, a v Interbase zaručeně fungoval za plného chodu už v roce 1988 ve verzi 3 nebo tak nějak.

Což mi připomnělo i tohle - aneb co uměla (a kde byla nasazována) Interbase v době, kdy autoři MySQL teprv zkusmo lepili cizí SQL frontend na první vývojovou verzi vlastního ISAM backendu. Skoro mi přijde, že začátky obou systémů docela pěkně odrážejí celé jejich další směrování. Přičemž u Interbase na začátku nebyl čtyřicet let starý (a zastaralý) ISAM, ale v té době revoluční verzovací transakční engine, který pak spousta lidí sprostě (a špatně ) vobšlehla, včetně Oraclu ("nefíčury" Oraclu před rokem 1992 jsou kapitola sama pro sebe ), PostgreSQL a MS SQL Serveru 2005. (A ano, jsem Smug Firebird Weenie a ještě to drze přiznávám. )

[deda.jabko]

v dBase III+ jsem jeste neco delal na skole, ale bylo to silene, ale ve foxpro jsem delal skutecne programy... a byl to zazitek... nejvetsi sranda je, ze jeste dneska se daji na spouste mist najit programy napsane ve foxpro 2.x...

[default]

Já si to říkám i teď

[Robert Krátký]

Šikovný zápisek, díky. K odkazům doplňuji:

Seriál: Tvorba databází v MySQL a Seriál: Správa databází v MySQL.

[Luk]

Při použití InnoDB je transakce každý samostatný příkaz. Tzn. okolo transakce složené z jednoho příkazu není třeba psát START TRANSACTION a COMMIT.

Tohle platí jen v případě, že je zapnutý autocommit (výchozí stav). Lze ovšem vypnout (SET AUTOCOMMIT=0) a pak se musí každá transakce zahájit a ukončit. Pokud se při zapnutém autocommitu zavolá START TRANSACTION, autocommit se do dokončení této transakce vypne (pak se zase sám zapne).

Během provádění transakce všechny ostatní pokusy o práci nad stejnými daty čekají.

Opět platí jen částečně. Do jaké míry bude přístup k datům zamykán, záleží na nastavení izolace transakcí.

[Robo]

S MySQL som moc casto nerobil. Tak mam dve otazky:
1. Ak defaultne zadam nejaky UPDATE statement, rovno ho commitne?
2. Ak je autocommit vypnuty a zadam UPDATE statement, hodi chybu, ze chyba zaciatok transakcie alebo transakciu zacne automaticky (ako napr. Oracle) a potom ju staci commitnut?

este jedna otazka, ak jeden user update-uje tabulku, lockne sa cela tabulka alebo iba dane riadky?

[Kyosuke]

K tomu poslednímu poslední: U MyISAM jedině celá, u BDB enginu stránky, u InnoDB jen dané řádky, jestli se nepletu. A optimistické zamykání asi v nedohlednu.

[Luk]

1. Ak defaultne zadam nejaky UPDATE statement, rovno ho commitne?

Ano. Možná to z té věty nebylo dostatečně zřejmé

2. Ak je autocommit vypnuty a zadam UPDATE statement, hodi chybu, ze chyba zaciatok transakcie alebo transakciu zacne automaticky (ako napr. Oracle) a potom ju staci commitnut?

Za b) je správně - začne transakci automaticky. Při použití je ale velkou chybou na to spoléhat, velmi snadno to totiž vede k tomu, že se pak člověk diví, proč mu něco nefunguje (když neví, jestli jestli je zrovna autocommit zapnutý). Když se pracuje s transakcemi, vždy je dobré používat START TRANSACTION, ať je autocommit zapnutý nebo ne.

este jedna otazka, ak jeden user update-uje tabulku, lockne sa cela tabulka alebo iba dane riadky?

Zamykání závisí na úrovni izolace (např. při READ UNCOMMITED se pro čtení nezamyká vůbec - ostatní uživatelé mohou tedy dostat data, která následně nebudou potvrzena). Celá tabulka se ale nezamyká nikdy, pokud se nezamkne explicitně. Explicitní zamykání celé tabulky ale není kompatibilní s transakcemi. Pokud se dá LOCK TABLES, automaticky to způsobí COMMIT. Naopak příkaz START TRANSACTION automaticky vyvolá UNLOCK TABLES.

[Luk]

To o tom zamykání vztahuji samozřejmě k InnoDB, pro ostatní enginy (bez transakcí) to už popsal Kyosuke (a asi správně).

[hikikomori82]

To je sice pekne, ale ked ide o nasadenie v realnom prostredi, treba zrazu riesit rozdielne verzie db, prava, uzivatelov a podobne sracky, prave dnes som musel jeden before update trigger zrusit a do kodu rucne doplnit co ten trigger robil lebo to neslo, myslim ze sa do buducna tomuto vyhnem a necham to KISS (keep it simple, stupid).

[deda.jabko]

co jsem všechno nevěděl a nikdy mi to nechybělo

a to jste predtim mysql i nejak pouzival?

[Přemek Vyhnal]

a to jste predtim mysql i nejak pouzival?

ano, spokojeně ;)

[deda.jabko]

:-]] zrejme slusny oddil...

[xkucf03]

Taky jsem takhle začínal - asi v patnácti jsem vzal MySQL a v phpMyAdminovi si naklikal nějaké ty tabulky, SQL (tehdy jen selekty) jsem se učil za chodu, z DB jsem prakticky nic nevyužíval, všechno bylo v aplikační vrstvě. A jak pěkně mi to fungovalo

Dneska si bez cizích klíčů, transakcí a spojování tabulek nedokážu práci s (relační) databází představit.

Výcuc je celkem fajn, třeba někomu pomůže, aby nezačínal tak blbě jako já, takže díky za něj.

[JohnnyDoe]

InnoDB – transakce, cizí klíče, neumí fulltext (a nebo už ano?)

Je zde někdo znalý, kdo ví, jaký je současný/budoucí stav fulltextu v InnoDB? Nebo pořád platí ten trik s paralelním vytvářením dvou tabulek se stejnými daty, ale s jednou InnoDB a na fultext s druhou MyISAM?

[Luk]

Je zde někdo znalý, kdo ví, jaký je současný/budoucí stav fulltextu v InnoDB?

Vypadá to špatně. Už to tam hnije skoro 3 roky a zatím nikdo nedal na vědomí, že by se to pohnulo kupředu. Ale to není ojedinělé - dlouho tam zahnívají i velmi záludné a ošklivé chyby (jako třeba tato, která zřejmě způsobuje poškození haldy - u verze 4 měla za následek zátuh serveru, verze 5 se při jejím výskytu aspoň sama restartuje).

Nebo pořád platí ten trik s paralelním vytvářením dvou tabulek se stejnými daty, ale s jednou InnoDB a na fultext s druhou MyISAM?

Ano.

[jenda]

v budoucnu, az bude zpracovani dat neco obycejneho jako strojni soucasti, prvky obvodu nebo technicka zarizeni budov, budou studenti konfrontovani s technickymi a historickymi otazkami z oblasti zpracovani informaci:

- vyjmenujte situace v provozu zpracovani dat, kdy jsou tzv. cizi klice prekazkou

- vyjmenujte situace, ve kterych je povoleno pouziti cizich klicu pres jejich nesporne nevyhody

- z historie je znama profese 'administrator rel. db. systemu'. Vyjmenujte nektere z tehdejsich nesmyslnych pracovnich cinnosti teto drivejsi profese a zduvodnete, co vedlo k jejimu zaniku.

- predstavte si, ze by jste meli v minulosti sabotovat zavedeni nejakeho informacniho systemu. Zduvodnete, ktera z uvedenych metod by byla tenkrat vyhodnejsi: extenzivni vyuziti cizich klicu se soucasnym loadem dat z tabulkoveho procesoru nebo vyuziti komplikovanch union-selectu s max. isolation-level

- vysvetlete, proc v drivejsich dobach se vyskytovaly na internetu stamiliony dotazu ohledne chyb v select-statementech i kdyz tyto jsa odvozeny z lidske reci by mely byt pro uzivatele 'prirozene' pouzitelne.

- jmenujte rok prvni pgsql-mysql valky a vyjmenujte hlavni argumenty myisam frakce.

[pavlix]

Aneb... jak se z MySQL snaží udělat velkou databázi a implementovat alespoň částečně věci, které jiné umějí už dávno (včetně třeba opensource PostgreSQL).

[Luk]

Ovšem původnímu tvůrci se to nelíbí a udělal odlehčený fork

[Kyosuke]

Já nevím, ale proč "velkou databázi"? Tyhlety věci už před dvaceti lety uměly i malé databáze. A to i včetně takových fičurek, jako je zabalení změn schématu databáze do transakcí a líných aktualizací fyzických řádků. (MySQL prý udělá novou kopii tabulky a dokud ji celou nepředělá, tak se do toho nedá zapisovat. Plus nějaké to místo na disku navíc, že? Ale kdo ví, když se kucí švédský sluníčkový budou hodně snažit, tak se do roku 1985 třeba brzy dostanou taky... )

[pavlix]

Třeba jo :). Ale nedej bože, aby pak z PHP udělali programovací jazyk.

[default]

SELECT * FROM lidi WHERE mesto IN (SELECT mesto FROM mesta)

A funguje už

SELECT
    *
FROM
    table1 t1 INNER JOIN (SELECT
            tx.col1, tx.col2
        FROM
            table2 tx) t2
    ON (t1.colx = t2.col2)
/

nebo se ještě pořád musí jezdit do serverovny mačkat tlačítko Reset?

[Luk]

nebo se ještě pořád musí jezdit do serverovny mačkat tlačítko Reset?

Každopádně u verze 5 platí, že když se něco podělá, tak se to samo restartuje - narozdíl od verze 4, která se sekla (např. kvůli této chybě).