Jak vyřešit chaos s prázdnými řetězci a NULL hodnotami v MySQL?

Znáte to – vytvoříte dotaz WHERE street = '', ale systém nevrátí všechny záznamy, které byste čekali. Nebo vám nefunguje LEFT JOIN tak, jak má. Důvodem je častý problém v databázích: nekonzistentní používání prázdných řetězců a NULL hodnot. Pojďme si ukázat, jak tento chaos vyřešit jednou provždy.

Kdy použít NULL a kdy prázdný řetězec?

Teoreticky je rozdíl jasný: NULL znamená „hodnota není zadaná“, zatímco prázdný řetězec znamená „hodnota je zadaná a je prázdná“. Podívejme se na reálný příklad z e-shopu, kde máme tabulku objednávek. Každá objednávka má povinnou dodací adresu a volitelnou fakturační adresu pro případ, že zákazník chce fakturovat na jiné místo (typické zatržítko „Fakturovat na jinou adresu“):

CREATE TABLE orders (
    id INT PRIMARY KEY,
    delivery_street VARCHAR(255) NOT NULL,
    delivery_city VARCHAR(255) NOT NULL,
    billing_street VARCHAR(255) NULL,
    billing_city VARCHAR(255) NULL
);

Pole billing_city a billing_street jsou nullable, protože fakturační adresa nemusí být vyplněná. Ale je mezi nimi rozdíl. Zatímco ulice může být legitimně prázdná (obce bez ulic), nebo nezadaná (použije se dodací adresa), město musí být vždy vyplněné, pokud je fakturační adresa použita. Buď tedy billing_city obsahuje název města, nebo je NULL – v tomto případě se použije dodací adresa.

Realita velkých databází

V praxi ale často dochází k tomu, že se v databázi začnou míchat oba přístupy. Příčin může být několik:

Změny v aplikační logice v průběhu času (např. přechod z jednoho ORM na jiné)
Různé týmy nebo programátoři používající různé konvence
Buggy migrace dat při slučování databází
Legacy kód, který se chová jinak než nový
Chyby v aplikaci, které občas propustí prázdný řetězec místo NULL nebo naopak

Tohle vede k situacím, kdy máme v databázi mix hodnot a musíme psát složité podmínky:

SELECT * FROM tbl
WHERE foo = '' OR foo IS NULL;

Daleko horší je, že NULL se chová neintuitivně při porovnání:

SELECT * FROM tbl WHERE foo = ''; -- nezahrne NULL
SELECT * FROM tbl WHERE foo <> ''; -- taky nezahrne NULL

-- musíme použít
SELECT * FROM tbl WHERE foo IS NULL;
SELECT * FROM tbl WHERE foo <=> NULL;

Tato nekonzistence v chování porovnávacích operátorů je další důvod, proč je výhodnější používat v databázi jen jeden způsob reprezentace prázdné hodnoty.

Proč se vyhnout dvojímu přístupu

Podobná situace jako v MySQL existuje i v JavaScriptu, kde máme null a undefined. Po letech zkušeností mnoho JavaScript vývojářů dospělo k závěru, že rozlišování mezi těmito dvěma stavy přináší víc problémů než užitku a raději se rozhodli používat pouze systémově nativní undefined.

V databázovém světě je situace podobná. Místo toho, abychom stále řešili, jestli něco je prázdný řetězec nebo NULL, je často jednodušší zvolit jeden přístup a toho se držet. Například databáze Oracle prázdné řetězce a NULL hodnoty v podstatě ztotožňuje, čímž tento problém elegantně obchází. Je to jedno z míst, kde se Oracle odchyluje od SQL standardu, ale zároveň tím zjednodušuje práci s prázdnými/NULL hodnotami.

Jak něčeho podobného dosáhnout v MySQL?

Co vlastně chceme vynutit?

U povinných polí (NOT NULL) chceme vynutit, aby vždy obsahovala smysluplnou hodnotu. Tedy zabránit vložení prázdného řetězce (nebo řetězce obsahujícího pouze mezery)
U volitelných polí (NULL) chceme zabránit ukládání prázdných řetězců. Když je pole volitelné, měl by být NULL jedinou reprezentací „nevyplněné hodnoty“. Míchání obou přístupů v jednom sloupci vede k problémům s dotazováním a JOIN operacemi, které jsme si ukázali výše.

Řešení v MySQL

V MySQL dávalo historicky smysl naopak používat výhradně prázdné řetězce ('') místo NULL hodnot. Byl to totiž jediný přístup, který šlo vynutit pomocí NOT NULL constraintu. Pokud jsme chtěli automaticky konzistentní databázi, byla to jediná cesta.

Existuje ale jeden důležitý případ, kdy tento přístup selže – když potřebujeme nad sloupcem unikátní index. MySQL totiž považuje více prázdných řetězců za stejné hodnoty, zatímco více NULL hodnot za různé:

Nicméně od MySQL verze 8.0.16 můžeme použít CHECK constraint a mít tak větší kontrolu nad tím, jaké hodnoty povolíme. Můžeme například vynutit, že sloupec bude buď NULL, nebo bude obsahovat neprázdný řetězec:

CREATE TABLE users (
    id INT PRIMARY KEY,

    -- Povinné pole - musí obsahovat nějaký neprázdný text
    email VARCHAR(255) NOT NULL UNIQUE
        CONSTRAINT email_not_empty      -- název pravidla
        CHECK (email != ''),

    -- Nepovinné pole - buď NULL nebo neprázdný text
    nickname VARCHAR(255)
        CONSTRAINT nickname_not_empty
        CHECK (nickname IS NULL OR nickname != '')
);

Při vytváření CHECK constraintu je důležité dát mu smysluplný název pomocí klíčového slova CONSTRAINT. Díky tomu dostaneme v případě porušení pravidla srozumitelnou chybovou hlášku Check constraint ‚nickname_not_empty‘ is violated místo obecného oznámení o porušení constraintu. To výrazně usnadňuje debugging a údržbu aplikace.

Problém jsou nejen prázdné řetězce, ale i řetězce obsahující pouze mezery. Řešení pomocí CHECK constraintu můžeme vylepšit použitím funkce TRIM:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
        CONSTRAINT email_not_empty
        CHECK (TRIM(email) != ''),
   ...
);

Nyní neprojdou ani tyto pokusy o obejití validace:

INSERT INTO users (email) VALUES ('   ');  -- samé mezery

Praktické řešení v Nette Framework

Konzistentní přístup k prázdným hodnotám je potřeba řešit i na úrovni aplikace. Pokud používáte Nette Framework, můžete využít elegantní řešení pomocí metody setNullable():

$form = new Form;
$form->addText('billing_street')
    ->setNullable(); // prázdný input se transformuje na NULL

Doporučení pro praxi

Na začátku projektu se rozhodněte pro jeden přístup:
- Buď používejte pouze NULL pro chybějící hodnoty
- Nebo pouze prázdné řetězce pro prázdné/chybějící hodnoty
Toto rozhodnutí zdokumentujte v dokumentaci projektu
Používejte CHECK constrainty pro vynucení konzistence
U existujících projektů:
- Proveďte audit současného stavu
- Připravte migrační skript pro sjednocení přístupu
- Nezapomeňte upravit aplikační logiku

Tímto přístupem se vyhnete mnoha problémům s porovnáváním, indexováním a JOIN operacemi, které vznikají při míchání NULL a prázdných řetězců. Vaše databáze bude konzistentnější a dotazy jednodušší.