PHP migrating data avoiding id collisions
Jun 29, 2010
by
0
You ever needed to move a lot of data from one database to another but maintain relationships and key associations? You can export a sql or csv dump and import it into another database, but this leaves one major problem.... If you are using incrementing numerical id's then your import into the second database will probably create id collisions ( ids from the old database being the same as the ids in the new database ). If both of the databases are used frequently, the amount of collisions will prevent you from importing your data this way. In order to maintain the relationships between your tables without causing id collisions, you are going to have to script your export and import.
You can of course use a variety of formats for this: xml, csv, etc. You could even create a script to go from one database to the other, however depending no your situation this may not be a possibility for you. One option would be to use sqlite, it can serve as a transport medium between the two databases. You can clone the schemas from your main database into an sqlite database file ( be warned though that some features and data types in mysql will not directly port into sqlite ). You can then take your sqlite file to your import script on another server, and import away.
The trick to avoiding id collisions in this case, will be to insert the primary record into the new database using all the old data except the id column, attain the newly assigned id, then use that id for the inserts of the secondary tables that relate to the primary record. For example, we have a blog post with an id of 4 and a title "great blog post". This blog post has 6 comments that relate to the blog post by the blog post id, every comment record has a blog_post_id field. You must take the data of the blog post, insert it into your new table, retrieve the newly assigned id, then fetch the comments for the old id from your transport medium and insert each comment using the newly assigned id so that the comments will relate properly with the new blog post record.
This process can either be a short one, or a long one, depending on how many tables and relationships exist in the tables you are trying to migrate.
Leave a comment
To leave a comment, please log in / sign up
RSS Feed
Facebook
Twitter
LinkedIn
YouTube