Иерархия в MySQL: дерево разделов и строковые ключи

Контекст Битрикс

Деревья встречаются в разделах инфоблоков, меню и кастомных категориях. В ядре чаще опираются на API и ORM, но отчёты и миграции иногда требуют «чистого» SQL по таблице вида id, parent_id, name.

Идея старого трюка

Для числовых идентификаторов распространён приём: накопить обойдённый список в пользовательской переменной @pv, на каждой строке дописывать id и проверять FIND_IN_SET(parent_id, @pv). Порядок строк критичен — обычно сортируют по parent_id, id.

SELECT id, name, parent_id
FROM (
  SELECT *
  FROM demo_tree
  ORDER BY parent_id, id
) AS ordered,
     (SELECT @pv := '19') AS seed
WHERE FIND_IN_SET(parent_id, @pv) > 0
  AND @pv := CONCAT(@pv, ',', id);

Почему ломается на UUID

Для CHAR(36) корни проблемы двойные. Во‑первых, CONCAT(@pv, ',', id) в старых версиях MySQL в составе WHERE могло не пересчитываться так, как ожидает автор, если выражение «оптимизируется» до константы. Во‑вторых, подстановки вроде (@pv := …) OR 1 маскируют вычисление и дают неверный объём выборки: оптимизатор может пропустить побочный эффект присваивания.

Надёжнее не полагаться на порядок вычисления пользовательских переменных в одном запросе — поведение не гарантировано стандартом.

Современная альтернатива: рекурсивный CTE (MySQL 8+)

Для потомков от корня и для цепочки предков лучше использовать WITH RECURSIVE: план предсказуем, типы строковых ключей не ломают семантику.

WITH RECURSIVE walk AS (
  SELECT id, name, parent_id, 0 AS lvl
  FROM demo_tree
  WHERE id = 'b10'
  UNION ALL
  SELECT c.id, c.name, c.parent_id, w.lvl + 1
  FROM demo_tree c
  INNER JOIN walk w ON c.parent_id = w.id
)
SELECT * FROM walk ORDER BY lvl, id;

Цепочка «вверх» к корню

Для списка предков удобен второй рекурсивный шаблон: стартуем от листа и поднимаемся по parent_id до корня или пустого значения.

Итог

Трюки с @var оставьте для legacy и коротких скриптов; в продакшене на MySQL 8 и в новых модулях рядом с Битрикс ориентируйтесь на CTE и явные ограничения по глубине, чтобы не упереться в бесконечный цикл при ошибочной связи.