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

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

Добавить комментарий


Защитный код
Обновить



Кто на сайте
Сейчас 35 гостей онлайн