Эмуляция 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 используется следующим образом:

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

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

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

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

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 (для сигнализации об ошибке).

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

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

-- Создаём последовательность со значениями полей по умолчанию
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:

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(). Измените запрос соответственно вашим условиям, которые обсуждались ранее: работаете ли вы с отдельной базой или нет.

SELECT nextval('sq_my_sequence') as next_sequence;

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

---

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