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

MySql 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

Справка на английском (увы) по INSERT SELECT для MySql

Надеюсь статья окажется вам полезной.

 

Возможно Вас заинтересуют эти материалы

PHP: Буферизация вывода

PHP: Буферизация вывода

Буферизация вывода в PHP это довольно полезная штука, если уметь ею пользоваться. Скажите сколько раз
POP3 протокол с примерами

POP3 протокол с примерами

POP3 это протокол по которому мы можем получить почту с сервера, для подобной задачи, используется
PHP: Хранение сессии в базе данных

PHP: Хранение сессии в базе данных

Как и обещал, тема данной заметки: база данных, как обработчик сессии. А так как на