MySQL Data migration

A. Scene

The migration of data from the previous old project to the new project involves a summary of the table data migration.

Two. Example

There are three 22 associated tables that need to be combined into a table to import a new project, and at the very beginning, I was exporting three tables of data in Excel, using the VLOOKUP function in Excel to merge three tables into one, and then inserting SQL INSERT statements in Excel. Copied to MySQL to do batch additions, but dropped off halfway.

1. A table in the old database that stores information is stored in a few fields that are too long, copied and exported to Excel data format is chaotic, can not do the following merge table and SQL statement stitching operation

2. Even if 1 is not present, the data of the function will not be used properly when merging the table.

3. Low efficiency and error-prone

Export the three tables from the old database to local and import them into the temporarily created database

1. Two associated tables are stored in a new table

CREATE table new Table     Select field 1, Field 2, Field 3 ... from source table 1 inner JOIN source table 2 on source table 1. Primary key = Source table 2. Foreign key

2. Save the newly added table and another table in a new table with the same code as above

3. The final new table is the consolidated table of three tables, why not one time to do three tables of the association and then into a new table, Midway will meet the table data will not, need to do the data amount of verification, to prevent errors

4. Save the new table in the target table for the new database, and the target table will be added to the actual business by setting default values for the individual fields, all the last steps to make the default field value modification of the target table

　　　

Two associated tables that store the value of one field of a table in a specified field in another table

Update target table, source table set  target table. field = Source table. field where  target table. id = source table. ID

　　

　　　　

　　　

MySQL Data migration