Nacházíte se:  MySQL » MySQL povinné hodnoty, NULL hodnoty, defaultní hodnoty, prázdné hodnoty a jak by se mělo chovat ORM

Článek v rubrikách:

»PHP
»MySQL

03.12.2009 20:22 - Autor: Roman Matěna

Sdílet

MySQL povinné hodnoty, NULL hodnoty, defaultní hodnoty, prázdné hodnoty a jak by se mělo chovat ORM

Víte jak se chová MySQL k prázdným hodnotám, povinným sloupcům, nenulovým sloupcům, nezadaným hodnotám v insertu apod.? A jak by se měla chovat ORM vrstva? Co by měla kontrolovat před samotným dotazem do databáze, jak moc by měla být abstraktní vůči typu databáze?

Pokud se tady mluví o ORM myslí se tím spíš active records. Nechci řešit něco jako Doctrine a vícekrát jsem se zmínil, že podobným systémům nevěřím hlavně z hlediska optimalizace.

K tématu článku si odpovím hned na první dotaz: Jak moc by měla být ORM vrstva abstraktní vůči typu databáze? Ač se mnou nebude možná řada z Vás souhlasit, tvrdím že ORM (active records vrstva) by měla být maximálně v co možné míře stavěna na konrétní databázi, tedy pokud programuji v mysql tak využiji všech proprietárníh řešeních, které nabízí jako např. SQL_CALC_FOUND_ROWS apod.

MySQL však má spousty dalších specialit a to například způsob jakým nakládá s defaultními, nullovými nebo povinnými hodnotami a jak se k tomu staví pokud v insert dotazu chybí apod. Pro tyto účely jsem si připravil sadu testů, které ilustrují a snad pomohou pochopit nebo ujasnit mechanismy fungování ohledně této věci.

Testovací tabulka

Základem je testovací tabulka, vytvoříme si ji tímto sql dotazem:

1. CREATE TABLE IF NOT EXISTS `test` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `test_mandatory_varchar` varchar(100) COLLATE utf8_czech_ci NOT NULL,
     `test_mandatory_text` text COLLATE utf8_czech_ci NOT NULL,
     `test_mandatory_int` int(11) NOT NULL,
     `test_notmandatory_varchar` varchar(100) COLLATE utf8_czech_ci DEFAULT NULL,
     `test_notmandatory_text` text COLLATE utf8_czech_ci,
     `test_notmandatory_int` int(11) DEFAULT NULL,
     `test_default_varchar` varchar(100) COLLATE utf8_czech_ci NOT NULL DEFAULT 'DEFAUT HELLO!',
     `test_default_int` int(11) NOT NULL DEFAULT '1000',
     `test_nulldefault_varchar` varchar(100) COLLATE utf8_czech_ci DEFAULT 'DEFAULT HELLO!',
     `test_nulldefault_int` int(11) DEFAULT '1000',
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

Výsledek je na tomto obrázku test-tabulka.png. Tabulka nemá defaultní sloupce pro text, datový typ TEXT/BLOB nemůže mít natavený default.

Nyní bude následovat série sql dotazů, které se pokouší vkládat data do tabulky za různých okolností, které mohou nastat zejména s použitím nějakého vlastního mapování objektů v databázi.

Výsledek všech dotazů najdete na tomto obrázku test-vysledek.png, co k těmto výsledkům vedlo sledujte v následujících kapitolách.

1. Korektní dotaz podle očekávání (vše se chová jak má)

1. INSERT INTO test
2. (id, test_mandatory_varchar, test_mandatory_text, test_mandatory_int, test_notmandatory_varchar, test_notmandatory_text, test_notmandatory_int, test_default_varchar, test_default_int, test_nulldefault_varchar, test_nulldefault_int)
3. VALUES
4. (1, 'SAY HELLO!', 'SAY HELLO!', 1, null, null, null, 'SAY HELLO!', 1, 'SAY HELLO!', 1);

2. Sloupce s defaultními hodnotami zadané jako NULL

1. INSERT INTO test
2. (id, test_mandatory_varchar, test_mandatory_text, test_mandatory_int, test_notmandatory_varchar, test_notmandatory_text, test_notmandatory_int, test_default_varchar, test_default_int, test_nulldefault_varchar, test_nulldefault_int)
3. VALUES
4. (2, 'SAY HELLO!', 'SAY HELLO!', 1, null, null, null, null, null, null, null);

Tak tohle zařve na sloupcích test_default_varchar a test_default_int, sloupce nesmí obsahovat null hodnotu o což se pokoušíme. #1048 - Column 'test_default_varchar' cannot be null.

3. Sloupce s defaultními hodnotami nejsou vůbec zadané

1. INSERT INTO test
2. (id, test_mandatory_varchar, test_mandatory_text, test_mandatory_int, test_notmandatory_varchar, test_notmandatory_text, test_notmandatory_int)
3. VALUES
4. (3, 'SAY HELLO!', 'SAY HELLO!', 1, null, null, null);

4. Sloupce s defaultními hodnotami zadané jako prázdný řetězec

1. INSERT INTO test
2. (id, test_mandatory_varchar, test_mandatory_text, test_mandatory_int, test_notmandatory_varchar, test_notmandatory_text, test_notmandatory_int, test_default_varchar, test_default_int, test_nulldefault_varchar, test_nulldefault_int)
3. VALUES
4. (4, 'SAY HELLO!', 'SAY HELLO!', 1, null, null, null, "", "", "", "");

5. Nepovinné (NULL) hodnoty zadané jako NULL (výchozí NULL)

1. INSERT INTO test
2. (id, test_mandatory_varchar, test_mandatory_text, test_mandatory_int, test_notmandatory_varchar, test_notmandatory_text, test_notmandatory_int, test_default_varchar, test_default_int, test_nulldefault_varchar, test_nulldefault_int)
3. VALUES
4. (5, 'SAY HELLO!', 'SAY HELLO!', 1, null, null, null, 'SAY HELLO!', 1, 'SAY HELLO!', 1);

6. Nepovinné (NULL) hodnoty nejsou vůbec zadané

(výchozí NULL)

1. INSERT INTO test
2. (id, test_mandatory_varchar, test_mandatory_text, test_mandatory_int, test_default_varchar, test_default_int, test_nulldefault_varchar, test_nulldefault_int)
3. VALUES
4. (6, 'SAY HELLO!', 'SAY HELLO!', 1, 'SAY HELLO!', 1, 'SAY HELLO!', 1);

7. Nepovinné (NULL) hodnoty zadané jako prázdný řetězec

(výchozí NULL)

1. INSERT INTO test
2. (id, test_mandatory_varchar, test_mandatory_text, test_mandatory_int, test_notmandatory_varchar, test_notmandatory_text, test_notmandatory_int, test_default_varchar, test_default_int, test_nulldefault_varchar, test_nulldefault_int)
3. VALUES
4. (7, 'SAY HELLO!', 'SAY HELLO!', 1, "", "", "", 'SAY HELLO!', 1, 'SAY HELLO!', 1);

8. Povinné (NOT NULL) hodnoty zadané jako NULL (není výchozí hodnota)

1. INSERT INTO test
2. (id, test_mandatory_varchar, test_mandatory_text, test_mandatory_int, test_notmandatory_varchar, test_notmandatory_text, test_notmandatory_int, test_default_varchar, test_default_int, test_nulldefault_varchar, test_nulldefault_int)
3. VALUES
4. (8, null, null, null, null, null, null, 'SAY HELLO!', 1, 'SAY HELLO!', 1);

Pochopitelně to opět zařve #1048 - Column 'test_mandatory_varchar' cannot be null.

9. Povinné (NOT NULL) hodnoty nejsou vůbec zadané

(není výchozí hodnota)

1. INSERT INTO test
2. (id, test_notmandatory_varchar, test_notmandatory_text, test_notmandatory_int, test_default_varchar, test_default_int, test_nulldefault_varchar, test_nulldefault_int)
3. VALUES
4. (9, null, null, null, 'SAY HELLO!', 1, 'SAY HELLO!', 1);

10. Povinné (NOT NULL) hodnoty zadané jako prázdný řetězec

(není výchozí hodnota)

1. INSERT INTO test
2. (id, test_mandatory_varchar, test_mandatory_text, test_mandatory_int, test_notmandatory_varchar, test_notmandatory_text, test_notmandatory_int, test_default_varchar, test_default_int, test_nulldefault_varchar, test_nulldefault_int)
3. VALUES
4. (10, "", "", "", null, null, null, 'SAY HELLO!', 1, 'SAY HELLO!', 1);

Závěrem

Při stavbě aplikace je potřeba si uvědomit s jakými typy hodnot potřebujeme pracovat.

1. Povinná hodnota

Povinná hodnota je naprosto legitimní potřeba a jak se zdá na úrovni mysql toto omezení nelze zajistit! Když například potřebujeme evidovat zákazníky nebo produkty, tak název produktu nebo jméno zákazníka je hodnota bez které data nemají význam a musí být zadána nezávisle na okolnostech. V mysql můžeme pouze nastavit, že hodnota nesmí být typu NULL a že neexistuje výchozí (defaultní) zadání, což lze chápat jako, že hodnota je povinná.

Nicméně příklad 9 ilustruje, že pokud do insert dotazu tyto sloupce vůbec nezadáme, pak se vloží jako prázdný údaj, není vyhozena žádná chyba, ale výsledek je stejný jako v případě vložení prázdných řetězců. Pravděpodobně záleží na nastavení modu mysql, existuje i jakýsi strict mode, nicméně nemám s ním zkušenosti a je otázka jestli s takovým nastavením můžeme manipulovat nebo dokonce chceme.

Tato chyba se typicky vyskytne v případě pokud se snažíme o nějaký způsob mapování objektů na databázi, tyto objekty obvykle generují sql dotazy podle toho, co bylo objektu nastaveno.

1. $customer=new Customer();
2. $customer->name="MY NAME";
3. $customer->insert();

1. Pokud, tedy programujeme vlastní ORM nebo jakoukoli vlastní podobu něčeho takového, měl by tento objekt sledovat při ukládání (updatu), zda se jedná o MANDATORY vlastnost (name), zda se jedná o vlastnost s povolenou NULL hodnotou? Znamená MANDATORY vlastnost, že NULL není relevantní?

2. Jak tedy nastavovat vlastnosti takových objektů? Měli bychom nastavit tři hodnoty? Mandatory, nullable, notempty?

Souvisí to s rozšiřováním tabulky v mysql, pokud je hodnota povinná a nesmí být nullová, pak by nebylo možné klasickým způsobem tabulku v mysql rozšířit o takový sloupec. Pokud tabulka je naplněna daty a chtěli bychom takový sloupec přidat, je nutné mu nastavit defaultní hodnotu (mysql si pomáhá prázdnou hodnotou), kterou budou všechny řádky tohoto nového sloupce vyplněny, aby byla data v tabulce stále konzistentní.

2. Defaultní hodnota

V případě, že sloupce se insertu vůbec neúčastní, pak se použije defaultní hodnota.

1. Je tedy potřeba, aby objekt v ORM vracel defaultní hodnotu? Nebo to necháme na databázi? S objektem je možné pracovat dále, chceme, aby např. funkce __toArray vrátila i defaultní hodnoty? Pokud je vlastnost prázdná, asi by měla zůstat prázdná a defaultní hodnota nás nezajímá. Má tedy defaultní hodnota v ORM vůbec smysl? Pokud není vlastnost vyplněna mělo by to hodit vyjímku nebo defaultní hodnotu?

Jak bude vypadat nastavení takové vlastnosti objektu?

1. $this->hasColumn("name", "%s", 255, array('mandatory'=>true, 'null'=>false, 'empty'=>false));
2. $this->hasColumn("isCustomer", "%b", 1, array('default'=>1, 'null'=>false));
3. $this->hasColumn("adminNote", "%s", 255, array('null'=>true, 'empty'=>true));

Vzniklo tady pár dotazů, co vy na to? Nebude validace na straně objektů zbytečně příliš náročná? Chceme mít možnost ji vypínat? Jak tedy pracovat s prázdnými hodnotami, defaultními nebo povinnými?

komentáře

 RSS Komentáře

• Hlavní stránka
• O weblogu
• PHP
• Regulární výrazy
• Javascript
• MySQL
• MSSQL
• Dibi
• Nette
• E-business
• XML
• HTML
• Linux
• Ostatní
• Smetí
• To musíš znát!
• Odkazy
• Facebook