Как сделать последовательность (sequence) в MySQL?

Всем известно, что такого объекта как «Последовательность» или «Sequence» в базе MySql нет, поэтому очень часто приходиться самим реализовывать последовательность. Сегодня я расскажу об одном способе, который использовал я, когда мне понадобился данный объект в MySql.

Начнем как всегда с теории, для того чтобы Вы понимали, о чем идет речь.

Последовательность (sequence)  – это объект базы данных, при обращении к которому будет возвращаться в отсортированном виде уникальное число с тем инкрементом, который Вы сами зададите.

Вообще, в других СУБД, кроме инкремента, можно задавать много параметров, например минимальное, максимальное значение и так далее.

Как было сказано выше, в MySql отсутствует данный объект, и многие выходят из этой ситуации путем создания в таблице столбца с auto_increment, в этом случае добавление новой записи в таблицу будет всегда с уникальным значением в данном столбце. Но это не всегда спасает, например, мне как-то раз понадобилось возвращать в отсортированном виде уникальное значение, а записи велись в разных таблицах, поэтому auto_increment мне не подошел, в другой СУБД на помощь мне пришла бы «Последовательность», но так как реализация была сделана в MySql, пришлось придумать свою последовательность.

На самом деле все примитивно и просто, сначала расскажу сам алгоритм. Создается таблица с двумя полями, одно из них auto_increment, другое, статус значения (я его так назвал:)).

Затем пишется функция, которая будет добавлять новое строку в эту таблицу и удалять предыдущую, для того чтобы таблица не росла, а функция работала максимально быстро. Что именно возвращать, а что удалять разруливается как раз полем «Статус».

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

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

Теперь перейдем к практике, и для начала создадим саму таблицу, я ее назвал sequence (база данных у меня с названием test):

     
   CREATE TABLE test.sequence(
        id BIGINT(20) NOT NULL AUTO_INCREMENT,
        stat TINYINT(1) NOT NULL,
     PRIMARY KEY (id)
   )
        ENGINE = INNODB
        AUTO_INCREMENT = 1
        AVG_ROW_LENGTH = 16384
   CHARACTER SET utf8
   COLLATE utf8_general_ci;

где,

  • id – это как раз уникальное значение, поле с auto_increment;
  • stat – это статус последнего значения, может быть 0 или 1 (0 – последнее значение, 1 – новое значение), но Вы это наглядно не увидите, так как у Вас в таблице всегда будет одна строка с последним возвращенным значением и статусом 0.

Теперь напишем функцию, при обращении к которой нам будет возвращаться уникальное число. Я ее назвал next_id (), и везде, где нужно получить уникальное значение обращайтесь к этой функции и все.

   
   CREATE DEFINER = 'root'@'localhost'
   FUNCTION test.next_id()
     RETURNS bigint(20)
   BEGIN
     DECLARE _id BIGINT; /*Объявляем переменную*/
     INSERT sequence (stat) VALUES (1);/*Вставка нового значения stat = 1*/
        /*Выбираем максимальный id и записываем в переменную _id*/
                SELECT max(id) INTO _id FROM  sequence WHERE  stat = 1;
      DELETE FROM sequence WHERE stat = 0;/*Удаляем строку со значением stat = 0*/
      UPDATE sequence SET stat = 0 WHERE stat = 1;/*Обновляем поле stat на 0*/
   RETURN _id;/*Возвращаем id*/
   END

Если Вы немного знаете sql, то проблем с описанием что делает эта функция, у Вас не возникнет, но на всякий случай я прокомментировал каждое действие.

Если и комментарии Вам непонятны, то прочитайте сначала про основы sql, например, вот эти статьи:

Воспользоваться этой функцией (или просто проверить) можно с помощью запроса:

   
   SELECT next_id()

Использовать ее можно и в других функциях и, конечно же, в процедурах.

Именно вот таким способом я вышел из своей ситуации, надеюсь, Вам это тоже поможет. Удачи!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 1
  1. Аватар
    Андрей

    Интересное решение, тоже плевался на Oracle из-за этого, ну что же такое придумали?!?!?
    А потом распознал вкус, получил ID в коде, и делай с ним что хочешь :) он только твой. В то время как в MySQL пока не выполнишь, не получишь новый ID. Понимаю проблема высосана из пальца, но тем не менее. Интересно можно ли эту функцию переделать так, чтобы параметром этой функции была таблица, ну сделать такой генератор для разных таблиц. Ну и по моему очень много операций. Можно было бы например получать ID, а раз в сутки скажем чистить таблицу, чтобы она не увеличивалась в размерах.

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.