Finding the next available id in MySQL

Question

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.

Answer 1

One way to do it is to set the index to be auto incrementing. Then your SQL statement simply specifies NULL and then SQL parser does the rest for you.

INSERT INTO foo VALUES (null);

Answer 2

Given what you said in a comment:

my id coloumn is auto increment i have to get the id and convert it to another base.So i need to get the next id before insert cause converted code will be inserted too.

There is a way to do what you're asking, which is to ask the table what the next inserted row's id will be before you actually insert:

SHOW TABLE STATUS WHERE name = "myTable"

there will be a field in that result set called "Auto_increment" which tells you the next auto increment value.

Answer 3

I don't think you can ever be sure on the next id, because someone might insert a new row just after you asked for the next id. You would at least need a transaction, and if I'm not mistaken you can only get the actual id used after inserting it, at least that is the common way of handling it -- see http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

Answer 4

If you want to select the first gap, use this:

SELECT  @r
FROM    (
        SELECT  @r := MIN(id) - 1
        FROM    t_source2
        ) vars,
        t_source2
WHERE   (@r := @r + 1) <> id
ORDER BY
        id
LIMIT   1;

There is an ANSI syntax version of the same query:

SELECT  id
FROM    mytable mo
WHERE   (
        SELECT  id + 1
        FROM    mytable mi
        WHERE   mi.id < mo.id
        ORDER BY
                mi.id DESC
        LIMIT 1
        ) <> id
ORDER BY
        id,
LIMIT   1

however, it will be slow, due to optimizer bug in MySQL.

Answer 5

As I understand, if have id's: 1,2,4,5 it should return 3.

SELECT t1.id + 1
FROM theTable t1
WHERE NOT EXISTS (
    SELECT * 
    FROM theTable t2
    WHERE t2.id = t1.id + 1
)
LIMIT 1

Answer 6

The shortest one i found on mysql developer site:

SELECT Auto_increment FROM information_schema.tables WHERE table_name='the_table_you_want';

mind you if you have few databases with same tables, you should specify database name as well.

Answer 7

If this is used in conjunction for INSERTING a new record you could use something like this.

(You've stated in your comments that the id is auto incrementing and the other table needs the next ID + 1)

INSERT INTO TABLE2 (id, field1, field2, field3, etc) 
VALUES(
   SELECT (MAX(id) + 1), field1, field2, field3, etc FROM TABLE1
   WHERE condition_here_if_needed
)

This is pseudocode but you get the idea

Answer 8

you said:

my id coloumn is auto increment i have to get the id and convert it to another base.So i need to get the next id before insert cause converted code will be inserted too.

what you're asking for is very dangerous and will lead to a race condition. if your code is run twice at the same time by different users, they will both get 6 and their updates or inserts will step all over each other.

i suggest that you instead INSERT in to the table, get the auto_increment value using LAST_INSERT_ID(), and then UPDATE the row to set whatever value you have that depends on the auto_increment value.

Answer 9

If you really want to compute the key of the next insert before inserting the row (which is in my opinion not a very good idea), then I would suggest that you use the maximum currently used id plus one:

SELECT MAX(id) + 1 FROM table

But I would suggest that you let MySQL create the id itself (by using a auto-increment column) and using LAST_INSERT_ID() to get it from the DBMS. To do this, use a transaction in which you execute the insert and then query for the id like:

INSERT INTO table (col1) VALUES ("Text");
SELECT LAST_INSERT_ID();

The returnset now contains only one column which holds the id of the newly generated row.