MySQL INSERT ... SELECT из 1 таблицы в 2 таблицы

Question

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

У меня есть временная таблица, которая содержит денормализованные данные. Мне необходимо

1. перебирать каждую строку временной таблицы
2. создать новую строку в первичной таблице с некоторыми полями во временной таблице
3. используйте идентификатор автоинкремента только что созданной строки первичной таблицы, чтобы создать новую строку во вторичной таблице, которая помещает LAST_INSERT_ID() в поле primary_id.

Я понимаю весь LAST_INSERT_ID() и я счастлив запустить транзакцию: я просто не знаю, как создать "внешний цикл выбора", который циклически перебирает временную таблицу и затем выполняет 2 последующие вставки.

Answer 1

Это может работать, если значения во временной таблице уникальны. Идея состоит в том, чтобы разделить вставки на два шага, один в основной таблице, а остальные в дополнительную таблицу:

insert into primary( . . .)
    select . . .
    from temp;

insert into secondary(primaryid, . . . )
    select p.PrimaryId, t.col . . .
    from temp t join
         primary p
         on t.col1 = p.col1 and . . .;

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

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

Answer 2

Как сказал @GordonLinoff, можно зацикливаться LAST_INSERT_ID(), Если каждый набор первичных столбцов во временной таблице может быть не уникальным, по сути, есть два способа сделать это:

1. Положитесь на ограничение уникальности для соответствующих столбцов в первичной таблице вместе с MySQL ON DUPLICATE KEY UPDATE расширение до INSERT:

   CREATE PROCEDURE foo() BEGIN
     DECLARE done BOOLEAN DEFAULT FALSE;
     DECLARE cur CURSOR FOR SELECT . . . FROM temp;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
   
     PREPARE ins1 FROM '
       INSERT INTO primary
         (. . .)
       VALUES
         (?, . . .)
       ON DUPLICATE KEY UPDATE
         PrimaryId = LAST_INSERT_ID(PrimaryId)
     ';
     PREPARE ins2 FROM '
       INSERT INTO secondary
         (primaryid, . . .)
       VALUES
         (LAST_INSERT_ID(), ?, . . .)
     ';
   
     OPEN cur;
     read_loop: LOOP
       FETCH cur INTO @a, @b, . . .;
       IF done THEN
         LEAVE read_loop;
       END IF;
   
       EXECUTE ins1 USING @a, . . .;
       EXECUTE ins2 USING @b, . . .;
     END LOOP;
     CLOSE cur;
   
     DROP PREPARE ins1;
     DROP PREPARE ins2;
   END
   

2. Сортируйте по временной таблице (неуникальные) первичные столбцы, затем отслеживайте последние просмотренные значения и вставляйте в первичную таблицу только при обнаружении новой записи:

   CREATE PROCEDURE foo() BEGIN
     DECLARE done BOOLEAN DEFAULT FALSE;
     DECLARE cur CURSOR FOR SELECT . . . FROM temp ORDER BY . . .;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
   
     PREPARE ins1 FROM '
       INSERT INTO primary
         (. . .)
       VALUES
         (. . .)  -- use "?" placeholders
     ';
     PREPARE ins2 FROM '
       INSERT INTO secondary
         (primaryid, . . .)
       VALUES
         (. . .)  -- use "?" placeholders
     ';
   
     OPEN cur;
     FETCH cur INTO @a, @b, . . .;
     WHILE NOT done DO
       SET @current_a := @a, . . .;  -- (non-unique) primary cols
       EXECUTE ins1 USING @a, . . .;
       SET @primaryid := LAST_INSERT_ID();
   
       REPEAT
         EXECUTE ins2 USING @primaryid, @b, . . .;
         FETCH cur INTO @a, @b, . . .;
       UNTIL done OR @a <> @current_a OR . . . END REPEAT;
     END WHILE;
     CLOSE cur;
   
     DROP PREPARE ins1;
     DROP PREPARE ins2;
   END