Внутреннее присоединение к временной таблице и вставка в таблицу в MySql

У меня есть список строк. У каждой из них есть категории, разделенные знаком «/».

Например:

животные / домашние / собака

животные / домашние / кошка

Что я хочу сделать с этими категориями, так это вставить их в таблицу категорий MySql. В таблице 4 столбца: id (int auto increment), category_name (nvarchar), parent_id (int), is_active (bit).

Логика их вставки должна быть следующей:

Для основных категорий (животных) значение parent_id должно быть равно 0.

Дочерние категории будут иметь идентификатор своего родителя как parent_id.

Не может быть двух активных категорий с одним и тем же названием.

Я попытался реализовать следующую логику:

Получите четкий список строк.
Из них составьте четкий список основных категорий.
Вставьте отдельные основные категории в таблицу категорий с родительским идентификатором 0.
Разделите каждую из категорий попарно и получите отдельные пары:

(животные, домашние)

(домашние, собачьи)

(Домашняя кошка)

Получите соответствующий идентификатор для каждой из родительских категорий и вставьте его в parent_id дочернего элемента.

SQL:

/*INSERT ALL THE FIRST PARENT CATEGORIES WITH A PARENT ID OF 0*/
            INSERT INTO categories (category_name, parent_id, is_active)
            VALUES ('animals', 0, 1);

/*INSERT ALL THE CATEGORIES IN PAIRS TO TEMP TABLE*/
            CREATE TEMPORARY TABLE tempcat(parent nvarchar(256), child nvarchar(256));
            INSERT INTO tempcat
            VALUES ('animals', 'domestic'),('domestic', 'dog'),('domestic','cat');

/*INSERT INTO THE CATEGORIES TABLE*/
            INSERT INTO categories(category_name, parent_id, is_active)
            SELECT tempcat.child, categories.id, 1            
            FROM categories
            INNER JOIN tempcat
            ON categories.category_name = tempcat.parent;
            WHERE categories.is_active = 1;

/*DISPOSE THE TEMPORARY TABLE*/
            DROP TEMPORARY TABLE tempcat;

Проблема: после выполнения запроса я ожидаю 4 записи в таблице категорий.

Но я получаю только 2.

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

Мы высоко ценим любые указания в правильном направлении.

Обновление №1. Предположим, что в спецификациях указано: «Не может быть двух активных категорий с одним и тем же именем категории и с одинаковыми родительскими идентификаторами». Например, если было две строки как (животные / домашняя / кошка), (животные / на улице / кошка), должно быть две записи для кошки с идентификаторами домашнего и уличного как parent_id.

Kavo 13.02.2020 источник

comment

Какую версию MySQL вы используете? - Gordon Linoff 13.02.2020

comment

@GordonLinoff это "5.6.46-log" - Kavo 13.02.2020

Ответы (2)

arrow_upward
1
arrow_downward

CREATE TABLE categories (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                         category_name VARCHAR(64), 
                         parent_id INT UNSIGNED NOT NULL DEFAULT 0,
                         is_active CHAR(1) NULL,
                         UNIQUE INDEX idx_name_active (category_name));

CREATE TABLE source_data (path TEXT);
INSERT INTO source_data VALUES ('animals/domestic/dog'), ('animals/domestic/cat');

CREATE PROCEDURE update_categories_table()
BEGIN
DECLARE cnt INT DEFAULT 0;
INSERT IGNORE INTO categories (category_name, parent_id, is_active)
SELECT SUBSTRING_INDEX(path, '/', 1), 0, '1'
FROM source_data;
iteration: LOOP
    SELECT COUNT(*) INTO cnt
    FROM source_data
    WHERE LOCATE('/', path);
    IF NOT cnt THEN 
        LEAVE iteration;
    END IF;
    INSERT IGNORE INTO categories (category_name, parent_id, is_active)
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(source_data.path, '/', 2), '/', -1),
           categories.id,
           '1'
    FROM source_data, categories
    WHERE SUBSTRING_INDEX(source_data.path, '/', 1) = categories.category_name;
    UPDATE source_data
    SET path = SUBSTRING(path FROM 1 + LOCATE('/', path));
END LOOP iteration;
TRUNCATE source_data;
END

call update_categories_table;
SELECT * FROM categories;

id | category_name | parent_id | is_active
-: | :------------ | --------: | :--------
 1 | animals       |         0 | 1        
 4 | domestic      |         1 | 1        
 7 | dog           |         4 | 1        
 8 | cat           |         4 | 1

db ‹> скрипт здесь

Akina 13.02.2020

comment

Спасибо. Это делает то, о чем просили спецификации. Также это довольно легко понять. UNIQUE INDEX idx_name_active (category_name) в настоящее время решает, следует ли дублировать название категории или нет. Я добавил в свой пост Обновление №1. Как мне изменить приведенную выше строку, чтобы добиться этого? - Kavo; 15.02.2020

comment

Для справки, мне удалось добиться вышеизложенного, настроив уникальный индекс UNIQUE INDEX idx_name_active (category_name, parent_id). - Kavo; 17.02.2020

arrow_upward
1
arrow_downward

В MySQL 8 это можно сделать с помощью одного запроса:

with splits as (
      select 1 as n, substring_index(cats, '/', 1) as cat, cats
      from strings union all
      select 2 as n, substring_index(substring_index(cats, '/', 2), '/', -1) as cat, cats
      from strings 
      where cats like '%/%' union all
      select 3 as n, substring_index(substring_index(cats, '/', 3), '/', -1) as cat, cats 
      from strings
      where cats like '%/%/%'
     ),
     splits_n as (
      select s.*, dense_rank() over (order by n, cat) as new_id
      from splits s
     ),
     splits_np as (
      select s.*, sp.new_id as parent_id
      from splits_n s left join
           splits_n sp
           on sp.cats = s.cats and sp.n = s.n - 1
     ) 
select distinct new_id as id, cat, parent_id, 1 as is_active
from splits_np s;

Вот скрипка db ‹>.

К сожалению, в более ранних версиях это было гораздо болезненнее.

Gordon Linoff 13.02.2020

Внутреннее присоединение к временной таблице и вставка в таблицу в MySql

Ответы (2)

Вопросы по теме