Эмуляция nextval для получения последовательности в MySQL

Оригинал: http://www.microshell.com/database/mysql/emulating-nextval-function-to-get-sequence-in-mysql/, автор Maresa.

        Бывают случаи, когда обычного автоинкремента становится недостаточно, и то, что вам нужно — это последовательности. К сожалению, в MySQL поддержки последовательностей нет. Кто работал с СУБД PostgreSQL, знает, что функция nextval() очень полезна. В этой статье я опишу, как средствами MySQL можно эмулировать nextval из PostgreSQL.

Чего мы хотим достичь

        В PostgreSQL вам сперва нужно создать последовательность, чтобы затем можно было обращаться к ней при помощи nextval(). Я собираюсь средствами MySQL создать nextval() с функционалом как можно более приближенным к nextval() из PostgreSQL. Итак, nextval() в PostgreSQL используется следующим образом:

?

1 2 3

-- Выполните этот код в PostgreSQL, -- заменив sequence_name на имя вашей последовательности SELECT nextval('sequence_name');

Если вы попытаетесь исполнить приведённый выше код в MySQL, то получите ошибку 1305, функция nextval не существует. Мы хотим заставить этот код работать в MySQL точно таким же образом, как он работает в PostgreSQL.

Структура базы данных

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

?

1 2 3 4 5 6 7 8 9 10

CREATE DATABASE `sequence`; CREATE TABLE `sequence`.`sequence_data` (     `sequence_name` varchar(100) NOT NULL,     `sequence_increment` int(11) unsigned NOT NULL DEFAULT 1,     `sequence_min_value` int(11) unsigned NOT NULL DEFAULT 1,     `sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615,     `sequence_cur_value` bigint(20) unsigned DEFAULT 1,     `sequence_cycle` boolean NOT NULL DEFAULT FALSE,     PRIMARY KEY (`sequence_name`) ) ENGINE=MyISAM;

        Небольшие разъяснения
        Я определяю последовательность как положительное число, способное только увеличиваться. Поэтому все колонки созданы как unsigned. Если вам нужна последовательность, которая может принимать отрицательные значения, уберите свойство unsigned.

        Я также хочу, чтобы шаг последовательности мог быть любым, не только 1. Например, для того, чтобы получать только чётные или только нечётные числа, достаточно будет установить соответствующее начальное значение и sequence_increment установить равным 2.

        Последняя колонка (sequence_cycle) — это флаг, определяющий, может ли последовательность сбрасываться в начальное значение при достижении своего максимума. Кроме этого, sequence_cur_value может принимать значение NULL, я воспользуюсь этим при переполнении, если sequence_cycle равен false (для сигнализации об ошибке).

Создаём последовательность

        Создание последовательности — это обычное добавление записи в таблицу

?

1 2 3 4 5 6 7 8 9 10 11 12

-- Создаём последовательность со значениями полей по умолчанию INSERT INTO sequence.sequence_data     (sequence_name) VALUE     ('sq_my_sequence') ; -- Можно установить собственный значения INSERT INTO sequence.sequence_data     (sequence_name, sequence_increment, sequence_max_value) VALUE     ('sq_sequence_2', 10, 100) ;

Определяем nextval() в MySQL

        Теперь, когда у нас есть структура данных и создано несколько последовательностей, давайте посмотрим на определение функции nextval() в MySQL:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

CREATE FUNCTION `nextval` (`seq_name` varchar(100))     RETURNS bigint(20) NOT DETERMINISTIC BEGIN     DECLARE cur_val bigint(20);     SELECT         sequence_cur_value INTO cur_val     FROM         sequence.sequence_data     WHERE         sequence_name = seq_name     ;     IF cur_val IS NOT NULL THEN         UPDATE             sequence.sequence_data         SET             sequence_cur_value =                 IF (                     (sequence_cur_value + sequence_increment) > sequence_max_value,                     IF (                         sequence_cycle = TRUE,                         sequence_min_value,                         NULL                     ),                     sequence_cur_value + sequence_increment                 )         WHERE             sequence_name = seq_name         ;     END IF;     RETURN cur_val; END;

        Если вы используете SQLyog, то для создания функции щёлкните правой кнопкой мыши на "Functions", а затем на "create function", либо так: Objects -> Functions -> Create Function…, а затем скопируйте приведённый код в открывшееся окно.

Разбор функции nextval()

        Давайте взглянем на функцию. Я определяю функцию как NOT DETERMINISTIC, потому что вызов функции с неизменными аргументами не гарантирует одинаковое возвращаемое значение. Вообще-то, она никогда не должна возвращать один и тот же результат, за исключением случаев переполнения с выставленным флагом sequence_cycle.

        После определения переменных мы получаем текущее значение последовательности из таблицы, записывая результат в переменную cur_val (строки 5 – 11). Затем, если cur_val не равен NULL (помните, колонка sequence_cur_value определена таким образом, что может принимать значение NULL), мы хотим увеличить cur_val, чтобы в следующий раз при вызове функция была готова вернуть следующее значение последовательности.

        Обновить значения sequence_cur_val не так просто. Мы должны принять во внимание следующее:

1. Если следующее значение укладывается в пределы последовательности, мы просто увеличиваем sequence_cur_val на sequence_increment.
2. Если же оно за разрешёнными пределами последовательности, то нужно проверить
   
   • Если sequence_cycle установлено (равно true), сбросить sequence_cur_val в начальное состояние sequence_min_val.
   • Если sequence_cycle равно false, установить sequence_cur_val в NULL, тем самым обозначив состояние переполнения / ошибки.

И наконец возвращаем cur_val. Заметьте, что select в строках 5 – 11 вернёт NULL, если последовательность с указанным именем не существует. Таким образом, nextval() возвращает NULL в 2 случаях (и оба сигнализируют об ошибке):

1. Если последовательность не существует
2. Если значение последовательности вышло за установленные пределы

Как вызывать функцию nextval() в MySQL

        Теперь, когда структуру данных определена, и сама функция готова, мы можем просто вызвать nextval(). Измените запрос соответственно вашим условиям, которые обсуждались ранее: работаете ли вы с отдельной базой или нет.

?

1	SELECT nextval('sq_my_sequence') as next_sequence;

        Вот и всё. Есть одна ловушка с этой функцией, о которой написано в следующей моей статье. Помните о репликации, когда пишете SQL. Надеюсь, статья поможет вам. Как обычно, я жду комментарии, вопросы, критику, которые помогут мне и остальным лучше разобраться в материале.

---

        Дополнение от переводчика
        Эта же проблема более подробно разобрана в статьях "MySQL. Генерация числовых последовательностей. Эмуляция SEQUENCE.", см. части 1 и 2. Авторы обеих статей (смотрите комментарии к оригиналу) приходят к выводу, что в реальных условиях использовать такой метод нельзя из за проблем с неатомарностью операций либо блокированием транзакций.