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

Обновление одной таблицы на основе значений другой

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

Решение
Создайте новую таблицу, заполненную данными результата соединения исходной таблицы и таблицы, содержащей новую информацию. Затем замените исходную таблицу новой. Или напишите программу, выбирающую информацию из связанной таблицы, и выполните необходимые запросы для обновления исходной таблицы. Или используйте mysql для формирования и выполнения запросов.

Обсуждение
Иногда при обновлении записей одной таблицы необходимо сослаться на записи другой таблицы. Вспомним таблицу states, использовавшуюся в нескольких предыдущих рецептах и содержащую строки типа:

mysql> SELECT * FROM states;

+----------------+--------+------------+----------+
| name      | abbrev | statehood  | pop          |
+----------------+--------+------------+----------+
| Alaska    | AK        | 1959-01-03 | 550043   |
| Alabama | AL        | 1819-12-14 | 4040587 |
| Arkansas | AR      | 1836-06-15 | 2350725 |
| Arizona    | AZ      | 1912-02-14 | 3665228 |
...

Теперь предположим, что вы хотите добавить в эту таблицу несколько новых столбцов, используя информацию из другой таблицы city, содержащей такие данные о каждом штате, как его столица и крупнейший (по количеству жителей) город:

mysql> SELECT * FROM city;

+-----------+---------------+--------------+
| state       | capital           | largest       |
+-----------+---------------+-------------+
| Alabama | Montgomery | Birmingham |
| Alaska     | Juneau         | Anchorage |
| Arizona   | Phoenix        | Phoenix      |
| Arkansas | Little Rock    | Little Rock  |
...

Достаточно просто добавить в таблицу states новые столбцы capital и largest с помощью предложения ALTER TABLE. Но как тогда изменять строки, чтобы заполнить новые столбцы соответствующими значениями? Удобнее всего выполнить запрос UPDATE, использующий синтаксис соединения в инструкции WHERE:

UPDATE states,city
SET states.capital = city.capital, states.largest = city.largest
WHERE states.name = city.state;

Но, к сожалению, так поступить нельзя, поскольку MySQL еще не поддерживает эту возможность. Вторым решением могло бы быть использование подзапроса в инструкции WHERE, но подзапросы планируется включить только в версию MySQL 4.1. Что же остается? Конечно, не хотелось бы обновлять каждую строку вручную. Это невероятно трудоемко, да и глупо, учитывая то, что вся новая информация уже хранится в таблице city. Таблицы states и city содержат общий ключ (названия штатов), и эту информацию можно использовать для связывания таблиц и выполнения обновления. Есть несколько путей достижения того же результата, что и многотабличное обновление:

• Создайте новую таблицу, похожую на исходную, но содержащую дополнительные столбцы, добавленные из связанной таблицы city. Заполнитеновую таблицу данными, используя результат соединения таблиц states и city, затем замените исходную таблицу новой.

• Напишите программу, использующую информацию из таблицы city для формирования и выполнения предложений UPDATE, которые обновляют по одному штату в каждом запросе в таблице states.

• Используйте mysql для формирования предложений UPDATE. Обновление связанной таблицы путем ее замены Замена таблицы выполняется так. Чтобы расширить таблицу states, включив в нее столбцы capital и largest из таблицы city, создайте таблицу tmp, аналогичную states, и добавьте в нее столбцы capital и largest:

CREATE TABLE tmp
(
name VARCHAR(30) NOT NULL, # название штата
abbrev CHAR(2) NOT NULL, # двухсимвольная аббревиатура
statehood DATE, # дата вступления в Союз
pop BIGINT, # население на 4/1990
capital VARCHAR(30), # столица
largest VARCHAR(30), # наиболее населенный город
PRIMARY KEY (abbrev)
);

Затем заполните tmp данными, используя результат соединения между states и city, которое сопоставляет строки по названиям штатов:

INSERT INTO tmp (name, abbrev, statehood, pop, capital, largest)
SELECT
states.name, states.abbrev, states.statehood, states.pop,
city.capital, city.largest
FROM
states LEFT JOIN city ON states.name = city.state;

Заметьте, что запрос использует соединение LEFT JOIN. Предположим, что таблица city не полная и не содержит по строке для каждого штата. Тогда обычное соединение не сможет сформировать строку вывода для штатов, отсутствующих в таблице city, в результате в таблице tmp не будет записей для таких штатов, даже если они присутствуют в таблице states. Нехорошо! LEFT JOIN обеспечивает формирование строки вывода SELECT для каждой строки states вне зависимости от ее соответствия строке таблицы city. Любой штат, отсутствующий в таблице city, будет содержать значения NULL в столбцах capital и largest таблицы tmp, что вполне подходит для тех случаев, когда вам неизвестны названия городов, а формирование неполной строки, конечно же, предпочтительнее полной потери строки.

В результате таблица tmp похожа на исходную, но содержит два новых столбца, capital и largest (можете проверить). Убедившись в том, что таблица tmp нас устраивает, используем ее для замены исходной таблицы states:

DROP TABLE states;
ALTER TABLE tmp RENAME TO states;

Если вы хотите исключить возможность даже кратковременной недоступности таблицы states, выполните замену таким способом:

RENAME TABLE states TO states_old, tmp TO states;
DROP TABLE states_old;

Обновление связанной таблицы из программы
Метод замены таблиц эффективен, так как всю работу делает сервер. Но он больше всего подходит для тех случаев, когда изменяются все или почти все строки таблицы. Если же обновляется всего несколько строк, проще изменить таблицу «на месте» там, где необходимо. Кроме того, замена таблицы требует как минимум удвоения исходного пространства таблицы states на время выполнения процедуры обновления. Если обновляется большая таблица, то вы можете не захотеть использовать все это пространство.

Второй способ обновления таблицы на основе информации из связанной таблицы заключается в считывании данных из связанной таблицы и использования их для формирования предложений UPDATE. Например, чтобы обновить таблицу states информацией, хранящейся в таблице city, прочитайте названия городов и используйте их для создания и запуска ряда таких запросов:

UPDATE states SET capital = 'Montgomery', largest = 'Birmingham'
WHERE name = 'Alabama';
UPDATE states SET capital = 'Juneau', largest = 'Anchorage'
WHERE name = 'Alaska';
UPDATE states SET capital = 'Phoenix', largest = 'Phoenix'
WHERE name = 'Arizona';
UPDATE states SET capital = 'Little Rock', largest = 'Little Rock'
WHERE name = 'Arkansas';
...

Для выполнения такой процедуры сначала измените таблицу states так, чтобы она содержала новые столбцы:

ALTER TABLE states ADD capital VARCHAR(30), ADD largest VARCHAR(30);

Затем напишите программу, которая читает таблицу city и использует ее содержимое для формирования предложений UPDATE, изменяющих таблицу states. Рассмотрим пример сценария update_cities.pl, выполняющего такие операции:

#! /usr/bin/perl -w
# update_cities.pl – обновление столбцов capital и largest таблицы states
# с использованием содержимого таблицы city. Предполагается, что таблица states
# была изменена так, что теперь содержит столбцы capital и largest.
use strict;
use lib qw(/usr/local/apache/lib/perl);
use Cookbook;my $dbh = Cookbook::connect ();
my $sth = $dbh->prepare ("SELECT state, capital, largest FROM city");
$sth->execute ();
while (my ($state, $capital, $largest) = $sth->fetchrow_array ())
{
$dbh->do ("UPDATE states SET capital = ?, largest = ? WHERE name = ?",
undef, $capital, $largest, $state);
}
$dbh->disconnect ();
exit (0);

Все имена таблиц и столбцов встроены в сценарий, что делает его узко специальным. Вы можете обобщить процесс, написав функцию, которая принимает параметры, указывающие имена таблиц, столбцы, которые должны использоваться для сопоставления записей в двух таблицах, и столбцы для обновления строк. Один из путей решения задачи реализован в сценарии update_related.pl из каталога joins дистрибутива recipes.

Обновление связанной таблицы с помощью mysql
Если ваши значения данных не требуют какой-то специальной обработки внутренних кавычек или других специальных символов, то формировать и обрабатывать предложения UPDATE можно с помощью mysql. Похожая техника была описана в рецепте 12.17 при использовании mysql для имитации подзапроса.

Поместите следующее предложение в файл update_cities.sql:

SELECT CONCAT('UPDATE states SET capital = \'',capital,
'\', largest = \'',largest,'\' WHERE name = \'',state,'\';')
FROM city;

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

% mysql -N cookbook < update_cities.sql > tmp

Файл tmp будет содержать предложения, похожие на запросы, сформированные сценарием update_cities.pl. Считая, что столбцы capital и largest добавлены в таблицу states, вы можете выполнить предложения для обновления таблицы так:

% mysql cookbook < tmp

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

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