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