Как сделать ID записи последовательными без пропусков?

Question

[Vlad_isLove]

Есть SQL таблица c объявлениями, в которой лежит 100 объявлений от разных юзеров 1 объявление = 1 строка. Строки имеют индексы id по порядку с 1 до 100. Проблема в том, что пользователи могут удалять свои объявления и следовательно появляются "пустоты" в индексах, например если пользователь удалит своё объявление с индексом 2, то при добавлении нового объявления оно добавится не под индексом 2, а под индексом 101, а я хочу чтобы заполнялись эти "пустоты" и новое объявление добавлялось под индексом 2 или еще лучше при удалении перенумеровывались все строки и получали новые индексы от 1 до 99. Можно ли заставить MySQL делать это? Может нужно указать что-то при создании колонки с индексами?

P.S. Хочу делать это во-первых потому, что так можно при запросе указывать не LIMIT 1000, 10(выбрать 10 записей начиная с 1000-й), что заставляет перебирать все 1000 записей, а WHERE id > 1000 LIMIT 10, что не заставляет перебирать все 1000 записей и это гораздо эффективнее. А из-за "пустот" во втором случае он может обратится к несуществующим записям.
Во-вторых со временем индекс будет становится все больше и мне кажется это может быть плохо.

Comments

[Дмитрий]

Зачем вам это? Индекс на то и индекс, если его постоянно переделывать, все связи будут нарушены, например индекс этого вопроса 641258, я дал ссылку на него в соседнем вопросе, вы потом удалили, ссылка уже ведёт не туда

Answer 1

[ThunderCat]

Так делать не надо. Если нужно поле которое будет у вас в диапазоне 1-100 - добавьте его отдельно, первичный индексный автоинкрементный ключ не для этого.

что заставляет перебирать все 1000 записей, а WHERE id > 1000 LIMIT 10, что не заставляет перебирать все 1000 записей

У вас нет никакого понятия как работают индексы, по этому вы думаете что так будет быстрее. Хотя логика подсказывает что за 20+ лет существования реляционных бд наверняка при необходимости повысить производительность до такой опции бы давно додумались и она была бы распространена, но почему то такого не случилось... Это по тому что достаточно каждый день выпивать по чайной ложке прочитать как работают индексы, и все встанет на свои места.

Answer 2

[Иван Шумов]

Во-первых нельзя, а во-вторых не нужно

Answer 3

[vaflya]

не нужно из-за индексации сайта поисковиками, и при переходе из гугла к примеру. Человек получит другое совсем объявление. а так же может нарушиться структура в БД. Я тоже боялся таких пустот, но это была глупость по неопытности. И как вариант не удалять, а помечать is_remove : True / False.

Comments

[vaflya]

а вывод добавь в Where: is_remove = False

[zhaar]

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

Answer 4

[Дмитрий Шицков]

Штатно, используя автоинкремент - нельзя. И перенумеровывать строки не рационально. А если строк миллиард? Каждую обновлять ради циферки? Зачем вам нужно заполнять эти пустоты? Пронумеровать строки можно и налету, в базе хранить нумерацию смысла нет.

Если очень хочется, то придется описать свой алгоритм поиска в базе дыр и заполнения их.

Answer 5

[kafkiansky]

Если порядок нужен для вывода, то просто пересчитывайте кол-во объявлений и каждому присваивайте номер текущей итерации, если чисто для себя, то не удаляйте объявления, а помечайте как удаленное (DELETED) в базе.

Answer 6

[AUser0]

Перенумеровать индекс:

SET @pos := 0;
UPDATE `table_name` SET `id`=(SELECT @pos:=@pos+1) ORDER BY `id` ASC;

Comments

[zhaar]

И все связанные таблицы после такого апдейта разлетятся, как карточный домик

[AUser0]

zhaar, если они есть, связанные-то...

[Дмитрий]

Плюс напряг на носители, постоянно таким если заниматься

[zhaar]

AUser0, связь не обязательно обратная. Т.е. целостность данных в рамках нескольких таблиц может поддерживаться без зависимой связи один-один или один-ко многим.
Простой пример - есть две таблицы, одна с шапками документов, вторая - с товарным содержимым. Если это документ заказа, то у него будет шапка и детали, если это просто принятие денег, то у него будет только шапка без деталей.