Complete Method for Creating New Tables Based on Existing Structure and Inserting Deduplicated Data in MySQL

Technical Implementation of Table Structure Replication and Data Deduplication in MySQL

In database management and data migration scenarios, there is often a need to create new tables with identical structures based on existing tables while requiring deduplication of source table data. Many developers initially attempt to combine table structure copying with data operations into a single SQL statement, but this approach is not supported in MySQL. This article provides a comprehensive analysis of this technical requirement and offers a complete solution.

Analysis of Common Error Patterns

Developers frequently attempt syntax similar to the following:

CREATE TABLE New_Users LIKE Old_Users, 
AS
(SELECT * FROM Old_Users GROUP BY ID);

This syntax cannot execute in MySQL because the CREATE TABLE ... LIKE statement and the CREATE TABLE ... AS SELECT statement have different syntax structures and execution logic. The MySQL official documentation clearly states that the LIKE clause is used to create empty tables, while the AS SELECT clause is used to create new tables containing query results—the two cannot be used simultaneously.

Correct Step-by-Step Implementation Method

The correct implementation requires two independent steps: first copy the table structure, then insert deduplicated data.

Step 1: Copy Table Structure

Use the CREATE TABLE ... LIKE statement to create a new table with identical structure:

CREATE TABLE New_Users LIKE Old_Users;

This statement creates a new table named New_Users with exactly the same column definitions, data types, indexes, constraints, and all structural attributes as the Old_Users table, but without any data.

Step 2: Insert Deduplicated Data

Use the INSERT INTO ... SELECT statement combined with the GROUP BY clause to insert deduplicated data:

INSERT INTO New_Users 
SELECT * FROM Old_Users GROUP BY ID;

Here it is assumed that the ID field serves as a unique identifier. Using GROUP BY ID eliminates duplicate records based on ID. If deduplication logic is more complex, advanced techniques such as DISTINCT or window functions may be required.

In-Depth Technical Principle Analysis

How the CREATE TABLE LIKE Statement Works

The CREATE TABLE ... LIKE statement in MySQL performs metadata replication. It reads the table definition information of the source table, including:

• All column names, data types, default values, and NULL permissions
• Constraint definitions such as primary keys, foreign keys, and unique constraints
• All index definitions (including B-tree, full-text indexes, etc.)
• Table options such as storage engine, character set, and collation

However, it does not copy the following:

• Actual data rows in the table
• Trigger definitions
• Certain specific table options (such as AUTO_INCREMENT values)

Considerations for Data Deduplication Operations

When using GROUP BY for deduplication, several key points must be noted:

1. Impact of Aggregate Functions: When using GROUP BY, the selection behavior of non-grouped columns depends on MySQL's SQL mode. In strict mode, aggregate functions must be explicitly specified.
2. Accuracy of Deduplication Logic: GROUP BY ID assumes ID is a unique identifier. If actual business requirements involve other duplicate determination criteria, adjustments must be made accordingly.
3. Performance Considerations: When performing deduplication operations on large tables, ensure appropriate indexes exist on relevant fields.

Extended Application Scenarios

Handling Complex Deduplication Requirements

For more complex deduplication needs, the following methods can be used:

-- Using DISTINCT for multi-column deduplication
INSERT INTO New_Users 
SELECT DISTINCT column1, column2, column3 
FROM Old_Users;

-- Using window functions to retain the latest records
INSERT INTO New_Users 
SELECT * FROM (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY ID ORDER BY create_time DESC) as rn
  FROM Old_Users
) t WHERE rn = 1;

Table Structure Modification and Data Migration

In actual projects, it is often necessary to modify table structures while copying them:

-- First copy the basic structure
CREATE TABLE New_Users LIKE Old_Users;

-- Modify table structure
ALTER TABLE New_Users 
ADD COLUMN new_column VARCHAR(100),
MODIFY COLUMN old_column INT NOT NULL;

-- Insert data
INSERT INTO New_Users (col1, col2, col3, new_column)
SELECT col1, col2, col3, 'default_value'
FROM Old_Users GROUP BY ID;

Performance Optimization Recommendations

1. Transaction Management: For large-scale data migration, it is recommended to execute operations within transactions to ensure data consistency.
2. Batch Insert Optimization: Using INSERT INTO ... SELECT is generally more efficient than row-by-row insertion, but extremely large tables may require batch processing.
3. Index Strategy: Temporarily disabling non-critical indexes before data insertion and rebuilding them after completion can significantly improve performance.
4. Storage Engine Considerations: If the source table uses InnoDB while the target table uses MyISAM, attention must be paid to differences in transaction support and locking mechanisms.

Error Handling and Debugging

Common errors when performing such operations include:

• Data type mismatches: Ensure columns returned by the SELECT query exactly match target table definitions
• Constraint conflicts: Check whether unique constraints or foreign key constraints may cause insertion failures
• Permission issues: Ensure users have appropriate operational permissions for both source and target tables

It is recommended to validate the entire operation flow in a test environment before executing in production.

Conclusion

The requirement to create new tables based on existing tables and insert deduplicated data in MySQL must be accomplished through two separate steps: CREATE TABLE ... LIKE and INSERT INTO ... SELECT. Although this design increases operational steps, it provides greater flexibility and control precision. Understanding the principles and best practices of this technical implementation is significant for database design, data migration, and ETL process development.