Bug #63876 Reset Auto_Increment
Submitted: 29 Dec 2011 17:12 Modified: 30 Dec 2011 9:58
Reporter: David Colter Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[29 Dec 2011 17:12] David Colter
Description:
It would be nice if the Auto Increment value could be easily reset.

In the case where certain data is occasionally purged, and the auto increment value could be repeated (from 1 for example) without reaching the previously used values, this would be a nice feature to have.

I can see the need for program code to continue beyond the highest to avoid duplication and extra overhead.

The choice to reset the value would be a nice option.

If this is not possible, please explain why.

Thank you.

How to repeat:
Alter Table tbl Auto_Increment=1

has no effect if values for that column are present.
[30 Dec 2011 9:58] Valeriy Kravchuk
I agree that this feature can be useful in some cases.

For reference, current behavior is described in the manual, http://dev.mysql.com/doc/refman/5.1/en/alter-table.html:

"You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed."