Hittade följande länk (otestat):
http://dev.mysql.com/doc/refman/5.0/...increment.html
Kod:
-- Posted by Thomas Mayer on January 20 2012 3:17pm
--
-- As InnoDb forgets its highest auto_increment after server restart, you can set it again,
-- if you have stored it anywhere. This happens often if you archive your data in an archive
-- table and then delete it and then restart mysql. When archiving again this will result in
-- duplicate key entries.
--
-- To work around this you can create a trigger which makes sure your auto_increment is
-- higher than the auto_increment of your archive table:
delimiter //
drop trigger if exists trigger_autoinc_tbl;
CREATE TRIGGER trigger_autoinc_tbl BEFORE INSERT ON tbl
FOR EACH ROW
BEGIN
declare auto_incr1 BIGINT;
declare auto_incr2 BIGINT;
SELECT AUTO_INCREMENT INTO auto_incr1 FROM information_schema.TABLES WHERE table_schema=DATABASE() AND table_name='tbl';
SELECT AUTO_INCREMENT INTO auto_incr2 FROM information_schema.TABLES WHERE table_schema=DATABASE() AND table_name='tbl_archiv';
IF (auto_incr2 > auto_incr1 and NEW.id<auto_incr2) THEN
SET NEW.id = auto_incr2;
END IF;
END;//
delimiter ;
-- Further reading: http://www.slicewise.net/index.php?id=82