Используем sequence в MySQL

Kate

Administrator
Команда форума
d0b9abdc4316e505852889a59008546f.png

Хочу рассказать о том, как создать сквозной монотонно возрастающий целочисленный id в MySQL (это называется sequence в других БД). Зачем это нужно? Например, в системе есть два вида задач для сотрудников, у каждого вида задач есть свой набор атрибутов и свой жизненный цикл (набор статусов). Для того чтобы не городить огород в коде приложения для корректного чтения разных сущностей из БД и не плодить колонки с NULL лучше разнести сущности по разным таблицам.
Пример реальных таблиц для которых хотим создать сквозной id
Пример реальных таблиц для которых хотим создать сквозной id
Но при всем при этом хочется иметь возможность эти задачи уникально идентифицировать в рамках системы. Например, в сервисах "одно окно" в одном разделе приложения представлено много схожих, но разных по 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:
idname
1example 1
task_2:
idname
2example 2
sequence:
id
1
2
Но получается, что у нас в таблице 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
2
В некоторых ситуациях требуется сделать запись в другую таблицу, используя 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 на начальное значение.

 
Сверху