Как создать порядковый номер для заказа конкретного заведения?

Question

[lexstile]

Есть таблица orders - заказы с привязкой к проекту (заведению).
Хочется, чтобы был порядковый номер заказа в срезе заведения.
Как можно реализовать данный механизм? (как я понял, получение количества перед созданием не дает 100% гарантии, что порядковые номера не совпадут - например, когда одномоментно создается несколько заказов).

Основная проблема - это постоянно увеличивающийся номер заказа, т. е. в срезе одного заведения будут заказы с номерами (id) №3, №10, №34... (неудобно).

Comments

[Константин Цветков]

Укажите на какой СУБД это необходимо сделать. Возможны разные варианты.

[lexstile]

Константин Цветков, прошу прощения, что не указал - MySQL

[Дмитрий]

не надо этого хотеть.

[Akina]

Элементарно делается триггером и опорной MyISAM-таблицей со вторичным автоинкрементом. Concurrent-safe.

прошу прощения, что не указал - MySQL

Для MySQL всегда указывайте точную версию.

Answer 1

[psiklop]

Если блокировать таблицу не совпадут.
LOCK TABLES orders WRITE
тут получаем максимальный номер
пишем новый заказ
UNLOCK TABLES

Comments

[lexstile]

Интересно, спасибо, не знал про такой способ.

[tukreb]

ещё как вариант сделать таблицу очередь, через которую проходят заказы, а потом уходят получая свои номера.
Конечно это относительно "дорогой" вариант :)

Answer 2

[Adamos]

Порядковый номер, небось, еще и сбрасываться должен каждый год?
Например, можно сделать два запроса: INSERT, при котором id таки назначится нормальным авто-инкрементом, и UPDATE, который заполнит отдельное поле number значением

SELECT count(id) + 1 
  FROM orders 
  WHERE id < :currentId AND project_id = 2 AND created_at >= '2022-01-01'

Никакие гонки это не запутают.
А если этот number нужен только в одном месте, его можно таким запросом получать динамически и не хранить нигде вообще.

Answer 3

[Akina]

Пример реализации.

-- рабочая таблица
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT,
  project_number INT
  );

-- опорная таблица для генерации номеров в разрезе проектов
CREATE TABLE generate_project_number (
  project_id INT,
  project_number INT AUTO_INCREMENT,
  PRIMARY KEY (project_id, project_number)
  ) ENGINE = MyISAM;

-- триггер-генератор
CREATE TRIGGER generate_project_number
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  -- вставить хапись в опорную таблицу для генерации номера
  INSERT INTO generate_project_number (project_id) VALUES (NEW.project_id);
  -- вставить сгенерированный номер в рабочую таблицу
  SET NEW.project_number = LAST_INSERT_ID();
  -- почистить опорную таблицу от лишних записей
  DELETE FROM generate_project_number 
  WHERE project_id = NEW.project_id
    AND project_number < NEW.project_number;
END

DEMO fiddle

Answer 4

[mayton2019]

Откажитесь от порядкового номера. Создайте код типа номер заведения + дата-время. И это будет коробочное решение вашей проблемы.

Все трюки с блокировкой таблицы могут параллизовать ваш бизнес. А зачем вам это надо?

Comments

[lexstile]

А если, например, считать номер заказа с начала открытия смены, тоже не вариант?
Т. е. делать это динамически, не записывая в БД.

[mayton2019]

lexstile, можно и так. Но в этом случае БД снимает с себя отвественность за учет номеров. Фактически если будет перегрузка сервера приложений то все счетчики будут забыты.

Вообще ведение счетчиков в условиях сетевой распределенной системы - это целая проблема. Про это статьи пишут. И придумывают всякие протоколы кворумов и голосований.

Но если ваша система такая простая - то делайте как вам проще.

Еще раз рассмотрите решение на базе sequence в БД. Это самое простое и надежное решение. Оно лучше чем все то что вы придумаете.