Data migration is a common task during MySQL database administration. Sometimes, we may need to migrate data from one table (source table) to another table (destination table), which may be due to data archiving, reorganizing table structure, migrating data, or merging tables. In this article, we will introduce 3 methods, for how MySQL moves data from one table to another and provide detailed step-by-step instructions.
Method 1: INSERT INTO … SELECT Statement
This is the most common and straightforward method of how MySQL moves data from one table to another, and is suitable for copying data from one table to a new table with the same or compatible structure.
sql
— Create the target table (if not already created)
CREATE TABLE new_table LIKE original_table;
— Move data from the original table to the new table
INSERT INTO new_table
SELECT * FROM original_table.
Method 2: RENAME TABLE in Combination with the CREATE TABLE AS Statement
This method is used when you need to keep the original table structure but want to quickly change where the data is stored. First, create a new table with the original table and populate it with data, then rename the location of both tables.
sql
— Create a new table with the original table and populate it with data
CREATE TABLE new_table AS SELECT * FROM original_table WHERE 1=2; — WHERE 1=2 is to not copy any of the data, only create the table structure and indexes
— Ensure that all transactions are committed to avoid data consistency issues.
COMMIT.
— Inserts all data from the original table into the new table.
INSERT INTO new_table
SELECT * FROM original_table.
— Ensure that all data has been successfully transferred, and then rename the table.
RENAME TABLE original_table TO old_table, new_table TO original_table; — Insert all data from the original table into the new table.
Method 3: Use A Combination of LOAD DATA INFILE and OUTFILE
For large datasets, you can export the data to a CSV or other text file first, and then import it into a new table. This method can effectively utilize disk I/O and improve the efficiency of large-scale data transfer.
sql
— Export raw table data to CSV file
SELECT *
INTO OUTFILE ‘/path/to/yourfile.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
FROM original_table;
— Empty the target table (if data already exists)
TRUNCATE TABLE new_table;
— Import the CSV file data into the new table.
LOAD DATA INFILE ‘/path/to/yourfile.csv’
INTO TABLE new_table
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’.
Conclusion
There are the three methods of MySQL moves data from one table to another. Which method you choose depends on your actual needs, including the size of data volume, the complexity of table structure, and the impact on system performance. Regardless of which method you use, ensure that you back up important data before the operation and try to perform it during the maintenance window to minimize the impact on your business. At the same time, understanding the applicable scenarios and considerations of each method can help you accomplish the data migration task more efficiently.