Использование значений AUTO_INCREMENT для связывания таблиц

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

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

Обсуждение
Будьте внимательны при использовании значений AUTO_INCREMENT для идентификации строк главной таблицы, если вы храните эти же значения в записях подчиненной таблицы для сопоставления соответствующей записи главной таблицы. Такие ситуации весьма распространены. Предположим, что у вас есть таблица invoice с информацией о счетах по заказам пользователей и таблица inv_item, перечисляющая позиции для каждого счета. Здесь invoice является главной таблицей, а inv_item – подчиненной. Для однозначнойидентификации каждого заказа таблица invoice могла бы содержать столбец AUTO_INCREMENT с именем inv_id. Также можно было бы хранить соответствующий номер счета в каждой записи таблицы inv_item, чтобы определить, к какому счету она относится.


Таблицы могут выглядеть как-то так:

CREATE TABLE invoice
(
inv_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (inv_id),
date DATE NOT NULL
# ... здесь могут быть другие столбцы
# ... (идентификатор клиента, адрес доставки и т. д.)
);
CREATE TABLE inv_item
(
inv_id INT UNSIGNED NOT NULL, # идентификатор счета (из таблицы invoice)
INDEX (inv_id),
qty INT, # количество
description VARCHAR(40) # описание
);

При подобном связывании таблиц обычно сначала вставляется запись в главную (master) таблицу (для формирования значения AUTO_INCREMENT, идентифицирующего запись), затем вставляется запись подчиненной (detail) таблицы, получающая идентификатор записи главной таблицы от функции LAST_INSERT_ID(). Например, если клиент покупает молоток (hammer), три коробки гвоздей (nails) и (предвидя разбитые пальцы) дюжину бинтов (bandage), то относящиеся к заказу записи могут быть вставлены в две таблицы следующим образом:

INSERT INTO invoice (inv_id,date)
VALUES(NULL,CURDATE());
INSERT INTO inv_item (inv_id,qty,description)
VALUES(LAST_INSERT_ID(),1,'hammer');
INSERT INTO inv_item (inv_id,qty,description)
VALUES(LAST_INSERT_ID(),3,'nails, box');
INSERT INTO inv_item (inv_id,qty,description)
VALUES(LAST_INSERT_ID(),12,'bandage');

Первое предложение INSERT добавляет запись в главную таблицу invoice и генерирует новое значение AUTO_INCREMENT для ее столбца inv_id.


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

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

Если подчиненная таблица содержит собственный столбец AUTO_INCREMENT, то добавлять записи в таблицы нужно еще осторожнее. Предположим, что требуется последовательно пронумеровать строки таблицы inv_item для каждого заказа. Для этого создаем многостолбцовый индекс AUTO_INCREMENT, генерирующий отдельную последовательность для составляющих каждого счета.


Создаем таблицу inv_item, используя PRIMARY KEY, объединяющий столбец inv_id со столбцом AUTO_INCREMENT, который называется seq:

CREATE TABLE inv_item (
inv_id INT UNSIGNED NOT NULL, # идентификатор счета (из таблицы invoice)
seq INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (inv_id, seq),
qty INT, # количество
description VARCHAR(40) # описание
);

Столбец inv_id позволяет сопоставить каждую строку inv_item соответствующей записи таблицы invoice, как и в исходной таблице. Дополнительно индекс обеспечивает последовательную нумерацию, начиная с 1, значений seq для элементов каждого счета. Однако теперь, когда обе таблицы содержат столбец AUTO_INCREMENT, вводить информацию о счете так же, как раньше, уже нельзя. Чтобы понять, почему, попробуем сделать следующее:

INSERT INTO invoice (inv_id,date)
VALUES(NULL,CURDATE());
INSERT INTO inv_item (inv_id,qty,description)
VALUES(LAST_INSERT_ID(),1,'hammer');
INSERT INTO inv_item (inv_id,qty,description)
VALUES(LAST_INSERT_ID(),3,'nails, box');
INSERT INTO inv_item (inv_id,qty,description)
VALUES(LAST_INSERT_ID(),12,'bandage');

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


Предложение INSERT для таблицы invoice выполняется корректно. Выполняется и первое предложение INSERT для таблицы inv_item; LAST_INSERT_ID() возвращает значение inv_id главной записи из таблицы invoice. Но это предложение INSERT генерирует и собственное значение AUTO_INCREMENT (для столбца seq), которое изменяет значение LAST_INSERT_ID(), и значение inv_id главной записи «пропадает». В результате последующие вставки в таблицу inv_item сохраняют значение seq предыдущей записи в столбце inv_id. Поэтому вторая и третья записи содержат некорректные значения inv_id.

Есть несколько путей решения проблемы. Один из них связан с использованием другого синтаксиса предложения INSERT для добавления подчиненных записей, другие предлагают сохранять значение AUTO_INCREMENT главной записи в переменной для дальнейшего использования:Одновременное добавление нескольких подчиненных записей. Одним из решений является добавление подчиненных записей с использованием синтаксиса INSERT MySQL, который позволяет добавить несколько строк в одном предложении. Тогда можно будет применить значение LAST_INSERT_ID() главной записи ко всем подчиненным:

INSERT INTO invoice (inv_id,date)
VALUES(NULL,CURDATE());
INSERT INTO inv_item (inv_id,qty,description) VALUES
(LAST_INSERT_ID(),1,'hammer'),
(LAST_INSERT_ID(),3,'nails, box'),
(LAST_INSERT_ID(),12,'bandage');

Использование переменной SQL.


Второй способ заключается в сохранении значения AUTO_INCREMENT главной записи в переменной SQL для использования при последующей вставке подчиненных записей:

INSERT INTO invoice (inv_id,date)
VALUES(NULL,CURDATE());
SET @inv_id = LAST_INSERT_ID();
INSERT INTO inv_item (inv_id,qty,description)
VALUES(@inv_id,1,'hammer');
INSERT INTO inv_item (inv_id,qty,description)
VALUES(@inv_id,3,'nails, box');
INSERT INTO inv_item (inv_id,qty,description)
VALUES(@inv_id,12,'bandage');

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

$dbh->do ("INSERT INTO invoice (inv_id,date) VALUES(NULL,CURDATE())");
$inv_id = $dbh->{mysql_insertid};
$sth = $dbh->prepare ("INSERT INTO inv_item (inv_id,qty,description)
VALUES(?,?,?)");
$sth->execute ($inv_id, 1, "hammer");
$sth->execute ($inv_id, 3, "nails, box");
$sth->execute ($inv_id, 12, "bandage");

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

Статьи из раздела MySQL на эту тему:
Генераторы однострочных последовательностей
Генерирование значений последовательности
Добавление последовательности в существующую таблицу
Извлечение значений последовательности
Как начать последовательность с определенного значения