Finding the next available id in MySQL

user_155332 0
Viewed 111853

I have to find the next available id (if there are 5 data in database, I have to get the next available insert place which is 6) in a MySQL database. How can I do that? I have used MAX(id), but when I delete some rows from the database, it still holds the old max value it didn't update.

mysql sql
15 Answers

The problem with many solutions is they only find the next "GAP", while ignoring if "1" is available, or if there aren't any rows they'll return NULL as the next "GAP".

The following will not only find the next available gap, it'll also take into account if the first available number is 1:

SELECT CASE WHEN MIN(MyID) IS NULL OR MIN(MyID)>1
-- return 1 if it's available or if there are no rows yet
THEN
    1
ELSE -- find next gap
    (SELECT MIN(t.MyID)+1
    FROM MyTable t (updlock)
    WHERE NOT EXISTS (SELECT NULL FROM MyTable n WHERE n.MyID=t.MyID+1))
END AS NextID
FROM MyTable
user_11451770
Related