Блог

Иерархические данные в MySQL с использованием модели списка смежности

У меня есть эта таблица с именем node_structur_data

 CREATE TABLE node_structure_data (
   id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   title VARCHAR(455) NOT NULL,
   parent_id INT(10) UNSIGNED DEFAULT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (parent_id) REFERENCES node_structure_data (id)
   ON DELETE CASCADE ON UPDATE CASCADE
);
 

Выход:

 id  title   parent_id
1   Division  NULL
2   Site 1    1
3   Paper     2
4   ms1       3
 

Как я могу дублировать узел и его дочерние элементы?
Например Site 1
Идентификатор и родительский идентификатор должны быть уникальными, но название должно оставаться прежним.

Ожидаемый Результат:

 id  title   parent_id
1   Division  NULL
2   Site 1    1
3   Paper     2
4   ms1       3
5   Site 1    1
6   Paper     5
7   ms1       6
 

Следующий подход сначала оценивает новый максимум, а затем использует рекурсивный cte для поиска всех дочерних элементов нужного узла «Сайт 1» и определения их новых возможных parent_id , если в таблице не было других одновременных записей.

Я бы рекомендовал выполнить следующее в транзакции и заблокировать таблицу во время операции, чтобы предотвратить одновременные изменения таблицы.

Чтобы проверить этот подход, я добавил некоторые дополнительные примеры данных, которые я включил ниже, однако вы можете увидеть этот подход в демонстрационном примере с вашими исходными образцами данных здесь

Смотрите вывод рабочей скрипки бд ниже:

Схема (MySQL v8.0)

 CREATE TABLE node_structure_data (
   id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   title VARCHAR(455) NOT NULL,
   parent_id INT(10) UNSIGNED DEFAULT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (parent_id) REFERENCES node_structure_data (id)
   ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO node_structure_data
  (`id`, `title`, `parent_id`)
VALUES
  ('1', 'Division', NULL),
  ('2', 'Site 1', '1'),
  ('3', 'Paper', '2'),
  ('4', 'ms1', '3'),
  ('5', 'ms2', '3'),
  ('6', 'os1', '4'),
  ('7', 'os2', '4'),
  ('8', 'gs1', '1'),
  ('9', 'hs1', '3'),
  ('10','js1','9');
 

Запрос № 1

 select 'Before Insert';
 

Query #2

 select * from node_structure_data;
 

Запрос № 3

 select 'Possible Data Changes';
 

Запрос № 4

 with recursive max_id AS (
    SELECT MAX(id) as id FROM node_structure_data
),
child_nodes AS (
    SELECT
        n.id,
        title,
        parent_id,
        m.id 1 as new_id,
        parent_id as new_parent_id
    FROM
        node_structure_data n
    CROSS JOIN
        max_id as m
    WHERE
        title='Site 1'
    
  
    UNION ALL
  
    SELECT
        n.id,
        n.title,
        n.parent_id,
        @row_num:=IF(@row_num=0,c.new_id,0)   1   @row_num  as new_id,
        c.new_id
    FROM
        child_nodes c
    INNER JOIN
        node_structure_data n ON n.parent_id = c.id 
    CROSS JOIN (
        SELECT @row_num:=0 as rn
    ) as vars
    
)
SELECT * FROM child_nodes;
 

Запрос № 5 — Выполнение фактической вставки

 INSERT INTO node_structure_data (title,parent_id)
with recursive max_id AS (
    SELECT MAX(id) as id FROM node_structure_data
),
child_nodes AS (
    SELECT
        n.id,
        title,
        parent_id,
        m.id 1 as new_id,
        parent_id as new_parent_id
    FROM
        node_structure_data n
    CROSS JOIN
        max_id as m
    WHERE
        title='Site 1'
    
  
    UNION ALL
  
    SELECT
        n.id,
        n.title,
        n.parent_id,
        @row_num:=IF(@row_num=0,c.new_id,0)   1   @row_num  as new_id,
        c.new_id
    FROM
        child_nodes c
    INNER JOIN
        node_structure_data n ON n.parent_id = c.id 
    CROSS JOIN (
        SELECT @row_num:=0 as rn
    ) as vars
    
)
SELECT title,new_parent_id FROM child_nodes ORDER BY new_id;
 

Результатов для отображения нет.

Query #6

 select 'AFTER INSERT';
 

Запрос № 7

 select * from node_structure_data;
 

Просмотр на скрипке БД

Дайте мне знать, если это сработает для вас.