Get the next ID not used?

Currently I have a database that i开发者_运维问答s not managed by me and I can't do any changes to it, the id field is a smallint 2 unsigned that gives you up to 65535 id entries.

My problem is that I need to reuse the ids because of the above limitations, how could I get the next usable ID in order or what would you do to manage the inserts with the above limitations ?

Check if 1 is free. If not:

SELECT MIN(a.id) + 1 AS smallestAvailableId
FROM your_table AS a
LEFT JOIN your_table AS a2
        ON a2.id = a.id + 1
WHERE a2.id IS NULL

From the tags I deduce that you need the id in Java.

I personally would avoid joining the table with itself. Since you have at most 64K rows, I would select id from table into Java and search for id in Java. One way to search for gaps is by sorting the array first (either in SQL or in Java); finding gaps then becomes trivial.

If you do this repeatedly, you can cache the array and avoid having to run an SQL statement every time you need an id.

Regardless of what you do, if there are multiple clients writing to the database you have to be prepared to deal with race conditions, where multiple clients would attempt to use the same id. You code would need to either use locking or be able to recover gracefully to re-trying the failed insert with a different id (I assume there is a uniqueness constraint on the id column.)

Whichever approach you take is likely to cause you problems because of race conditions unless you know you will have exactly one client accessing the db at any single moment.

To answer your question, what do you consider an "usable" id? Please shed some light on that. Until all id's have been used a simple

SELECT MAX(id) + 1 FROM table;

should do. If you establish a criterion for "usable" ids such as for example, reuse all ids that have been flagged old then you can do:

SELECT MIN(id) FROM table WHERE is_old = 1; 

Then just unflag the selected id.