I'd like write a php script that merges several databases, and I'd like to be certain of methods for everyone it before I start anything.

I've 4 databases which have a similar structure and almost same data. I wish to merge them with no duplicate entry while protecting (or re-connecting) the foreign secrets.

For instance there's a db1.product table that is nearly as good as db2.products and so i think I would need to use LIKE comparison on name and description posts to be certain which i only place new rows. However, when merging the orders table I must make certain the productID still signifies the best product.

And So I considered 2 solutions :

  1. Either I personally use for every table insert into db1.x as select * from db2.x and then suggest new links and appearance for duplicate using triggers.

  2. Either I remove duplicate records increase new foreign secrets (after getting dropped constraints) after which place row in to the primary database.

  3. Just heard about MySQL Data Compare and Toad for mySQL, could they assist me to merge tables ?

Could someone indicate in my experience what ought to be the right solution ?

sorry for my british and thanks !

First factor is how's it going identifying whether items are identical? You pointed out LIKE comparison on title and outline. You have to begin a rule what states that method is one and also the same inside your db1, db2 and so forth.

However, let us think that product's title and outline would be the characteristics that comprise it.

ALTER TABLE products ADD UNIQUE('name', 'description');

Run this on all your databases.

After you have done that, choose among the databases you want to import into and run the next query:

INSERT IGNORE INTO db1.products SELECT * FROM db2.products;

Repeat for that remaining databases.

Naturally, all of this fails if you cannot figure out how you are likely to compare the items. Note: never use reserved words for the column names for example word "title".

First of all, have fun with this - seems like a difficult job.

Next, I would not do that with PHP - I'd write SQL to complete the job, presuming this can be a one-off migration task and never a recurring task.

Being an approach, I'd perform the following.

  1. Produce a database using the schema you would like - it may sound like all of your 4 databases have small versions within the schema. Just produce the schema for the time being, don't be concerned concerning the data.

  2. Produce a "working" database, with similar schema, however with posts for "old" primary secrets. For example:

    table ORDER order_id int primary key auto increment old_order_id int not null ...other posts...

    table ORDER_LINE order_line_id int primary key auto increment old_order_line_id int not null order_id int foreign key ...other posts...

  3. Table by table, Place to your working database out of your first source database. Allow the primary secrets auto_increment, but place the original primary key in to the "old_" column. For example:

    place into workingdb.orders choose null, order_id, ....other posts... from db1.orders

Where you've got a foreign key, populate it by locating the record within the old_ column. For example:

insert into workingdb.order_line
select null, ol.order_line_id, o.order_id
from   db1.order_line ol, 
       workingdb.order
where  ol.order_id = o.old_order_id
  1. Repeat all over again for that other databases.

  2. Finally, copy the information out of your working database in to the "proper" database. This really is optional - assistance to support the old IDs for searches etc.