sql - 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

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';