Visa ett inlägg
Oläst 2013-06-04, 02:12 #10
Conny Westh Conny Westh är inte uppkopplad
Klarade millennium-buggen
 
Reg.datum: Aug 2005
Inlägg: 5 166
Conny Westh Conny Westh är inte uppkopplad
Klarade millennium-buggen
 
Reg.datum: Aug 2005
Inlägg: 5 166
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
Conny Westh är inte uppkopplad   Svara med citatSvara med citat