mysql найти наименьший + уникальный идентификатор

19

У меня есть идентификатор столбца и что-то вроде 1000 элементов, некоторые из которых были удалены как id=90, id=127, id=326

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

его как min(ID) , но я хочу найти только идентификаторы, которые НЕ находятся в моей базе данных, поэтому, если я удалю элемент с ID = 90 , в следующий раз, когда я нажму ADD ITEM, я вставлю его как id = 90     

задан braindamage 16.02.2011 в 14:19
источник

7 ответов

33

Вы можете получить минимально доступный идентификатор, используя этот запрос:

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 null или нет. Если он равен нулю, тогда этот идентификатор доступен. Предположим, что у вас есть таблица, где ID :
1
2
5
6

Затем этот запрос даст вам результат в виде 3 , который вы хотите.

    
ответ дан shamittomar 16.02.2011 в 14:23
источник
  • Почему бы не выбрать MIN (idColumn) -1? –  ThiefMaster♦ 16.02.2011 в 14:37
  • @ThiefMaster, потому что мы должны получить СЛЕДУЮЩИЙ доступный идентификатор. MIN будет давать самый низкий доступный идентификатор, а +1 - следующий. –  shamittomar 16.02.2011 в 21:32
  • мертвый на помощнике. спасибо, @shamittomar –  azBrian 27.03.2014 в 08:41
  • Не работает, если ваш идентификатор начинается (например) 58, и поэтому вы хотите знать, что идентификаторы 1 - 58 бесплатны. Поэтому он должен найти 57 как самый низкий доступный номер. –  geoidesic 17.08.2016 в 01:23
6

Не используйте повторно ID. Обычно у вас достаточно доступных идентификаторов, поэтому вам не нужно заботиться о фрагментации.

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

    
ответ дан ThiefMaster 16.02.2011 в 14:23
источник
  • спасибо, но это не так, и мне нужно знать, как получить этот min + unique + unused id –  braindamage 16.02.2011 в 14:26
3

Это противоречит концепции суррогатных ключей, чтобы попытаться повторно использовать идентификаторы

Суррогатный ключ хорош, потому что он сам определяет саму запись, а не какой-то объект в реальной жизни. Если запись исчезла, идентификатор тоже исчез.

Опытные разработчики БД не боятся исчерпать числа, потому что знают, сколько столетий нужно истощать, скажем, длинными целыми числами.

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

Дополнительная литература: Суррогатные ключи

    
ответ дан fedd 16.02.2011 в 14:48
источник
  • каждая запись имеет свой собственный идентификатор, автоинкремент и все, что уникально. Имущественное создание - это только потому, что мой клиент хочет быть уникальным, но также не превышает общее количество продуктов, которые он добавил (другое поле) .. вы, ребята, делаете много предположений, но спасибо за ваш вклад, возможно, другие люди, которые соответствуют тому, что вы сказали, могут извлечь выгоду из этого ... –  braindamage 16.02.2011 в 16:53
  • , тогда я бы посоветовал не удалять записи, а отмечать их удаленные в некотором поле. когда вам нужно будет заполнить запись новыми данными, вы найдете минимальный «id» записи, где deleted = true и обновите его. с правильными настройками изоляции транзакций сервер db позаботится о том, чтобы предотвратить «грязные чтения», чтобы другой поток не захотел обновлять одну и ту же запись, думая, что она все еще «удалена», –  fedd 17.02.2011 в 08:14
  • Это хороший дополнительный ответ и добавляет вес и полезную информацию в общую SO. Первое, что я подумал, когда читаю вопрос, почему вы хотите это сделать? Вопрос не определил рассуждения - и даже теперь, когда было разъяснено выше, это все еще не имеет смысла. это уникальное уникальное создание только потому, что мой клиент хочет быть уникальным, но также не превышает общее количество продуктов, которые он добавил (другое поле) - если вы используете идентификаторы для указания общего количества продуктов, что-то не так –  ncatnow 04.04.2014 в 12:39
2

запрос как :

SELECT MIN(tableFoo.uniqueid + 1) AS nextID
FROM tableFoo
LEFT JOIN tableFoo tf1
       ON tableFoo.uniqueid + 1 = tf1.uniqueid
WHERE tf1.uniqueid IS NULL
    
ответ дан Haim Evgi 16.02.2011 в 14:23
источник
0

Обратите внимание, что ответы shamittomar и Haim Evgi не работают, если самый низкий ID является бесплатным. Чтобы разрешить перезарядку самого низкого идентификатора, предварительно проверьте, доступна ли она:

SELECT TRUE FROM tablename WHERE ID = 1;

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

    
ответ дан Jeffrey Friedl 11.10.2015 в 11:56
источник
0

По моему личному мнению. Вместо того, чтобы удалять строку из автоматического приращения, было бы дешевле летать дешевле, чтобы иметь Boolean Column для «Removed» или «Deleted», а для дополнительной безопасности над правой строкой с пробелами, когда вы устанавливаете удаленный флаг.

UPDATE table SET data=" ", removed = TRUE WHERE id = ##

(## - фактический id btw) Тогда вы можете

SELECT * FROM table WHERE removed = TRUE ORDER BY id ASC

Это сделает вашу базу данных более эффективной и сохранит тесто на серверах. Не говоря уже об отсутствии неприятных ошибок.     

ответ дан Mike Pengelly 19.11.2015 в 19:15
источник
0

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

Однако, если у вас есть таблица несколько миллионов записей / более длинный идентификатор, вы обнаружите, что принятый ответ не закончится в разумное время.

Принятый ответ будет давать вам наименьшее из этих значений, правильно, однако, вы платите цену за не использование столбца автоматического увеличения, или если у вас есть один, не используя авто increment column как фактический идентификатор, как он предназначен (Как и я, иначе меня бы здесь не было). Я нахожусь во власти устаревшего приложения, так как идентификатор не является фактическим первичным ключом, и он случайно генерируется с помощью алгоритма без уважительной причины, поэтому мне нужно было заменить его, так как увеличение диапазона столбцов теперь чрезвычайно дорогостоящие изменения.

Здесь он выясняет всю соединяйтесь между целым числом t1 и t2, прежде чем сообщать, что такое мин этих объединений. По сути, вы заботитесь только о первом NULL t1, который найден, независимо от того, является ли он на самом деле наименьшим или нет.

Таким образом, вы выберете MIN и добавите LIMIT из 1.

edit: Поскольку это не первичный ключ, вам также нужно будет проверить не null, поскольку поле первичного ключа не может быть 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

Это всегда даст вам идентификатор, который вы можете использовать, его просто не гарантировано всегда будет самым маленьким.

    
ответ дан Paul Stanley 28.04.2016 в 13:20
источник