MySQL: Get next auto increment value

by leo

MySQL
Recently, I needed to get the auto increment value for the next inserted row in a MySQL table.  Generally, this is not a good idea and you should stop reading right now.
You may end up with duplicate entries in your application, which is not a good thing.
At a first glance you may think: “I can get the top id value from my table, add 1 and that’s all, I got the next auto_increment value I need.”
Something like this:

  1. $query = mysql_query(SELECT MAX(id) as max_id FROM tablename);
  2. $row = mysql_fetch_array($query);
  3. $next_id = $row[‘max_id’] + 1;

Using the above example, you got the current max auto_increment id plus 1.
As you may already notice, this is wrong. Auto increment values use unique ids.  Imagine the following table:

  1. Id country
  2. 1 Italy
  3. 2 Germany
  4. 3 Greece
  5. 4 Turkey

If you delete the record 4 and run the above script, you will get “4” as next_id.
MySQL will use “5” as next_id.
The solution is to use the “SHOW TABLE STATUS” query.

  1. $query = mysql_query(SHOW TABLE STATUS LIKE tablename);
  2. $row = mysql_fetch_array($query);
  3. $next_id = $row[‘Auto_increment’] ;

The “SHOW TABLE STATUS” command returns a two dimensional array with the “Auto_increment” value among others.