April 23, 2012

mysql auto increment without inserting

Question by chaft

I have a mysql table with an auto increment field. I am using the auto increment counter as an id generator across the database.

I need a way to increment the counter, get the new number but without inserting any data.

Is there an easy to do it?

Answer by Eric Petroelje

If you are using it as an ID generator, then (aside from altering the table) you’ll have to insert something to get the next value.

The easiest way to do this without inserting any data would be to do an INSERT followed by calling LAST_INSERT_ID followed by issuing a ROLLBACK. That will rollback the insert, but the next ID will still be incremented.

Answer by Starx

Use this query, to get the status of the table


There will be a field Auto_increment with the value you want

Using PHP to extract the value

$result = mysql_query("SHOW TABLE STATUS LIKE 'table_name'");
$row = mysql_fetch_assoc($result);
$nextAutoIncrement = $row['Auto_increment'];

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+


Please fill the form - I will response as fast as I can!