Question about mysql auto incrementing id and possibility of collision after migration

I am in the process of writing some backend stuff that will move some tables from one db to another. In the process it will take the id from certain tables and use that in the new table - thats all well and good, but I'm wondering what would happen if there was an imported ID that is later clashed during a normal insert operation?

Ie, if fresh table X, contains IDs 1, 2, 3, 100 after import, then model insert the next time comes in with ID 4, 5 .. etc.

What then happens when it comes to 99 and then 100 ? During this process is mysql smart enough to skip over 100 and go to 101.

Sorry this seems obvious, just not something i've come across before. Just trying to avoid future issues.

thanks

lostdreamer_nl

7 years ago

Level 53

If you have a table with auto incrementing ID, and you have 1 record with ID = 1, and then add a record with ID = 100, the auto increment ID for that table will now be set to 101.

So unless you manually add items with a self generated ID, MySQL will not accidentally re-use an id that is already in use.

"Ie, if fresh table X, contains IDs 1, 2, 3, 100 after import, then model insert the next time comes in with ID 4, 5 .. etc."

So, Model::create() would never give back ID 4, 5 in this case, it would go on at 101.

1 like

Please or to participate in this conversation.