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
SHOW TABLE STATUS LIKE 'table_name'
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'];
