CODEWITHSUNDEEP
mysqldatabase-migration
WeSee
WeSee

Reputation: 3762

MySQL: How to migrate tables while preserving relationship?

I have several tables related to each other with foreign keys. Each table has a primary key with autoincrement. There are some thousand rows in each table.

Here are my tables:

CREATE TABLE table1 (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    val INT
);
CREATE TABLE table2 (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    val INT, 
    table1_id INT REFERENCES table1 (id)
);
INSERT INTO table1 VALUES (1,1), (2,2), (3,3);
INSERT INTO table2 VALUES (1,1,1), (2,2,2), (3,3,3);

I need to add all data to another database where the tables already exist (with the same definition) and have some data in it. The existing data must be preserved.

How to export and reimport these tables while preserving relationship between these tables?

My idea was to write a program to export the tables into a JSON and another progam to do the reimport by inserting the rows an adjusting the foreign keys (I mainly use PHP). or should I use Microsoft Excel? But I suppose there are simpler ways to go.

Upvotes: 1

Views: 615

Answers (1)

Akina
Akina

Reputation: 42685

An example.

-- working tables with data
CREATE TABLE main_w (id_m INT AUTO_INCREMENT PRIMARY KEY, 
                     val INT);
CREATE TABLE slave_w (id_s INT AUTO_INCREMENT PRIMARY KEY, 
                      val INT, 
                      id_m INT REFERENCES main_w (id_m));
INSERT INTO main_w VALUES (1,1), (2,2), (3,3);
INSERT INTO slave_w VALUES (1,1,1), (2,2,2), (3,3,3);

-- temporary tables with data to be added
CREATE TABLE main_s (id_m INT, 
                     val INT);
CREATE TABLE slave_s (id_s INT, 
                      val INT, 
                      id_m INT);
INSERT INTO main_s VALUES (1,11), (2,22), (3,33);
INSERT INTO slave_s VALUES (1,11,1), (2,22,2), (3,33,3);

-- add data from temporary main to working main
INSERT INTO main_w
SELECT NULL, val 
FROM main_s;

-- add data from temporary slave to working slave adjusting references
INSERT INTO slave_w
SELECT NULL, slave_s.val, main_w.id_m
FROM slave_s
JOIN main_s ON slave_s.id_m = main_s.id_m
JOIN main_w ON main_s.val = main_w.val;

db<>fiddle here

Upvotes: 1

Related Questions