Извлечение значений последовательности

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

Решение
В предложении SQL можно использовать функцию LAST_INSERT_ID(). Если вы пишете программу, то ваш API для MySQL может предлагать какой-то способ получения значения без непосредственного использования LAST_INSERT_ID().

Обсуждение
Во многих приложениях требуется определить значение AUTO_INCREMENT только что созданной записи. Например, если вы честолюбивы и беретесь за создание веб-интерфейса для ввода записей в таблицу insect Джуниора, то можете сделать так, чтобы приложение отображало каждую новую запись красиво отформатированной на новой странице сразу же после того, как вы нажимаете кнопку Submit. Для этого необходимо знать новое значение id, чтобы извлечь соответствующую запись. Другая распространенная ситуация, в которой требуется значение AUTO_INCREMENT, возникает, когда используется несколько таблиц: после вставки записи в главную таблицу обычно необходимо извлечь ее идентификатор, чтобы создать в других таблицах записи, ссылающиеся на главную.

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


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

Получение значений AUTO_INCREMENT с помощью функции LAST_INSERT_ID()
Очевидный (но неверный) способ определения значения AUTO_INCREMENT новой записи использует тот факт, что генерируемое MySQL значение становится максимальным номером последовательности в столбце. То есть можно попробовать использовать для его извлечения функцию MAX():

SELECT MAX(id) FROM insect;

Но такой способ ненадежен, так как он не принимает в расчет многопоточность сервера MySQL. Запрос SELECT действительно возвращает максимальное значение id в таблице, но это значение вполне может быть сформировано не вами. Предположим, что вы вставляете запись, которая получает значение id, равное 9. Если другое клиентское приложение вставит запись, преждечем вы запустите запрос SELECT, значением MAX(id) будет 10, а не 9. Для решения проблемы можно сгруппировать предложения INSERT и SELECT в транзакцию или заблокировать таблицу, но MySQL предоставляет и более простой способ получения правильного значения – функцию LAST_INSERT_ID().


Она возвращает последнее из значений AUTO_INCREMENT, сгенерированное вами в рамках соединения с сервером. Например, можно вставить запись в таблицу insect, а затем извлечь ее значение id так:

mysql> INSERT INTO insect (name,date,origin)
-> VALUES('cricket','2001-09-11','basement');
mysql> SELECT LAST_INSERT_ID();

+------------------+
| last_insert_id() |
+------------------+
| 9 |
+------------------+

Или можно использовать новое значение для извлечения всей записи, даже не зная ее идентификатора:

mysql> INSERT INTO insect (name,date,origin)
-> VALUES('moth','2001-09-14','windowsill');
mysql> SELECT * FROM insect WHERE id = LAST_INSERT_ID();

+---+-------+--------------+------------+
| id | name | date | origin |
+---+-------+--------------+------------+
| 10 | moth | 2001-09-14 | windowsill |
+---+-------+--------------+------------+

Использование API для получения значений AUTO_INCREMENT
LAST_INSERT_ID() – это функция SQL, так что вы можете использовать ее в любом клиентским приложении, умеющем создавать предложения SQL.


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

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

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

Если вы используете для запуска запроса prepare() и execute(), обращайтесь к mysql_insertid как к атрибуту дескриптора предложения:

my $sth = $dbh->prepare ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
$sth->execute ();
my $seq = $sth->{mysql_insertid};PHP

После запуска запроса, формирующего значение AUTO_INCREMENT, извлеките значение, вызвав mysql_insert_id():

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Аргументом функции является идентификатор соединения.


Если аргумент не указан, mysql_insert_id() использует последнее открытое соединение.

Python
Драйвер MySQLdb для API DB предоставляет метод курсора insert_id() для получения значений последовательности. Используйте его с объектом курсора, через который выполняется запрос, формирующий значение AUTO_INCREMENT:

cursor = conn.cursor ()
cursor.execute ("""
INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')""")
seq = cursor.insert_id ()

Java
Драйвер JDBC MySQL Connector/J предлагает метод getLastInsertID() для получения значений AUTO_INCREMENT. Метод может использоваться с объектами как Statement, так и PreparedStatement. Рассмотрим на примере Statement:

Statement s = conn.createStatement ();
s.executeUpdate (
"INSERT INTO insect (name,date,origin)"
+ " VALUES('moth','2001-09-14','windowsill')");
long seq = ((com.mysql.jdbc.Statement) s).getLastInsertID ();
s.close ();

Заметим, что поскольку getLastInsertID() зависит от драйвера, для получения доступа к методу необходимо преобразовать объект Statement к типу com.mysql.jdbc.Statement.


Если используется объект PreparedStatement, приведите его к типу com.mysql.jdbc.PreparedStatement:

PreparedStatement s = conn.prepareStatement (
"INSERT INTO insect (name,date,origin)"
+ " VALUES('moth','2001-09-14','windowsill')");
s.executeUpdate ();
long seq = ((com.mysql.jdbc.PreparedStatement) s).getLastInsertID ();
s.close ();

Сравнение серверного и клиентского способов извлечения значений последовательности
Как уже говорилось, значение LAST_INSERT_ID() поддерживается в рамках соединения на серверной стороне соединения MySQL. А API-способы, обеспечивающие прямой доступ к значениям AUTO_INCREMENT, реализованы на клиентской стороне. Серверный и клиентский приемы извлечения значений последовательности имеют как сходства, так и различия.

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

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

• Значение последовательности, доступное на клиентской стороне соединения, обычно устанавливается для всех запросов, а не только для генерирующих значения AUTO_INCREMENT. Если запускается предложение INSERT, формирующее новое значение, затем выполняется какой-то другой запрос, то, обратившись за значением последовательности на клиентской стороне, вы, вероятно, получите ноль. Конкретное положение дел зависит от используемого API, но для надежности я бы рекомендовал руководствоваться таким общим правилом: если запрос формирует значение последовательности, которое вы не планируете сразу же использовать, сохраните его в переменной, на которую вы сможете затем сослаться. В противном случае при обращении за значением последовательности может оказаться, что оно уже ликвидировано.

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

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