SQL INSERT SELECT одним запросом

В этой заметке хочу рассказать об одной особенности использования SQL операторов INSERT SELECT. Конечно для подобных вещей мне бы выделить отдельную категорию, и назвать её, скажем, SQL, но так как статей на эту тему у меня пока мало поместим её сюда.

Часто на практике приходится выполнять копирование каких либо данных. Например вместо того, чтобы создавать нового пользователя можно скопировать существующего и просто внести в его профиль нужные изменения — так экономится много времени, особенно если эту задачу приходится повторять часто. Конечно в данной статье речь идёт о копировании сущностей данные которых находятся в пределах одной таблицы.

Вопрос в том, как это сделать лучше и проще, да желательно одним запросом?

В MySql для этого в операторе INSERT можно использовать оператор SELECT следующим образом (здесь мы копируем данные из одной таблицы в другую, имеющую такую же структуру):

INSERT INTO
    `some_table` (`fld1`,`fld2`,`fld3`)
SELECT
    `some_table2`.`fld1`,
    `some_table2`.`fld2`,
    `some_table2`.`fld3` 
FROM
    `some_table2` 
WHERE
    `some_table2`.`fld` > 100;

Стоит заметить, что операция INSERT SELECT может быть произведена в рамках одной таблицы.

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

INSERT INTO
    `some_table` (`fld1`,`fld2`,`fld3`)
SELECT
    `src`.`fld1`,
    `src`.`fld2`,
    `src`.`fld3` 
FROM
    `some_table`  AS `src`
WHERE
    `src`.`fld` > 100;

В случае, если вы работаете с одной таблицей и, если она имеет поле с уникальным индексом, его дублирование нужно как-то обрабатывать. Обычно уникальное поле это исскуственный первичный ключ с AUTO_INCREMENT, и как правило в этой ситуации его можно просто не указывать — система его создаст самостоятельно. Но, если у вас есть ещё уникальные индексы, то при копировании необходимо о них позаботиться. Например как вариант следующим образом (`unic_int_field` — уникальное целочисленное поле):

INSERT INTO
    `some_table` (`fld1`,`fld2`,`fld3`,`unic_int_field`)
SELECT
    `src`.`fld1`,
    `src`.`fld2`,
    `src`.`fld3` ,
    (`unic_int_field` + 1)
FROM
    `some_table`  AS `src`
WHERE
    `src`.`fld` > 100;

Если поле имеет один из строковых типов данных, то как вариант можно использовать следующий маневр (`unic_name` — уникальное поле с символьным типом данных,`unic_int_field` — уникальное целочисленное поле) :

INSERT
    INTO `some_table` (`name`,`ins_payment`,`description`,`franchise`,`checked_out`)
SELECT
    CONCAT("COPY ", SYSDATE(), " : ", `unic_name`),
    `src`.`fld1`,
    `src`.`fld2`,
    `src`.`fld3` ,
    (`unic_int_field` + 1)
FROM
    `some_table`  AS `src`
WHERE
    `src`.`fld` > 100;

Т.е. к строке мы просто конкатенируем «COPY » и текущее дату-время. Таким образом вновь созданное поле с большой долей вероятности будет иметь уникальное значение, и дублирования, и как следствия ошибки запроса не произойдёт. Но что бы система работала более стабильно можно использовать ключевое слово IGNORE. Тогда, если вдруг у вас всё таки, что-то продублируется — ошибки не произойдёт, а MySql сообщит что запрос выполнен, но эффект затронул 0 строк. А эту ситуацию вы легко обработаете в клиентском коде:

INSERT IGNORE
    INTO `some_table` (`name`,`ins_payment`,`description`,`franchise`,`checked_out`)
SELECT
    CONCAT("COPY ", SYSDATE(), " : ", `unic_name`),
    `src`.`fld1`,
    `src`.`fld2`,
    `src`.`fld3` ,
    (`unic_int_field` + 1)
FROM
    `some_table`  AS `src`
WHERE
    `src`.`fld` > 100;

Но в любом случае это просто примеры — на практике все может оказаться сложнее и там все будет зависеть от конкретной ситуации.

Кстати, примеры выше будут работать даже без использования псевдонимов:

INSERT
    INTO `some_table` (`name`,`ins_payment`,`description`,`franchise`,`checked_out`)
SELECT
    CONCAT("COPY ", SYSDATE(), " : ", `unic_name`),
    `fld1`,
    `fld2`,
    `fld3` ,
    (`unic_int_field` + 1)
FROM
    `some_table`
WHERE
    `fld` > 100;

Но в более сложных запросах псевдонимы всё же придётся использовать, в любом случае запрос не должен противоречить синтаксису и правилам SQL.