Finding the next available id in MySQL

Question

Update 2014-12-05

I am not recommending this approach due to reasons laid out in Simon's (accepted) answer as well as Diego's comment. Please use query below at your own risk.

---

Original answer

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, like so:

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

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

In addition to Lukasz Lysik's answer - LEFT-JOIN kind of SQL.
As I understand, if have id's: 1,2,4,5 it should return 3.

SELECT u.Id + 1 AS FirstAvailableId
FROM users u
LEFT JOIN users u1 ON u1.Id = u.Id + 1
WHERE u1.Id IS NULL
ORDER BY u.Id
LIMIT 0, 1

Hope it will help some of visitors, although post are rather old.

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.