mysql find smallest + unique id available

Question

i have a column ID and something like 1000 items, some of then were removed like id=90, id=127, id=326

how can i make a query to look for those available ids, so i can reuse then for another item?

its like a min(ID) but i want to find only the ids that are NOT in my database, so if i remove a item with the ID = 90, next time i click on ADD ITEM i would insert it as id = 90

Answer 1

Do not reuse IDs. You usually have way enough available IDs so you don't have to care about fragmentation.

For example, if you re-use IDs, links from search engines might point to something completely unrelated from whatever is in the search index - showing a "not found" error is much better in such a case.

Answer 2

Given that your database is small enough, the correct answer is to not reuse your ids at all and just ensure its an auto incremented primary key. The table is a thousand records, so you can do this without any cost.

However, if you have a table of a few million records/longer id, you will find that the accepted answer wont finish in sensible time.

The accepted answer will give you the smallest of these values, correctly so, however, you are paying the price of not using an auto increment column, or if you have one, not using the auto increment column as the actual ID as it is intended (Like me, else I wouldn't be here). I'm at the mercy of a legacy application were the ID isn't the actual primary key is being used, and is randomly generated with a lolgorithm for no good reason, so I needed a means to replace that since upping the column range is now an extremely costly change.

Here, it is figuring out the entire join between the entirety of t1 and t2 before reporting what the min of those joins is. In essence, you only care about the first NULL t1 that is found, regardless of whether it actually is the smallest or not.

So you'd take the MIN out and add a LIMIT of 1 instead.

edit : Since its not a primary key, you will also need to check for not null, since a primary key field cant be 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

This will always give you an id that you can use, its just not guaranteed to always be the smallest one.

Answer 3

It's against the concept of surrogate keys to try to reuse IDs

The surrogate key is good because it idetifies the record itself, not some object in real life. If the record is gone, the ID is gone too.

Experienced DB developers are not afraid of running out of numbers because they know how many centuries it is needed to deplete, say, long integer numbers.

BTW, you may experience locking or violating uniqueness problems in a multithreaded environment with simultaneous transactions trying to find a gap in the ID sequence. The auto increment id generators provided by DB servers usually work outside the transactions scope and thus generate good surrogate keys.

Further reading: Surrogate keys

Answer 4

In my personal opinion. Instead of removing the row from the auto increment it would be light years less expensive to have Boolean Column for "Removed" or "Deleted" and for extra security over right the row with blanks while you set the removed flag.

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

(## is the actual id btw) Then you can

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

This will make your Database perform better and save you dough on servers. Not to mention ensure no nasty errors occur.

Answer 5

You can get the minimum available ID using this query:

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

What it does is that it joins the table with itself and checks whether the min+1 ID is null or not. If it's null, then that ID is available. Suppose you have the table where ID are:
1
2
5
6

Then, this query will give you result as 3 which is what you want.

Answer 6

the query is like :

SELECT MIN(tableFoo.uniqueid + 1) AS nextID
FROM tableFoo
LEFT JOIN tableFoo tf1
       ON tableFoo.uniqueid + 1 = tf1.uniqueid
WHERE tf1.uniqueid IS NULL