MySQL – práce s hierarchickými daty

Relační databáze nejsou úplně ideální pro práci s hierarchicky uspořádanými daty, např. se seznamem kategorií a podkategorií, pokud je to možné, je lepší pro tyto data použít třeba XML. Pokud jinou možnost nemáme, tak se musíme smířit se složitějšími SQL dotazy a strukturou dat.

Vytvoříme si jednoduchou tabulku se seznamem kategorií a podkategorií:

CREATE TABLE `category` (
  `ID` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `Name` varchar(30) CHARACTER SET latin1 NOT NULL,
  `Parent` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

a naplníme ji daty. Sloupec parent slouží jako odkaz na hlavní kategorii, ty mají tuto hodnotu rovnou NULL.

+----+-------------------------+--------+
| ID | Name                    | Parent |
+----+-------------------------+--------+
|  1 | Kyseliny                |   NULL |
|  2 | Hydroxidy               |   NULL |
|  3 | Kyselina sírová         |      1 |
|  4 | Kyselina dusičná        |      1 |
|  5 | Kyselina chlorovodíková |      1 |
|  6 | Kyselina bromovodíková  |      1 |
|  7 | Hydroxid sodný          |      2 |
|  8 | Hydroxid vápenatý       |      2 |
|  9 | Hydroxid draselný       |      2 |
| 10 | Kyselina octová         |      1 |
| 11 | Kyselina propionová     |      1 |
| 12 | Hydroxid lithný         |      2 |
+----+-------------------------+--------+

Pokud potřebujeme vytvořit seznam kategorií a podkategorií, např. takovýto select:

Jednou z možností je pro každou kategorii použít zvláštní select, což u malého systému nemusí moc vadit, ale u větší dávky dat, už to může hodně zpomalovat. Druhou možností je využít složutější SQL dotaz s JOINem, který slouží ke spojování tabulek. V našem případě máme sice jen jednu tabulku, ale potřebujeme k ní přistupovat nadvakrát, nejprve vybrat jednotlivé kategorie a pak zjistit název nadřazené kategorie. Syntaxe příkazu je uvedena tady:

SELECT `t1`.`name` `l1`, `t2`.`name` `l2`
FROM `category` `t1`
LEFT JOIN `category` `t2` ON `t2`.`parent` = `t1`.`ID`
WHERE `t2`.`name` IS NOT NULL
ORDER BY `l2`

Takové spojení tabulek se označuje jako SELF JOIN, ale v příkazu používáme příkaz LEFT JOIN. Za klíčovým slovem ON je uvedeno pravidlo, an základě kterého vybíráme odpovídající kategorii.

Výstup z tohoto dotazu vypadá takto:

+-----------+-------------------------+
| l1        | l2                      |
+-----------+-------------------------+
| Hydroxidy | Hydroxid draselný       |
| Hydroxidy | Hydroxid lithný         |
| Hydroxidy | Hydroxid sodný          |
| Hydroxidy | Hydroxid vápenatý       |
| Kyseliny  | Kyselina bromovodíková  |
| Kyseliny  | Kyselina dusičná        |
| Kyseliny  | Kyselina chlorovodíková |
| Kyseliny  | Kyselina octová         |
| Kyseliny  | Kyselina propionová     |
| Kyseliny  | Kyselina sírová         |
+-----------+-------------------------+

Pokud budeme mít v seznamu více úrovní, tak musíme použít více JOINů. Data v tabulce si můžeme upravit tímto způsobem:

+----+-------------------------+--------+
| ID | Name                    | Parent |
+----+-------------------------+--------+
|  1 | Kapaliny                |   NULL |
|  2 | Pevné                   |   NULL |
|  3 | Kyseliny                |      1 |
|  4 | Zásady                  |      1 |
|  5 | Kyseliny                |      2 |
|  6 | Zásady                  |      2 |
|  7 | Kyselina sírová         |      3 |
|  8 | Kyselina chlorovodíková |      3 |
|  9 | Triethylamin            |      4 |
| 10 | Tributylamin            |      4 |
| 11 | Kyselina citrónová      |      5 |
| 12 | Kyselina šťavelová      |      5 |
| 13 | Hydroxid sodný          |      6 |
| 14 | Hydroxid draselný       |      6 |
+----+-------------------------+--------+

A SQL dotaz bude potom vypadat takto:

SELECT `t1`.`name` `l1`, `t2`.`name` `l2`, `t3`.`name` `l3`
FROM `category2` `t1`
LEFT JOIN `category2` `t2` ON `t2`.`parent` = `t1`.`ID`
LEFT JOIN `category2` `t3` ON `t3`.`parent` = `t2`.`ID`
WHERE `t3`.`name` IS NOT NULL
ORDER BY `l1`

A výstup:

+----------+----------+-------------------------+
| l1       | l2       | l3                      |
+----------+----------+-------------------------+
| Kapaliny | Kyseliny | Kyselina sírová         |
| Kapaliny | Kyseliny | Kyselina chlorovodíková |
| Kapaliny | Zásady   | Triethylamin            |
| Kapaliny | Zásady   | Tributylamin            |
| Pevné    | Zásady   | Hydroxid draselný       |
| Pevné    | Kyseliny | Kyselina citrónová      |
| Pevné    | Kyseliny | Kyselina šťavelová      |
| Pevné    | Zásady   | Hydroxid sodný          |
+----------+----------+-------------------------+

I v relačních databázích lze pracovat s hierarchicky uspořádanými daty, ale pokud je to možné, doporučuji volit např. XML, které je pro tento typ dat vhodnější.

Leave a Reply