Calculate next auto-increment value for MySQL tables

Sometimes it is necessary to determine the next auto-increment value that will be used by MySQL for inserting values into tables.

First off, there are several different approaches to determine it. Most people use the MAX() function and simply add 1 to the value. Although this is fine is most cases, you may run into problems when forced to retain data consistency after deleting rows.

Another approach is to determine the AUTO_INCREMENT value directly:

SHOW TABLE STATUS LIKE 'your_table'

In order to determine the next auto-increment value used by MySQL you simply have to get the value of the field called ‘Auto_increment’.

Using PHP, this can be achieved like so:

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

You may also like...

1 Response

  1. Alex Plaksin says:

    tnx. very helpfull

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.