MySQL Table Copying and Data Migration: How to Clone Structure, Copy Data, and Avoid Common Pitfalls - Devuly | Smart Analytics for Developers & Projects

This article focuses on the most common MySQL table copying and data migration tasks, addressing practical questions such as how to preserve schema, whether primary keys and indexes are inherited, and how to migrate data conditionally. It covers three core SQL patterns: CREATE TABLE ... SELECT, CREATE TABLE ... LIKE, and INSERT INTO ... SELECT. Keywords: MySQL table copy, data migration, SQL practical guide.

Technical Specification	Details
Language	SQL
License	CC 4.0 BY-SA (adapted from original content)
Stars	Not provided
Core Dependencies	MySQL 5.7+/8.0+, InnoDB

Table of Contents

Table copying in MySQL is not the same as a full clone

In day-to-day MySQL development, backup workflows, and test environment setup, copying tables is a very common operation. However, a “successful copy” does not mean the new table is structurally identical to the original. Columns, default values, primary keys, indexes, auto-increment behavior, and foreign keys are preserved differently depending on the SQL syntax you use.

The most common misconception is to run create table new_table select * from old_table; and assume the new table is fully equivalent to the original one. In reality, this statement is better suited for quickly generating a data snapshot than for performing a strict schema clone.

The differences between the three copy methods should guide your implementation choice

Requirement	Recommended Syntax	Copies Data	Preserves Primary Keys / Indexes
Copy table structure and data	`create table b select * from a;`	Yes	No
Copy only the full schema	`create table b like a;`	No	Yes
Copy data when the target table already exists	`insert into b select * from a;`	Yes	Depends on the target table

-- Create the original test table
create table user_info (
    id int primary key auto_increment, -- Primary key with auto-increment
    username varchar(50) not null,     -- Username cannot be null
    age int,
    create_time datetime default current_timestamp -- Default creation time
);

-- Insert test data
insert into user_info(username, age)
values ('zhangsan', 22), ('lisi', 25), ('wangwu', 28);

This SQL builds the base test environment used in all subsequent copy examples.

Using CREATE TABLE … SELECT works best for quickly copying structure and data

CREATE TABLE ... SELECT creates a new table and writes the query result directly into it. It preserves column names and column types, and it also supports WHERE filters, which makes it ideal for quickly generating temporary tables, reporting snapshots, or subsets of test data.

-- Copy the column structure and all data into a new table
create table user_info_copy
select * from user_info;

This statement creates the table and copies the data in a single step, which is useful for quick backups and test initialization.

This method does not preserve primary keys, auto-increment, or indexes

Many production issues start here. Although the id column still exists in the new table, it is usually no longer a primary key and no longer retains the auto_increment attribute. Unique indexes, regular indexes, and foreign key constraints are also not copied automatically.

-- Inspect the copied table structure
desc user_info_copy;

-- Focus on the result: the id column usually no longer shows PRI or auto_increment

Use this step to verify structural differences after copying, so you do not mistake “same columns” for “same schema.”

You can copy only a subset of data by using filters

If your goal is not a full backup but rather extracting data based on business conditions, WHERE is the most direct option. It is especially useful for masked testing, canary validation, and sample preparation before partitioned data migration.

-- Copy only rows where age is greater than 25
create table user_info_copy2
select * from user_info
where age > 25;

This statement creates a new table and inserts only the rows that match the condition.

When copying only table structure, you should first decide whether constraints must be preserved

If you only need an empty table, there are usually two paths: one that copies only column definitions, and another that keeps primary keys, indexes, and auto-increment behavior intact. The syntax looks similar, but the results differ significantly.

Using WHERE 1=0 copies only the column structure

The essence of WHERE 1=0 is that it forces the query result to be empty, so the new table contains only the column definitions and no rows. However, because it still relies on CREATE TABLE ... SELECT, it does not preserve primary keys, indexes, or auto-increment attributes.

-- Copy only the column structure, without copying data
create table user_info_struct
select * from user_info
where 1 = 0;

This statement is useful for quickly generating a column template, but it is not suitable when you need strict schema consistency.

Using LIKE preserves the full table definition

If your goal is to create an empty table that can truly serve as a structural replacement for the original, you should use LIKE first. It preserves columns, primary keys, indexes, auto-increment settings, and other metadata, making it the standard approach for schema cloning.

-- Fully copy the table structure and preserve primary keys, indexes, and auto-increment
create table user_info_like like user_info;

This statement is well suited for backup tables, archive table templates, and prebuilt target tables.

When the target table already exists, use INSERT INTO … SELECT to copy data

When the target table has already been created, data migration should switch to INSERT INTO ... SELECT. This separates schema control from data import, making the process more predictable and production-friendly.

-- First, copy the full structure
create table user_info_data like user_info;

-- Then import all data
insert into user_info_data
select * from user_info;

This two-step approach is safer and better suited for migration tasks that require strong schema consistency.

Column mapping, deduplication, and conditional filtering are the most common extensions

In real systems, select * is rarely enough forever. You may need to migrate only specific columns, filter out dirty data, or avoid duplicate rows. All of these can be handled on the SELECT side.

-- Copy only specific columns; the column order must match the target table
insert into user_info_data(username, age)
select username, age from user_info;

-- Copy distinct rows only
insert into user_info_data
select distinct * from user_info;

-- Copy rows conditionally
insert into user_info_data
select * from user_info
where username like '%li%';

These statements cover three high-frequency scenarios: column mapping, deduplicated import, and conditional migration.

Cross-database copying and column transformations can be done in a single SQL statement

MySQL supports cross-database copying by using database name prefixes directly, which is especially practical for environment migration and multi-database cleanup. If you also need to rename columns or populate default values during the process, you can perform lightweight transformations in the SELECT clause.

-- Copy a table and its data across databases
create table db2.user_info_copy
select * from db1.user_info;

-- Rename columns and populate default values during copy
create table user_info_modify
select id,
       username as name,        -- Rename the column
       ifnull(age, 18) as age,  -- Fill null values with a default
       create_time
from user_info;

This style is useful for quickly generating table variants, but you still need to remember that constraints are not preserved automatically.

You must address the key production pitfalls in advance

First, CREATE TABLE ... SELECT does not preserve primary keys, indexes, or auto-increment settings, so it should not directly replace the original table for core write traffic.

Second, when using INSERT INTO ... SELECT, the target table’s column order, data types, and nullability must be compatible with the query result. Otherwise, you may trigger type conversion errors or failed inserts.

Large-table copying requires careful control of timing and resource usage

Copying a large volume of data consumes I/O, Buffer Pool capacity, and lock-related resources. If you run it during peak traffic, it may cause slow queries, replication lag, or higher primary-replica pressure. In production, prefer batch imports and, when necessary, combine them with transaction control or off-peak execution windows.

-- Manually adjust the auto-increment starting value
alter table user_info_data auto_increment = 1000;

Use this statement after copying to correct the auto-increment starting point and avoid future primary key conflicts or numbering anomalies.

You can use this decision table for quick syntax selection

Scenario	Recommended Approach	Why
Temporary full-table backup	`create table ... select`	Fast and completed in one step
Create an empty table with an identical schema	`create table ... like`	Preserves primary keys and indexes
Import data into an existing table	`insert into ... select`	Separates schema from data for better control
Build only a column template	`create table ... select ... where 1=0`	Simple, but does not preserve constraints
Cross-database migration	`create table db2... select * from db1...`	Direct and easy to understand

FAQ

Q1: Why does `create table ... select` drop the primary key?

A: Because this syntax is fundamentally “create a table from a query result.” It copies column definitions and the result set, not the full metadata of the original table. Primary keys, indexes, auto-increment settings, and foreign keys are not inherited automatically.

Q2: What is the best practice if I want to preserve the schema and copy the data?

A: First run create table new like old; to preserve the full schema, then run insert into new select * from old; to import the data. This is the most reliable combination.

Q3: What matters most when copying a large table?

A: Focus on lock impact, disk I/O, replication lag, and transaction duration. In production, run the operation during off-peak hours, migrate in batches, and validate the target table’s indexing and auto-increment strategy in advance.

Core summary: This article systematically explains the core MySQL syntax for table copying and data migration, including the use cases, constraint preservation differences, cross-database copying patterns, and common pitfalls of CREATE TABLE ... SELECT, CREATE TABLE ... LIKE, and INSERT INTO ... SELECT. It helps developers quickly complete backups, test table creation, and production-safe data migration.