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ší.

Napsat komentář

This site uses Akismet to reduce spam. Learn how your comment data is processed.