MySQL / 12. Использование нескольких таблиц

Вставка записей в таблицу, включающую значения из другой

Задача
Вам нужно вставить запись в таблицу, требующую значение идентификатора. Вы же знаете только соответствующее идентификатору имя, но не сам идентификатор.

Решение
Если предположить, что у вас есть справочная таблица, сопоставляющая имена идентификаторам, то можно создать запись с помощью предложения INSERT INTO ... SELECT, в котором SELECT выполняет просмотр имен для получения значения соответствующего идентификатора.

Обсуждение
Справочные таблицы часто использовались в этой главе в соединениях, обычно для сопоставления идентификаторам или кодам значений описательных имен. Но справочные таблицы полезны не только для предложенийSELECT, они могут помочь и при создании новых записей. Давайте вернемся к таблицам artist и painting, содержащим информацию о вашей коллекции произведений искусства. Предположим, что вы едете в Миннесоту, где удается приобрести всего за 51 доллар репродукцию картины «Жонглеры» («Les jongleurs») Ренуара. Ренуар уже отмечен в таблице artist, так что нет необходимости добавлять туда новую запись. Но нужно добавить запись в таблицу painting. Чтобы создать ее, необходимо сохранить идентификатор художника, название картины, штат покупки и цену. Вы знаете все, кроме идентификатора автора, и не хотите сами искать его в таблице artist. Раз Ренуар там уже есть, почему бы не позволить MySQL найти для вас его идентификатор? Для добавления новой записи выполним предложение INSERT ...SELECT. Укажем все известные значения в списке столбцов вывода SELECT и используем инструкцию WHERE для поиска идентификатора художника по его фамилии:

mysql> INSERT INTO painting (a_id, title, state, price)
-> SELECT a_id, 'Les jongleurs', 'MN', 51
-> FROM artist WHERE name = 'Renoir';

Естественно, вам не захочется писать вручную полный текст этого запроса при каждой новой покупке. Но легко можно написать небольшой сценарий, который по фамилии художника, названию картины, штату и стоимости покупки формировал бы запрос и выполнял его для вас. Также можно написать код для проверки вхождения художника в таблицу artist и, в случае его отсутствия, генерирования нового значения идентификатора для художника перед выполнением вставки новой записи в painting. Просто выполните следующий запрос перед созданием новой строки в таблице painting:

INSERT IGNORE INTO artist (name) VALUES('фамилия художника');

Статьи по MySQL на эту тему:

Выявление и удаление несвязанных записей
Обновление одной таблицы на основе значений другой
Одновременное использование нескольких серверов MySQL
Создание справочной таблицы с помощью соединения
Удаление связанных строк в нескольких таблицах