Finding the next available id in MySQL

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.