MySQL: Get next auto increment value
by leo

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:
-
$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:
-
Id country
-
1 Italy
-
2 Germany
-
3 Greece
-
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.
-
$next_id = $row[‘Auto_increment’] ;
The “SHOW TABLE STATUS” command returns a two dimensional array with the “Auto_increment” value among others.
Comments
Why would you choose to use the statment SHOW TABLE STATUS LIKE ?
specificly the LIKE.
As to chicago’s comment, why not fight fire with fire?
SHOW TABLE STATUS query is not transactioned query. So do not safely using it in this case
i’m not mysql genius but it solved my little problem.
thanks
THANKS!!!
Hi,
you could use the direct select like this:
select auto_increment from information_schema.TABLES where TABLE_NAME=’tablename’ and TABLE_SCHEMA=’basename’;
Hi
The comments you have made are really useful. In fact exactly what I was looking for as I have the same problem. I am trying to solve it a different way however.
$count_query = “select count(location_id) as id from location where location_id is not null”;
$count_result = mysql_query($count_query);
while($c = mysql_fetch_assoc($count_result))
{
$count = $c['id'];
}
I use count(field) name to get all the auto_increment values that are not null. I then somehow plan to use the count function to work through retrieved records. I am just having trouble doing this at the moment.
NICE
VERY NICE ARTICLE AND I WANT TO PUT THIS ARTICLE IN MY BLOG.
http://www.mywolfbook.com
hi very nice artical what i am looking i got thanks i will use this artical in mystyle at http://www.oneanall.com/mysql
thanks
ranjeet singh (freelancer)