Automatically set mysql autoincrement to min value

Question

i just wondered, if there is an elegant way of automatically resetting the autoincrement of a mysql table to the lowest value according to the present content.

example:

mytable:

1  content of row 1
2  content of row 2
3  content of row 3
4  content of row 4
5  content of row 5

now the autoincrement will be at 6

but before i insert new contents, i delete row 3,4 and 5. the content would look like this:

1  content of row 1
2  content of row 2

the autoincrement would still be at 6.

and this is the issue.

i would like the autoincrement to be at 3, because it is the lowest possible value according to the inserted IDs.

the would prevent extremely large numbers, if the autoincrement would grow "infinitely" and get out of range of a 12 digits long integer.

thanks for any suggestion!

Answer 1

This depends on your storage engine,

For MyISAM and for InnoDB on MySQL 5.6+, you can set the AUTO_INCREMENT value for the table to say 1. That value will automatically be increased to the maximum current value + 1. Do that like this.

ALTER TABLE table_name AUTO_INCREMENT = 1;

For InnoDB on MySQL < 5.6, this will not work and you will need to do this manually like this:

SELECT MAX(autoincrement_field) + 1 FROM table_name INTO @maxautoinc;
ALTER TABLE table_name AUTO_INCREMENT = @maxautoinc;

Note in that last case, the two queries will need to be run with the same database connection.