MySQL: Changing IDs of primary keys

I need to merge parallel tables from 2 different schemas. They are practically the same (some fields are different), but the IDs (their PKs) are different.

I need to conform one table to contain the same IDs for the parallel rows. (both tables continue to开发者_如何学编程 live after the merge, and one is synched to the other according to the IDs)

What is the best way to go about it?

• Renaming the IDs of the first table according to the second one, and then changing the auto_increment value of the PK?

• Creating a temporary table with the needed values inserted with the correct IDs, and then removing the original and renaming the temp?

• Is there a better way?

Besides, how can this affect indexes?

does it have any other reprocussions? (besides foreign_keys and places in the code - if exist - using hardcoded ID values)

---

1. Don't think you have an option. You have to generate new ids. When you insert records the auto_increment would anyway happen.
2. You can have an additional column reference which can have the original table key value to back trace.
3. PK indexes will be re-indexed.

Other repercussions depend on the app functionality which you have to consider.