Как получить все дочерние записи в иерархических данных MYSQL

Question

У меня есть таблица со следующими столбцами

id   |    parent_customer_id
-----------------------------
1    |    0
2    |    0
3    |    1
4    |    2
5    |    4
6    |    4

Я хотел бы сценарий, который может вернуть все дочерние идентификаторы определенного клиента. Например

get_child_ids (1) = 1,3
get_child_ids(2) = 2,4,5,6
get_child_ids(3) = 3
get_child_ids(4) = 4,5,6
get_child_ids(5) = 5
get_child_ids(6) = 6

Некоторые идентификаторы могут доходить до 10 уровней. Я нашел отличное решение для получения родительских идентификаторов на https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/ но у меня возникли проблемы получать детей

Answer 1

Лучшее решение - "написать рекурсивный запрос SQL с синтаксисом CTE", но это не поддерживается до MySQL 8.0.1.

Рекурсивный синтаксис CTE - это стандартный SQL, поддерживаемый всеми популярными брендами SQL-совместимых продуктов, теперь, когда MySQL его поддерживает.

Я сделал презентацию о предстоящей функции рекурсивных запросов в MySQL на конференции Percona Live Conference в апреле 2017 года: Рекурсивный сброс запросов в MySQL 8.

WITH RECURSIVE MyCTE AS (
  SELECT id, parent_customer_id FROM MyTable WHERE id = ?
  UNION
  SELECT id, parent_customer_id FROM MyTable JOIN MyCTE 
    ON MyTable.parent_customer_id = MyCTE.id
)
SELECT * FROM MyCTE;

Если вы не можете использовать MySQL 8.0.1 или новее, вы можете использовать умное решение в ExplainExtended или вы можете хранить свои иерархические данные другим способом для поддержки нерекурсивных запросов.

Я показываю несколько решений в своей презентации Модели для иерархических данных или в своем ответе на вопрос " Какой самый эффективный / элегантный способ разбить плоский стол на дерево?,

Я также написал главу об этом в своей книге " Антипаттерны SQL: предотвращение ловушек программирования баз данных".

Answer 2

Вы должны проверить следующую статью об управлении иерархическими данными в MySQL

Это отличная статья, которая показывает вам отличную технику работы с иерархическими данными с "бесконечной" глубиной.

Слово предостережения: Если вы имеете дело с иерархическими данными, в которых у ребенка есть уникальный родитель, это отличное решение для вас. Но если вы имеете дело с детьми, у которых более одного родителя, то вы имеете дело с графиками, и по этой причине MySQL вам не подходит. Вместо этого вы должны исследовать решения как Neo4J.