MySQL / 11. Формирование и использование последовательностей

Управление несколькими столбцами AUTO_INCREMENT одновременно

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

Решение
Сохраните значения в переменных SQL на будущее. Если вы используете запросы внутри программы, сохраните значения последовательности в переменных программы. А может быть, вам удастся создавать запросы, используя разные объекты соединения или предложения, чтобы не смешивать их.

Обсуждение
Функция-индикатор значения последовательности на стороне сервера LAST_INSERT_ID() устанавливается при каждом генерировании запросом значения AUTO_INCREMENT, в то время как клиентские индикаторы могут восстанавливаться после каждого запроса. Предположим, что вы хотите запустить предложение, генерирующее значение AUTO_INCREMENT, но использовать это значение только после того, как запущено второе предложение, тоже генерирующее значение AUTO_INCREMENT. Тогда первое значение уже не будет доступно ни как значение LAST_INSERT_ID(), ни как какое-то клиентское значение. Чтобы вновь получить доступ к исходному значению, необходимо сохранить его перед выполнением второго предложения.

Есть несколько способов сделать это:

• На уровне SQL можно сохранить значение в переменной SQL после запуска запроса, генерирующего значение AUTO_INCREMENT:

INSERT INTO имя_таблицы (id,...) VALUES(NULL,...);
SET @saved_id = LAST_INSERT_ID();

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

• На уровне API можно хранить значение AUTO_INCREMENT в переменной базового языка. Для этого можно сохранить значение, возвращаемое LAST_INSERT_ID() или любым доступным специальным расширением API.

• Третий способ можно использовать в тех API, которые обеспечивают поддержку независимых клиентских значений AUTO_INCREMENT. Например, в Python при использовании объекта курсора для выполнения запроса для доступа к значению AUTO_INCREMENT, генерируемому запросом, вызовите метод курсора insert_id(). Если вы создаете другие запросы, используя тот же курсор, то значение будет потеряно. Однако если использовать
другой объект курсора для выполнения дополнительных запросов, то исходное значение insert_id останется неизменным:

cursor1 = conn.cursor ()
cursor2 = conn.cursor ()
gen_seq_val (cursor1) # создать запрос, формирующий
# номер последовательности
gen_seq_val (cursor2) # создать еще запрос, используя другой курсор
seq1 = cursor1.insert_id ()
seq2 = cursor2.insert_id ()
print "seq1:", seq1, "seq2:", seq2 # эти значения будут различными
cursor1.close ()
cursor2.close ()

В Perl тот же эффект можно получить, используя два дескриптора предложений; атрибут mysql_insertid каждого из них не изменяется при вы-полнении запросов для другого. В Java используйте разные объекты Statement или PreparedStatement.

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

Вы правы, это сработало бы, но в данном случае цель не оправдывает приложенных усилий. Затраты на открытие второго соединения гораздо выше, чем на простое сохранение значения mysql_insert_id() в переменной PHP перед запуском второго запроса. Более того, открыть второе соединение не так просто, как кажется. Если выполнить второй вызов mysql_connect() или mysql_pconnect() с теми же параметрами соединения, что и первоначальный вызов, то PHP вернет тот же идентификатор соединения, что и в первый раз!

Для того чтобы получить действительно другой идентификатор соединения, вам нужно будет подключиться к серверу под другим именем пользователя. (Рискуя замутить воду, все же отмечу, что начиная с PHP 4.2.0 mysql_connect() поддерживает опцию явного указания на необходимость открытия нового соединения. Вы можете использовать эту возможность для хранения разных клиентских значений AUTO_INCREMENT.)

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

Генераторы однострочных последовательностей
Использование значений AUTO_INCREMENT для связывания таблиц
Последовательная нумерация строк вывода запроса
Формирование повторяющихся последовательностей