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

Генераторы однострочных последовательностей

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

Решение
Примените другой механизм формирования последовательности, который использует всего одну строку.

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

Чтобы подсчитывать события одного типа, можно использовать тривиальную таблицу, состоящую из одной строки и одного столбца. Например, если вы продаете книгу под названием «Red Horse Hill», то можете создать и инициализировать таблицу для записи результатов продаж следующим образом:

CREATE TABLE red_horse_hill (copies INT UNSIGNED);
INSERT INTO red_horse_hill (copies) VALUES(0);

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

CREATE TABLE booksales
(
title VARCHAR(60) NOT NULL, # название книги
copies INT UNSIGNED NOT NULL, # продано экземпляров
PRIMARY KEY (title)
);
Инициализируем таблицу, добавляя строку для каждой книги:
mysql> INSERT INTO booksales (title) VALUES
-> ('Red Horse Hill'),
-> ('Sparkplug of the Hornets'),
-> ('Bulldozer'),
-> ('The Long Trains Roll'),
-> ('Who Rides in the Dark?');
mysql> SELECT * FROM booksales;

+------------------------------+--------+
| title                                   | copies |
+------------------------------+--------+
| The Long Trains Roll          | 0        |
| Bulldozer                           | 0        |
| Sparkplug of the Hornets | 0        |
| Red Horse Hill                   | 0        |
| Who Rides in the Dark?    | 0        |
+------------------------------+--------+

Таблица создана. Как ее использовать? Можно увеличивать столбец copies для указанной книги, создавая простое предложение UPDATE с названием книги:

UPDATE booksales SET copies = copies+1 WHERE title = 'Bulldozer';

Для извлечения счетчика (чтобы можно было, например, вывести для клиента сообщение типа «вы купили n-ый экземпляр книги») создайте запрос SELECT для книги с этим названием:

SELECT copies FROM booksales WHERE title = 'Bulldozer';

К сожалению, такой способ на самом деле не очень хорошо работает. Предположим, что в промежуток времени между предложениями UPDATE и SELECT кто-то другой покупает экземпляр книги, тем самым увеличивая значение copies. Тогда предложение SELECT фактически выведет не то значение счетчика продаж, которое получено путем прибавления вашего экземпляра, а самое последнее значение счетчика. Иначе говоря, другие клиенты могут изменить значение прежде, чем вы успеете его извлечь. Проблема похожа на обсуждавшуюся ранее и возникающую при извлечении последнего значения AUTO_INCREMENT из столбца при помощи вызова MAX(имя_столбца), а не LAST_INSERT_ID().

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

UPDATE booksales SET copies = LAST_INSERT_ID(copies+1)
WHERE title = 'Bulldozer';

Затем можно вызвать LAST_INSERT_ID() без аргументов для извлечения значения:

SELECT LAST_INSERT_ID();

Обновляя таким образом столбец copies, вы всегда можете получить присвоенное значение, даже если какое-то другое клиентское приложение его тем временем изменило. Если вы запускаете предложение UPDATE из API, обеспечивающих непосредственное извлечение последнего значения AUTO_INCREMENT, то можно даже не создавать запрос SELECT. Например, в Python вы можете обновить счетчик и получить новое значение, используя метод insert_id():

cursor = conn.cursor ()
cursor.execute ("""
UPDATE booksales SET copies = LAST_INSERT_ID(copies+1)
WHERE title = 'Bulldozer'
""")
count = cursor.insert_id ()

В Java эта операция выглядит так:

Statement s = conn.createStatement ();
s.executeUpdate (
"UPDATE booksales SET copies = LAST_INSERT_ID(copies+1)"
+ " WHERE title = 'Bulldozer'");
long count = ((com.mysql.jdbc.Statement) s).getLastInsertID ();
s.close ();

Последовательность, сформированная с использованием LAST_INSERT_ID(), обладает свойствами, отличными от настоящих последовательностей AUTO_INCREMENT:

• Значения AUTO_INCREMENT каждый раз увеличиваются на единицу, в то время как значения счетчика, генерируемые LAST_INSERT_ID(выражение) могут увеличиваться на любое значение. Например, чтобы сформировать последовательность 10, 20, 30,…, каждый раз увеличивайте счетчик на 10. Необязательно даже каждый раз увеличивать счетчик на одно и то же значение. Если вы продали двенадцать экземпляров книги, а не один, измените значение счетчика ее продаж так:

UPDATE booksales SET copies = LAST_INSERT_ID(copies+12)
WHERE title = 'Bulldozer';

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

• Чтобы вернуть счетчик в исходное значение, просто назначьте ему нужное значение. Предположим, что вы хотите сообщить покупателям книг о продажах текущего месяца, а не об общем объеме продаж (например, выводя сообщение типа «вы являетесь n-ым покупателем месяца»). Для обнуления счетчика в начале каждого месяца выполните такой запрос:

UPDATE booksales SET copies = 0;

• Одно не столь приятное свойство заключается в том, что значение, формируемое вызовом LAST_INSERT_ID(выражение), не всегда доступно для извлечения клиентскими приложениями. Его можно получить после запросов UPDATE и INSERT, но не для предложений SET. Если генерировать значение следующим образом (в Perl), то клиентское значение, возвращенное mysql_insertid, будет равно 0, а не 48:

$dbh->do ("SET \@x = LAST_INSERT_ID(48)");
$seq = $dbh->{mysql_insertid};Чтобы в этом случае получить значение, необходимо запросить его у сер-
вера:
$seq = $dbh->selectrow_array ("SELECT LAST_INSERT_ID()");

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

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