Значение автоматического увеличения для столбца, не являющегося первичным ключом
В моей MariaDB у меня есть две таблицы project и issue, таблица issue содержит все проблемы, созданные для всех проектов.
create table if not exists Project(
id integer PRIMARY KEY auto_increment,
`name` varchar(200) NOT NULL
);
create table if not exists Issue(
id integer PRIMARY KEY AUTO_INCREMENT,
project_id integer,
issue_number integer,
content text,
FOREIGN KEY (project_id)
REFERENCES Project(id)
);
issue_number указан для каждого проекта и всегда начинается с 1, как можно увеличить его на 1 и решить проблемы с одновременной вставкой?
Я не могу использовать select max(issue_number) + 1 from Issue where project_id=X для определения нового значения, поскольку оно может устареть.
Отвечает ли это на ваш вопрос? Что-то вроде «различных индексов автоинкремента» для значений первичного ключа
Ответы 2
Вы можете вычислить поле issue_number, хранить его не нужно:
SELECT
id,
project_id,
row_number() over (partition by project_id order by id) as issue_number,
content
FROM Issue;
или вы можете сохранить его, когда вам часто понадобится фактическое значение issue_number....
Вы можете создать таблицу «последовательности» для хранения следующего номера выпуска для каждого проекта.
create table sequencing (
project_id int primary key not null,
next_number int
);
insert into sequencing (project_id, next_number) values (1234, 1);
Вы можете использовать оптимистическую блокировку или пессимистическую блокировку для устранения состояния гонки. В приведенной ниже последовательности используется пессимистическая блокировка. Решение «Оптимистическая блокировка» немного отличается (здесь не показано).
При добавлении нового выпуска вы сделаете:
- Запустите транзакцию базы данных.
- Получите следующий номер выпуска для проекта и заблокируйте строку.
- Увеличьте номер следующего выпуска проекта.
- Вставьте проблему с вновь полученным номером проблемы.
- Зафиксируйте транзакцию и снимите блокировку.
В SQL это будет выглядеть так:
start transaction;
select next_number -- save this number in app as "n"
from sequencing
where project_id = 1234
for update;
update sequencing
set next_number = next_number + 1
where project_id = 1234;
insert into issue (..., issue_number) values (..., n); -- use "n" here
commit;
Обязательно получите следующий номер внутри транзакции.
Что именно вы подразумеваете под «может быть устаревшим»?