Recompute auto_increment values on data export

from the Artful MySQL Tips List

In some table export jobs, you need the database to regenerate auto_increment values, but mysqldump has no such option. A workaround is to define a trigger which presets the auto_increment value to NULL on the table which is to load the dumped data, for example ...

drop table if exists t;
create table t(id int primary key auto_increment, j int);
DROP TRIGGER IF EXISTS tinsert;
CREATE TRIGGER tinsert BEFORE INSERT ON t FOR EACH ROW SET NEW.id = NULL;

Last updated 1 Dec 2024

Return to the Artful MySQL Tips page