Как сделать последовательность (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 специалиста
Добавить комментарий

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