How to properly adjust the auto_increment property of a table in MySQL

Wed, 2012.01.18 - 14:14 — müzso

If you've an autoincrement column in a table and you want to set the next value to the max value + 1 from the actual values in the table, then use something like this ...

SET @tab := "MY_TABLE";
SET @col := "MY_ID_COLUMN";
SET @q1 := CONCAT("SELECT @newid := MAX(`", @col, "`) + 1 FROM `", @tab, "`");
PREPARE stmt FROM @q1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @q2 := CONCAT("ALTER TABLE `", @tab, "` AUTO_INCREMENT = ", @newid);
PREPARE stmt FROM @q2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Executing this in a single batch will almost guarantee atomicity.