Get next id not used?

Question

Currently, I have a database that is not managed by me, and I can’t add it to it, the id field is smallint 2 unsigned, which gives you up to 65535 identifier entries.

My problem is that I need to reuse identifiers due to the above limitations, how can I get the next useful identifier in order or what will you do to manage inserts with the specified restrictions?

Answer 1

Check if it is free 1. 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

Answer 2

From the tags, I come out that you need an identifier in Java.

. 64K , select id from table Java id Java. - ( SQL, Java); .

, SQL , .

, , , , . , ( , id.)

Answer 3

, , - , , , db .

To answer your question, what do you consider a “usable” identifier? Please shed some light on this. Until all id is used, just

SELECT MAX(id) + 1 FROM table;

must do. If you set the criteria for “usable” identifiers, for example, reusing all identifiers that have been marked with the old, you can do:

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

Then just release the selected identifier.