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

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

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

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

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

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

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

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

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

Теперь перейдем к практике и для начала создадим саму таблицу, я ее назвал 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*/
      SELECT max(id) INTO  _id  FROM  sequence  WHERE  stat = 1;/*Выбираем максимальный id и 
								записываем в переменную _id*/
      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()

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

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