I know that I can reset the AUTO_INCREMENT value in MySQL using
ALTER TABLE fooo AUTO_INCREMENT = 1;
However, can I avoid changing the value when the table is not empty? Alternatively, can I automatically and atomically set it to the current maximum value + 1?
Background
I have a queue table that is filled by various processes with simple data that is processed by another, single process. The latter deletes items it has processed. It is likely that the table becomes completely empty, although new data will come in quickly.
Currently the auto increment value is over 1,000,000,000 and sooner or later this will lead to nasty integer overflows in the processes. This is why I'm looking for a way to reset the autoinc value for a busy table (within the process that consumes/deletes the rows in the table).
NOTE: As this is a queue, I must absolutely avoid to set the autoinc value to a value lower than currently existing data.