У меня есть идентификатор столбца и что-то вроде 1000 элементов, некоторые из которых были удалены как id=90, id=127, id=326
как я могу сделать запрос для поиска доступных идентификаторов, так что я могу повторно использовать его для другого элемента?
его как min(ID) но я хочу найти только идентификаторы, которые НЕ находятся в моей базе данных, поэтому, если я удалю элемент с ID = 90 , в следующий раз, когда я нажму ADD ITEM, я вставлю его как id = 90
Вы можете получить минимальный доступный идентификатор, используя этот запрос:
SELECT MIN(t1.ID + 1) AS nextID FROM tablename t1 LEFT JOIN tablename t2 ON t1.ID + 1 = t2.ID WHERE t2.ID IS NULL
Что он делает, так это то, что он соединяет таблицу с самим собой и проверяет, равен ли min+1 идентификатор или нет. Если он равен нулю, тогда этот идентификатор доступен. Предположим, что у вас есть таблица, где ID :
1
2
5
6
Затем этот запрос даст вам результат 3 который вы хотите.
Не используйте повторно ID. Обычно у вас достаточно доступных идентификаторов, поэтому вам не нужно заботиться о фрагментации.
Например, если вы повторно используете идентификаторы, ссылки из поисковых систем могут указывать на нечто совершенно не связанное с тем, что находится в индексе поиска – показ ошибки «не найден» в этом случае намного лучше.
Это противоречит концепции суррогатных ключей, чтобы попытаться повторно использовать идентификаторы
Суррогатный ключ хорош, потому что он сам определяет саму запись, а не какой-то объект в реальной жизни. Если запись исчезла, идентификатор тоже исчез.
Опытные разработчики БД не боятся исчерпать числа, потому что знают, сколько столетий нужно истощать, скажем, длинными целыми числами.
BTW, вы можете столкнуться с блокировкой или нарушением проблем уникальности в многопоточной среде с одновременными транзакциями, пытаясь найти пробел в последовательности идентификаторов. Генераторы Auto increment id, предоставляемые серверами БД, обычно работают вне области транзакций и, таким образом, генерируют хорошие суррогатные ключи.
Дальнейшее чтение: Суррогатные ключи
запрос выглядит так :
SELECT MIN(tableFoo.uniqueid + 1) AS nextID FROM tableFoo LEFT JOIN tableFoo tf1 ON tableFoo.uniqueid + 1 = tf1.uniqueid WHERE tf1.uniqueid IS NULL
Обратите внимание, что ответы shamittomar и Haim Evgi не работают, если самый низкий идентификатор является бесплатным. Чтобы разрешить перезарядку самого низкого идентификатора, предварительно проверьте, доступна ли она:
SELECT TRUE FROM tablename WHERE ID = 1;
Если это возвращает что-либо, то идентификатор 1 не является бесплатным, и вы должны использовать их ответ. Но если идентификатор 1 свободен, просто используйте его.
По моему личному мнению. Вместо того, чтобы удалять строку из автоматического приращения, было бы дешевле летать дешевле, чтобы иметь Boolean Column для «Removed» или «Deleted», а для дополнительной безопасности над правой строкой с пробелами, когда вы устанавливаете удаленный флаг.
UPDATE table SET data=" ", removed = TRUE WHERE id = ##
(## – фактический id btw). Затем вы можете
SELECT * FROM table WHERE removed = TRUE ORDER BY id ASC
Это заставит вашу базу данных работать лучше и сохранить тесто на серверах. Не говоря уже об отсутствии неприятных ошибок.
Учитывая, что ваша база данных достаточно мала, правильный ответ заключается в том, чтобы не повторно использовать ваши идентификаторы вообще и просто обеспечить его автоматически увеличивающийся первичный ключ. Таблица составляет тысячу записей, поэтому вы можете сделать это без каких-либо затрат.
Однако, если у вас есть таблица из нескольких миллионов записей / более длинный идентификатор, вы обнаружите, что принятый ответ не закончится в разумное время.
Принятый ответ даст вам наименьшее из этих значений, правильно, однако, вы платите цену за то, что не используете столбец автоматического увеличения, или если он у вас есть, а не с использованием столбца автоматического увеличения в качестве фактического идентификатора, как он предназначен (Как и я, иначе меня бы здесь не было). Я нахожусь во власти устаревшего приложения, так как идентификатор не является фактическим первичным ключом, и он случайно генерируется с помощью алгоритма без уважительной причины, поэтому мне нужно было заменить его, так как увеличение диапазона столбцов теперь чрезвычайно дорогостоящие изменения.
Здесь он вычисляет полное соединение между целым числом t1 и t2, прежде чем сообщать, что такое мин этих объединений. По сути, вы заботитесь только о первом найденном NULL t1, независимо от того, является ли он на самом деле наименьшим или нет.
Таким образом, вы должны взять MIN и добавить LIMIT из 1 вместо.
edit: Поскольку это не первичный ключ, вам также нужно будет проверить не null, поскольку поле первичного ключа не может быть нулевым
SELECT t1.ID + 1 AS nextID FROM tablename t1 LEFT JOIN tablename t2 ON t1.ID + 1 = t2.ID WHERE t2.ID IS NULL AND t1.ID IS NOT NULL LIMIT 1
Это всегда даст вам идентификатор, который вы можете использовать, его просто не гарантировано всегда будет самым маленьким.