Как сделать так, чтобы не было пропусков в сгенерированных значениях автоинкрементного первичного ключа?

Question

[Иван Мельников]

В MySQL 5.7 имеется InnoDB-таблица с автоинкрементным суррогатным первичным ключом: id INT UNSIGNED NOT NULL AUTO_INCREMENT. В эту таблицу некоторое приложение пишет данные через хранимую функцию в однопоточном режиме, производя записи в таблицу одна за одной. Как сделать, чтобы не было пропусков в сгенерированных значениях id (за работу внешнего приложения я не отвечаю, а оно может попытаться записать в таблицу данные, не проходящие валидацию)?
Хранимая функция, через которую приложение пишет в таблицу БД выглядит следующим образом:

CREATE FUNCTION insert_material (n VARCHAR(255), d DATE) RETURNS INT UNSIGNED
BEGIN
INSERT INTO Material(name, date) VALUE(n, d);
RETURN LAST_INSERT_ID();
END

Как наиболее просто модифицировать эту функцию, чтобы гарантировать отсутствие пропусков в генерируемых значениях id?

Comments

[Сергей delphinpro]

чтобы гарантировать отсутствие пропусков

Стандартный встречный вопрос: Нафига?!

[Сергей delphinpro]

Поясню. Первичный ключ обычно используется для служебных целей, для связи таблиц, для уникальной идентификации записей. Какая разница, есть там пропуски, или нет, если возложенную на него задачу он выполняет?
Дайте угадаю — вы возложили на него какие дополнительные функции, для которых первичный ключ использоваться не должен?

[Иван Мельников]

Сергей delphinpro, просто я так хочу.

[Иван Мельников]

Сергей delphinpro,

Какая разница, есть там пропуски, или не

Например, для того, чтобы не пришлось расширять тип id с INT UNSIGNED до BIGINT UNSIGNED.

[Сергей delphinpro]

Иван Мельников,

Я знаю, что это бессмысленно, я знаю, что это не нужно и т.д.

Отлично что понимаете. Задайтесь вопросом — будет ли разработчик добавлять в свой продукт бессмысленную возможность?

чтобы не пришлось расширять тип id с INT UNSIGNED до BIGINT UNSIGNED.

Звучит бредово в контексте вопроса ))

Answer 1

[Руслан Федосеев]

Перестать заниматься ерундой ;)
Перестать обращать внимание на дырки в автоинкременте и заняться логикой приложения.

Comments

[Иван Мельников]

Я знаю, что это ерунда. Но мне хочется услышать что-нибудь конкретно по вопросу.

Answer 2

[Олег]

автоинкремент может иметь шаг более 1. нр репликация мастер-мастер. Инсерт он дупликате апдейт тоже его увеличивает даже в случае апдейта. борьба с дырками бесмысленна

Comments

[Иван Мельников]

Неужели нет способа? А что мешает в insert явно задавать значение id, равное максимальному значению id в таблице + 1?

[Rsa97]

Иван Мельников, Ничто не мешает, но только если сможете гарантировать, что не будет одновременно двух запросов на запись. Ну и LAST_INSERT_ID раьотать перестанет.

[Иван Мельников]

Rsa97, а как можно иначе избавиться от пропусков в id, оставив его генерацию СУБД?

[Олег]

какую задачу вы пытаетесь решить ?
контроль целостности данных ?
или просто для красоты ?

[Иван Мельников]

Я просто исследую MySQL. И мне хочется избавиться от пропусков в id. Я знаю, что это бессмысленно, я знаю, что это не нужно и т.д. Но я хочу избавиться от пропусков.

[Rsa97]

Иван Мельников, Никак. Это механизм резервирования новых ID при обработке одновременных запросов.
Когда приходит INSERT СУБД резервирует необходимое количество следующих ID для максимального количества вставляемых строк и соответственно увеличивает NEXT_ID. Если в это время приходит второй INSERT, то для него выполняется та же процедура. Если первый запрос не смог добавить строки, то обратно NEXT_ID уже откатить нельзя, он был изменён вторым запросом. Так и возникают лакуны.

[Иван Мельников]

Rsa97, Как-то очень сомнительно, что в MySQL никак нельзя это реализовать...

[Rsa97]

Иван Мельников, При конкурентных запросах - никак. При строго последовательных - через MAX(`id`)+1 и вставке по одной строке.

[Иван Мельников]

Rsa97, а как можно перевести БД в режим последовательных запросов?

[Олег]

Иван Мельников не занимайтесь извращением. Вопрос из разряда как мне правильно выстрелить в ногу.
цель ид только одна - ОН ДОЛЖЕН БЫТЬ УНИКАЛЬНЫМ.
и требуется только для простого доступа к КОНКРЕТНОЙ ЗАПИСИ.
не мешайте системе делать ее работу.

Вот начнете Вы лочить на уровне таблицы - ПРОБЛЕМ огребете в других местах.

[Rsa97]

Иван Мельников, Только блокировками таблиц перед запросами.

[Иван Мельников]

Олег Муравейко, я это всё прекрасно понимаю. Но в данный момент мне нужно избавиться от пропусков в id.

[Олег]

Только блокировками таблиц перед запросами