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

Ссылка на имена столбцов вывода соединения в программе

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

Решение
Используйте псевдонимы столбцов для присваивания уникальных имен всем столбцам или ссылайтесь на столбцы позиционно.

Обсуждение
Соединения часто извлекают столбцы похожих таблиц, и нередко получается так, что выбранные из разных таблиц столбцы называются одинаково.

Обратимся вновь к соединению таблиц shirt, tie и pants:

mysql> SELECT shirt.item, tie.item, pants.item FROM shirt, tie, pants;

+----------+-------------+------+
| item        | item           | item |
+----------+-------------+------+
| Pinstripe | Fleur de lis | Plaid |
| Tie-Dye  | Fleur de lis | Plaid |
| Black       | Fleur de lis | Plaid |
| Pinstripe | Paisley       | Plaid |
...

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

$stmt = qq{
SELECT shirt.item, tie.item, pants.item
FROM shirt, tie, pants
};
$sth = $dbh->prepare ($stmt);
$sth->execute ();
# Определим количество столбцов результирующего множества двумя способами:# -

Проверим атрибут дескриптора предложения NUM_OF_FIELDS

# - Извлечем строку в хеш и посмотрим, сколько ключей он будет содержать
$count1 = $sth->{NUM_OF_FIELDS};
$ref = $sth->fetchrow_hashref ();
$count2 = keys (%{$ref});
print "The statement is: $stmt\n";
print "According to NUM_OF_FIELDS, the result set has $count1 columns\n";
print "The column names are: " . join (",", sort (@{$sth->{NAME}})) . "\n";
print "According to the row hash size, the result set has $count2 columns\n";
print "The column names are: " . join (",", sort (keys (%{$ref}))) . "\n";

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

According to NUM_OF_FIELDS, the result set has 3 columns
The column names are: item,item,item
According to the row hash size, the result set has 1 columns
The column names are: item

Что-то не так – счетчики столбцов не совпадают. Второй счетчик равен 1, так как неуникальные имена столбцов приводят к сопоставлению нескольких значений столбцов одному элементу хеша. В итоге некоторые значения оказываются утеряны. Для решения проблемы сделайте имена столбцов уникальными с помощью псевдонимов. Например, можно переписать запрос:

SELECT shirt.item, tie.item, pants.item
FROM shirt, tie, pants
так:
SELECT shirt.item AS shirt, tie.item AS tie, pants.item AS pants
FROM shirt, tie, pants

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

According to NUM_OF_FIELDS, the result set has 3 columns
The column names are: pants,shirt,tie
According to the row hash size, the result set has 3 columns
The column names are: pants,shirt,tie

Теперь счетчики столбцов одинаковы, при выборе в хеш значения не были утеряны.

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

while (my @val = $sth->fetchrow_array ())
{
print "shirt: $val[0], tie: $val, pants: $val\n";
}

В других языках проблему конфликтов имен можно решать другими способами. Например, в сценариях на Python дело обстоит несколько иначе, чем в Perl. Если вы извлекаете строку, используя словарь (ближайший аналог Python для хеша Perl), то модуль MySQLdb замечает совпадение имен столбцов и помещает их в словарь, используя ключ, состоящий из имени столбца, перед которым указано имя таблицы. То есть в приведенном ниже запросе ключами словаря будут item, tie.item и pants.item:

SELECT shirt.item, tie.item, pants.item
FROM shirt, tie, pants

Это означает, что значения столбцов не будут потеряны, но все же нельзя забывать о неуникальных именах. Если вы попытаетесь сослаться на значения столбцов, используя просто их имена, то не получите ожидаемых результатов для тех имен, в начало которых добавлены имена таблиц. Если использовать псевдонимы для обеспечения уникальности всех имен столбцов, то записи словаря получат именно те имена, которые вы им присвоите.

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

Вставка записей в таблицу, включающую значения из другой
Вывод списков для записей «главная-подчиненная» и итогов
Вычисление разности между последовательными строками
Вычисление рейтинга команд
Выявление и удаление несвязанных записей