Complete Guide to Copying Data from Existing Tables to New Tables in MySQL

Fundamentals of Data Replication

In database management systems, data replication is a common and critical operational task. MySQL offers multiple approaches for inter-table data copying, with the INSERT INTO SELECT statement being one of the most flexible and widely used methods. The advantage of this approach lies in its ability to complete both data querying and insertion operations within a single SQL statement, eliminating the need for cumbersome intermediate processing steps.

Field Mapping Replication Strategy

When the field structures of source and target tables are not identical, field mapping becomes necessary for data replication. Consider the following typical application scenario:

INSERT INTO table2 (st_id, uid, changed, status, assign_status)
SELECT st_id, from_uid, NOW(), 'Pending', 'Assigned'
FROM table1

In this example, we implement precise field mapping from table1 to table2:

• The st_id field is copied directly from source to target table
• The value from from_uid field is mapped to the target table's uid field
• The changed field uses the NOW() function to generate current timestamp
• The status and assign_status fields are set to fixed values 'Pending' and 'Assigned' respectively

Conditional Filtering and Data Selection

In practical applications, copying all data from the source table is often unnecessary. By adding a WHERE clause, precise control over the copied data can be achieved:

INSERT INTO table2 (st_id, uid, changed, status, assign_status)
SELECT st_id, from_uid, NOW(), 'Pending', 'Assigned'
FROM table1
WHERE created > '2023-01-01'

This query will only copy data records created after January 1, 2023, effectively reducing unnecessary data transfer and processing overhead.

Data Type Compatibility Considerations

When performing data replication operations, ensuring compatibility between source and target field data types is essential. MySQL attempts implicit type conversion, but certain incompatible type combinations may lead to data truncation or insertion failures. It is recommended to verify field type matching before replication, particularly in scenarios involving datetime values, numerical precision, and string length constraints.

Performance Optimization Recommendations

Performance optimization becomes particularly important for large-scale data replication operations:

• Create appropriate indexes on the source table to accelerate SELECT queries
• Consider using transactions to ensure data consistency, especially when errors might occur during replication
• For extremely large datasets, process in batches to avoid prolonged table locking
• Monitor server resource usage to ensure replication operations don't impact other database activities

Error Handling and Data Validation

In practical deployment scenarios, the following strategies are recommended to ensure replication operation reliability:

START TRANSACTION;

INSERT INTO table2 (st_id, uid, changed, status, assign_status)
SELECT st_id, from_uid, NOW(), 'Pending', 'Assigned'
FROM table1
WHERE created > '2023-01-01';

-- Validate replication results
SELECT COUNT(*) AS copied_count FROM table2 
WHERE changed >= DATE_SUB(NOW(), INTERVAL 1 HOUR);

COMMIT;

Through transaction mechanisms and result validation, atomicity and correctness of data replication can be ensured.