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?
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.
With MyISAM, simply reset to 1 with ALTER, and it will automatically use the next highest.
For InnoDB, to reset and use the next highest value atomically:
LOCK TABLES table WRITE;
SELECT COALESCE(MAX(id), 1) FROM table;
// Use value from SELECT in the ALTER
ALTER TABLE AUTO_INCREMENT = 99;
UNLOCK TABLES;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments