MySQL temporarily disable auto increment ID for data migration-CodePudding

I need to insert old data to a new database which I want to keep the old database ID value to the new database ID (the new database ID field is the auto increment ID). So how can I do this for the auto increment ID in MySQL (no way to export / import as I do not have the right for doing this, I can only insert data from the old database and the structure of new database and old database are different), so how can I do this?

## how to disable the auto increment id in new_table?

insert into new_table(id, new_field1) select id, old_field1 from old_table;

## how to re-enable the auto increment id and set the initial value to the max(id)?

CodePudding user response：

Just building upon the comment from P.Salmon, it seems that it should just happen without any problem when you insert values to both the id and other fields at the same time.

However if you for some reason need to change the id given to the next inserted row in the table, you have use an alter table statement. Just like the one in the example on the page P.Salmon linked to https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

ALTER TABLE new_table AUTO_INCREMENT = 100;