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
Надеюсь статья окажется вам полезной.
Информация копипастерам
Внимание! Копирование контента с сайта, возможно только с разрешения администратора. Т.е. Меня! Я скорее всего разрешу Вам это сделать, в обмен на живую ссылку, на статью оригинал.