Хочу рассказать о том, как создать сквозной монотонно возрастающий  целочисленный id в MySQL (это называется sequence в других БД). Зачем это нужно? Например, в системе есть два вида задач для сотрудников, у каждого вида задач есть свой набор атрибутов и свой жизненный цикл (набор статусов). Для того чтобы не городить огород в коде приложения для корректного чтения разных сущностей из БД и не плодить колонки с NULL лучше разнести сущности по разным таблицам.

Но при всем при этом хочется иметь возможность эти задачи уникально идентифицировать в рамках системы. Например, в сервисах "одно окно" в одном разделе приложения представлено много схожих, но разных по workflow сущностей, как в примере выше. Но проблема в том, что если сущности лежат в разных таблицах, то у каждой из них свой autoincrement, и следовательно, нумерация будет пересекаться.

Один из вариантов решения это использование идентификации с помощью id + type. Но это несколько усложняет архитектуру, ведь об этом придется помнить как на backend, так и на frontend. И возникает возможность случайно совершить действие с другой задачей, просто-напросто перепутав id.

Альтернативное решение это использовать uuid, но он имеет ряд недостатков:

• uuid плохо влияет на работу primary key и не очень хорошо дружит с Btree индексом

• uuid разумеется занимает гораздо больше места, чем integer

• И пожалуй самый главный недостаток это то, что с ним не очень удобно работать, особенно когда он присутствует в ссылках на ресурс. Отсутствует «человекочитаемость». Например, ff86e090-0625-11ea-9f44-8c16456798f1 невозможно сходу запомнить в отличие от числа 1256. Особенно на это жалуются тестировщики и отдел сопровождения, когда пользователь приложил скриншот, на котором url c несколькими uuid, и им приходится аккуратно посимвольно переписывать их со скриншота.

Эмулировать sequence в MySQL довольно просто. Итак, начнем.

Подготовка

Для примера создадим две таблицы:

CREATE TABLE task_1 (
    id INT UNSIGNED NOT NULL DEFAULT 0,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE task_2 (
    id INT UNSIGNED NOT NULL DEFAULT 0,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

Для поля id у нас нет AUTOINCREMENT, потому что мы будем брать значение из нашего будущего sequence, DEFAULT 0 - потому что поле с primary key не может быть null, а следовательно, нужно иметь какое-то значение по умолчанию. Создадим так же таблицу, где будем хранить наш сквозной id.

CREATE TABLE sequence (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL
);

#1 Через INSERT

Будем реализовывать автоматическую выдачу нового id через триггеры для каждой из таблиц.

CREATE TRIGGER task_1_before_insert
  BEFORE INSERT ON 
    task_1
  FOR EACH ROW
BEGIN
  INSERT INTO sequence SET id=DEFAULT;
  SET NEW.id = LAST_INSERT_ID();
END;

CREATE TRIGGER task_2_before_insert
  BEFORE INSERT ON 
    task_2
  FOR EACH ROW
BEGIN
  INSERT INTO sequence SET id=DEFAULT;
  SET NEW.id = LAST_INSERT_ID();
END;

Данные триггеры будут срабатывать при каждом INSERT в таблицы task_1 и task_2. В начале, мы делаем вставку в таблицу sequence, а затем получаем последний id (LAST_INSERT_ID) и используем его для вставки в целевую таблицу (с помощью переменной NEW). Протестируем наше решение путем создания задач в каждой из таблиц.

INSERT INTO task_1 SET name = 'example 1';
INSERT INTO task_2 SET name = 'example 2';

В итоге таблицы будут содержать следующие данные:

task_1:

task_2:

sequence:

Но получается, что у нас в таблице sequenceпод каждый выданный id имеется запись. Не очень красиво, хотя и не критично... Хотелось бы просто иметь одну запись, в которой указан последний выданный id.

#2 Через UPDATE

Заново создадим таблицу sequence, но без AUTO_INCREMENT и проинициализируем наш сквозной id.

CREATE TABLE sequence (
  id INT UNSIGNED PRIMARY KEY NOT NULL
);

INSERT INTO sequence VALUES (0);

А затем перепишем триггеры:

CREATE TRIGGER task_1_before_insert
  BEFORE INSERT ON 
    task_1
  FOR EACH ROW
BEGIN
  UPDATE sequence SET id = LAST_INSERT_ID(id+1), NEW.id = LAST_INSERT_ID();
END;

CREATE TRIGGER task_2_before_insert
  BEFORE INSERT ON 
    task_2
  FOR EACH ROW
BEGIN
  UPDATE sequence SET id = LAST_INSERT_ID(id+1), NEW.id = LAST_INSERT_ID();
END;

Согласно документации LAST_INSERT_ID() может принимать выражение, результат которого он запомнит и при следующем вызове его вернет. Мы сначала увеличиваем id в таблице sequence и кэшируем результат, а затем обращаемся к закэшированному значению и передаем его в переменную NEW.

Теперь, если снова сделать вставки в таблицы task_1 и task_2, то sequence будет содержать одну единственную запись с номером последнего выданного id.

sequence:

В некоторых ситуациях требуется сделать запись в другую таблицу, используя id созданной сущности, например, связать созданную задачу и пользователей. Но есть одна проблемка, если мы захотим в коде нашего серверного приложения (PHP, NodeJS, Python и т.д.) получить id, только что выданный при вставке в таблицу задач, то мы получим 0.

INSERT INTO task_1 SET name = 'example 3';
SELECT LAST_INSERT_ID(); -- Result: 0

Это связано с тем, что триггер не изменяет поведение LAST_INSERT_ID() для нашей таблицы, а значение по умолчанию для колонки id в таблице task_1 является 0.

При этом в INSERT мы также не можем передать конкретный id, ведь в коде триггера мы перезаписываем его, беря из sequence. Исправляется эта проблема довольно легко:

CREATE TRIGGER task_1_before_insert
  BEFORE INSERT ON 
    task_1
  FOR EACH ROW
BEGIN
  IF NEW.id IS NULL OR NEW.id = 0 THEN
    UPDATE sequence SET id = LAST_INSERT_ID(id+1), NEW.id = LAST_INSERT_ID();
  END IF;
END

Мы просто проверим, а есть ли id и не равен ли он дефолтному значению(0) и только в этом случае будем использовать наш sequence.

UPDATE sequence SET id = LAST_INSERT_ID(id+1); --- вручную выделили новый id

INSERT INTO task_1 SET id = LAST_INSERT_ID(), name = 'example 4';

INSERT INTO task_for_user SET task_id = LAST_INSERT_ID(), user_id = 1;

Мы вручную увеличили наш sequence, использовали полученный id для создания задачи, а также для записи информации о созданной задаче в другую таблицу. При желании инкрементирование sequence можно завернуть в функцию, чтобы не писать каждый раз подобный запрос. Этим мы и займемся в следующем разделе.

#3 Через nextval

Сделаем имитацию функции nextval как в других БД, типа Oracle.

CREATE TABLE sequence (
  id INT UNSIGNED PRIMARY KEY NOT NULL,
  -- с помощью name мы можем хранить множество sequence в одной таблице.
  name VARCHAR(255) NOT NULL
);

INSERT INTO sequence SET id = 0, name = 'my_sequence';

Дальше создадим функцию nextval, которая принимает на вход имя sequence и возвращает новый id.

CREATE FUNCTION `nextval`(`seq_name` VARCHAR(255)) RETURNS INT UNSIGNED
    MODIFIES SQL DATA
    DETERMINISTIC
BEGIN
  UPDATE sequence SET id = LAST_INSERT_ID(id+1) WHERE name = seq_name;
  RETURN LAST_INSERT_ID();
END

Теперь мы можем получать новый id с помощью следующего запроса:

SELECT nextval('my_sequence'); -- 1
SELECT nextval('my_sequence'); -- 2

Можем теперь снова переписать наш триггер c использованием nextval

CREATE TRIGGER task_1_before_insert
  BEFORE INSERT ON 
    task_1
  FOR EACH ROW
BEGIN
  IF NEW.id IS NULL OR NEW.id = 0 THEN
    SET NEW.id = (SELECT nextval('my_sequence'));
  END IF;
END

При желании nextval можно кастомизировать, например, вторым параметром указывать на сколько инкрементировать (nextval('my_sequence', 10)) sequence, чтобы можно было создать множество id, или сделать, чтобы sequence выдавал только четные id, в рамках какого-то диапазона (например, от 0...1000), или даже можно зациклить его.

Итог

Мы реализовали тремя несложными способами создание уникального сквозного id. Использовать sequence бездумно не стоит, ведь в большинстве случаев обычного autoincrement хватит за глаза.

Но он очень удобен, если:

• необходимо сущности уникально проидентифицировать в рамках одной БД, но при этом у каждой из этих сущностей свой набор атрибутов и их невозможно хранить в одной таблице.

• нам нужно в коде приложения до INSERT самой сущности создать Value Object и нам необходим id чтобы полноценно работать с ней, вместо uuid можно воспользоваться sequence.

• с помощью зацикливания sequence можно генерить номер заказа как в Макдональс: при достижении максимального значения сбрасывается значение sequence на начальное значение.