Что-то, о чем здесь не упоминалось, хотя и немного похоже на вторую альтернативу принятого ответа, но отличается и невысокой стоимостью для большого иерархического запроса и простых элементов (insert update delete), будет добавлять столбец постоянных путей для каждого элемента.
some like:
id | name | path
19 | category1 | /19
20 | category2 | /19/20
21 | category3 | /19/20/21
22 | category4 | /19/20/21/22
Пример:
-- get children of category3:
SELECT * FROM my_table WHERE path LIKE '/19/20/21%'
-- Reparent an item:
UPDATE my_table SET path = REPLACE(path, '/19/20', '/15/16') WHERE path LIKE '/19/20/%'
Оптимизировать длину пути и ORDER BY path
, используя кодировку base36, вместо этого действительный числовой идентификатор пути
]
// base10 => base36
'1' => '1',
'10' => 'A',
'100' => '2S',
'1000' => 'RS',
'10000' => '7PS',
'100000' => '255S',
'1000000' => 'LFLS',
'1000000000' => 'GJDGXS',
'1000000000000' => 'CRE66I9S'
https://en.wikipedia.org/wiki/Base36
Подавление разделителя '/' с помощью фиксированной длины и отступов до encoded id
Подробное объяснение оптимизации здесь: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/
TODO
построение функции или процедуры для разделения пути для возвращающих предков одного элемента