How to reset AUTO_INCREMENT in MySQL to lower than the maximum current ID

First published on January 1, 2008

In short, you cannot and should not. In MySQL, if you have a column that is assigned the AUTO_INCREMENT count, you can reset that count by using this statement (or check out the Operations tab in phpMyAdmin):

ALTER TABLE tablename AUTO_INCREMENT=0

However, if you have existing rows, the AUTO_INCREMENT value will only reset to one higher than the maximum current ID. For example, if you have a table with the following entries…

ID	First name
3	Peter
6	Paul
7	Mary

… and the AUTO_INCREMENT count is set at 60, the statement to reset the AUTO_INCREMENT count to 0 will only reset it to 8. Only if there are no rows remaining will it reset to 0.

There is good reason for this limitation. If you were to successfully reset the AUTO_INCREMENT count to 0 in the case above, eventually you would hit existing rows at IDs 3, 6 and 7, triggering errors or overwriting important information.

However, if you want to keep the existing rows and still reset the AUTO_INCREMENT count to 0, MySQL will not let you.

There is a way around this if you absolutely must reset the AUTO_INCREMENT count to lower than the maximum current ID. What you need is two tables: one that simply keeps track of the AUTO_INCREMENT count and the other to store your actual data.

Table 1 fields: Unique key “id” auto_increment (just one field)
Table 2 fields: Unique key “id” NOT auto_increment; other fields like first name, last name, etc.

For example, you might have temporary data that is deleted after, say, 24 hours. Therefore, if you inserted 10,000 rows per day, your AUTO_INCREMENT count would be up to 3,650,000 after a year. However, the current IDs might only be between 3,640,000 and 3,650,000, leaving no rows with IDs less than 3,640,000. If you tried to reset the AUTO_INCREMENT count to 0, it wouldn’t work. And you cannot just delete all rows because you need those 10,000 rows that were created in the last 24 hours.

Assuming that you are working in PHP, whenever you need to insert a row to Table 2, execute the following steps:

1) Insert a row into Table 1

mysql_query('INSERT INTO table1 (id) VALUES (NULL)');

2) Get the row via the PHP function mysql_insert_id();

$row_to_insert = mysql_insert_id();

or

$result = mysql_query('SELECT last_insert_id()');
while ($theresult = mysql_fetch_array($result)) {
$row_to_insert = $theresult[0];
}

3) Delete the row you just inserted into Table 1 (this is optional as you can clear Table 1 periodically)

mysql_query('DELETE FROM table1 WHERE id = ' . $row_to_insert);

4) Insert the row with all the important data into Table 2 using the ID that was just generated in Table 1 (since Table 2 doesn’t use AUTO_INCREMENT):

mysql_query('INSERT INTO table2 (id, createtime, firstname, lastname) VALUES (' . $row_to_insert . ', ' . time() . ', ' . $first_name . ', ' . $last_name . ')');

5) Delete rows from Table 2 that are older than 24 hours

mysql_query('DELETE FROM table2 WHERE ' . time() . ' > createtime + 86400');

Whenever you want to reset the count, just clear Table 1 and use the ALTER TABLE statement to successfully reset the AUTO_INCREMENT count back to 0 (since there is really no data in Table 1).

mysql_query('DELETE FROM table1');
mysql_query('ALTER TABLE table1 AUTO_INCREMENT=0');

In our example, new rows into Table 1 get IDs starting at 0 and new rows into Table 2 get the IDs from Table 1. Therefore they also start at 0, even though there are existing IDs in Table 2 between 3,640,000 and 3,650,000.

Facebook Twitter Email this

This entry is filed under Web development tutorials and written by Peter Keung. You can follow any responses to this entry through the RSS 2.0 feed.