Создание справочной таблицы с помощью соединения

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

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

Обсуждение
Общий подход для экономии места – хранить в таблице идентификационные номера или коды вместо описательных строк. Повышается и производительность, поскольку числа быстрее индексировать и извлекать, чем строки.

(Для запросов, в которых необходим вывод имен, объедините значения идентификаторов со справочной таблицей вида идентификатор-название.) Когда вы создаете новую таблицу, помните об этой стратегии и сразу проектируйте таблицу так, чтобы ее можно было использовать со справочной таблицей. Но может случаться так, что у вас уже есть таблица с описательными строками, которую можно преобразовать так, чтобы она использовала значения идентификаторов. В этом разделе описано, как создать справочную таблицу, сопоставляющую каждому описанию его идентификатор, и как преобразовать описания в идентификаторы в исходной таблице.


Будем использовать ALTER TABLE в сочетании с приемами обновления связанной таблицы.

Предположим, вы коллекционируете монеты и решили отслеживать свою коллекцию с помощью базы данных, используя такую таблицу:

CREATE TABLE coin
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
date CHAR(5) NOT NULL, # 4 цифры года + буква1
denom CHAR(20) NOT NULL, # название номинала (например, Lincoln cent)
PRIMARY KEY (id)
);

Каждой монете автоматически присваивается идентификатор как значение AUTO_INCREMENT; кроме того, записываются год выпуска каждой монеты и ее название номинала (denomination name). Введенные записи выглядят так:

mysql> SELECT * FROM coin;

+----+-------+---------------------+
| id | date | denom |
+---+-------+------------------------+
| 1 | 1944s | Lincoln cent |
| 2 | 1977 | Roosevelt dime |
| 3 | 1955d | Lincoln cent |
| 4 | 1938 | Jefferson nickel |
| 5 | 1964 | Kennedy half dollar |
| 6 | 1959 | Lincoln cent |
| 7 | 1945 | Jefferson nickel |
| 8 | 1905 | Buffalo nickel |
| 9 | 1924 | Mercury head dime |
| 10 | 2001 | Roosevelt dime |
| 11 | 1937 | Mercury head dime |
| 12 | 1977 | Kennedy half dollar |
+---+--------+-----------------------+

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


Было бы эффективнее хранить в таблице coin идентификаторы названий номиналов монет, и при необходимости находить название номинала в таблице denom, содержащей все названия номиналов монет и их идентификаторы.

(Преимущество такого метода для небольшой таблицы может быть неочевидным, но когда коллекция вырастет до 10 000 экземпляров, экономия пространства от хранения номеров вместо строк станет весьма ощутимой.)

Процедура создания справочной таблицы и преобразования исходной таблицы coin такова:

1. Создайте справочную таблицу denom для хранения соответствий идентификаторов названиям.

2. Заполните таблицу denom названиями номиналов из исходной таблицы coin.

3. Замените названия номиналов монет в таблице coin на соответствующие значения идентификаторов.

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

CREATE TABLE denom
(
denom_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(20) NOT NULL,
PRIMARY KEY (denom_id)
);

Чтобы заполнить таблицу данными, вставьте в нее те названия номиналов, которые присутствуют в таблице coin.


Используйте SELECT DISTINCT, поскольку каждое название номинала должно быть введено ровно один раз:

INSERT INTO denom (name) SELECT DISTINCT denom FROM coin;

Предложение INSERT добавляет в таблицу denom только название номинала; denom_id – это столбец AUTO_INCREMENT, так что MySQL автоматически присвоит ему последовательные значения. Результирующая таблица будет такой:

+------------+-----------------------+
| denom_id | name |
+-----------+------------------------+
| 1 | Lincoln cent |
| 2 | Roosevelt dime |
| 3 | Jefferson nickel |
| 4 | Kennedy half dollar |
| 5 | Buffalo nickel |
| 6 | Mercury head dime |
+-----------+------------------------+

В MySQL версии 3.23 и выше вы можете создать таблицу denom и заполнить ее данными в одном предложении CREATE TABLE ... SELECT:

CREATE TABLE denom
(
denom_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (denom_id)
)
SELECT DISTINCT denom AS name FROM coin;

Следующим шагом после создания таблицы denom является преобразование названий номиналов в таблице coin в соответствующие идентификаторы:

• Создайте таблицу tmp, похожую на coin, но содержащую столбец denom_id вместо столбца denom.

• Заполните tmp результатами соединения таблиц coin и denom.

• Используйте таблицу tmp для замены исходной таблицы coin.

Для создания таблицы tmp выполните предложение CREATE TABLE, которое похоже на первоначальное предложение для создания coin, но подставьте в нем столбец denom_id вместо столбца denom:

CREATE TABLE tmp
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
date CHAR(5) NOT NULL, # 4 цифры года + буква
denom_id INT UNSIGNED NOT NULL, # идентификатор названия номинала
PRIMARY KEY (id)
);

Затем заполните tmp, используя соединение coin и denom:

INSERT INTO tmp (id, date, denom_id)
SELECT coin.id, coin.date, denom.denom_id
FROM coin, denom
WHERE coin.denom = denom.name;

Наконец, замените исходную таблицу coin таблицей tmp:

DROP TABLE coin;
ALTER TABLE tmp RENAME TO coin;

В MySQL версии 3.23 и выше вы можете создать и заполнить таблицу tmp в одном предложении:

CREATE TABLE tmp
(
PRIMARY KEY (id)
)
SELECT coin.id, coin.date, denom.denom_id
FROM coin, denom
WHERE coin.denom = denom.name;

Теперь, как и раньше, замените coin на tmp.
В качестве еще одного способа преобразования таблицы coin после создания таблицы denom можно предложить изменение coin на месте, без использования таблицы tmp:

1.


Добавьте столбец denom_id в таблицу coin при помощи ALTER TABLE.

2. Заполните значение denom_id каждой строки идентификатором, соответствующим ее названию номинала denom.

3. Удалите столбец denom.

Чтобы выполнить процедуру, добавьте в coin столбец для хранения идентификаторов:

ALTER TABLE coin ADD denom_id INT UNSIGNED NOT NULL;

Затем заполните столбец denom_id соответствующими идентификаторами, используя соответствие названий номиналов идентификаторам, хранящимся в таблице denom. Приведем небольшой сценарий, обновляющий значения идентификаторов в таблице coin для одного номинала за один проход:

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

Этот сценарий извлекает каждую пару идентификатор/название номинала из таблицы denom и формирует предложение UPDATE для изменения всех строк таблицы coin, содержащих название номинала, путем установки их значений denom_id в соответствующий идентификатор.


Когда сценарий завершит работу, все строки таблицы coin будут содержать обновленные значения denom_id. Теперь столбец denom больше не нужен, и от него можно избавиться:

ALTER TABLE coin DROP denom;

Какой бы способ вы ни использовали для преобразования таблицы coin, ее содержимое в результате будет выглядеть так:mysql> SELECT * FROM coin;

+---+--------+------------+
| id | date | denom_id |
+---+--------+------------+
| 1 | 1944s | 1 |
| 2 | 1977 | 2 |
| 3 | 1955d | 1 |
| 4 | 1938 | 3 |
| 5 | 1964 | 4 |
| 6 | 1959 | 1 |
| 7 | 1945 | 3 |
| 8 | 1905 | 5 |
| 9 | 1924 | 6 |
| 10 | 2001 | 2 |
| 11 | 1937 | 6 |
| 12 | 1977 | 4 |
+---+--------+------------+

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

mysql> SELECT coin.id, coin.date, denom.name
-> FROM coin, denom
-> WHERE coin.denom_id = denom.denom_id;

+---+--------+------------------------+
| id | date | name |
+---+--------+------------------------+
| 1 | 1944s | Lincoln cent |
| 2 | 1977 | Roosevelt dime |
| 3 | 1955d | Lincoln cent |
| 4 | 1938 | Jefferson nickel |
| 5 | 1964 | Kennedy half dollar |
| 6 | 1959 | Lincoln cent |
| 7 | 1945 | Jefferson nickel |
| 8 | 1905 | Buffalo nickel |
| 9 | 1924 | Mercury head dime |
| 10 | 2001 | Roosevelt dime |
| 11 | 1937 | Mercury head dime |
| 12 | 1977 | Kennedy half dollar |
+---+--------+------------------------+

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

А как насчет вставки новых элементов в таблицу coin? При работе с исходной таблицей вы бы вводили в каждую строку название номинала монеты. Теперь же, когда номинал преобразован в значения идентификаторов, используйте предложение INSERT INTO ... SELECT для поиска идентификатора номинала по названию. Например, для того чтобы ввести монету 10 центов (dime) 1962 года с Рузвельтом, используйте такое предложение:

INSERT INTO coin (date, denom_id)
SELECT 1962, denom_id FROM denom WHERE name = 'Roosevelt dime';

Оцените статью: (0 голосов)
0 5 0

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