How to Duplicate a Table in MySQL

Duplicating a table in MySQL is a useful operation when you need to create a backup of a table, copy its structure, or test modifications without affecting the original table. In this tutorial, we’ll walk you through the process of duplicating a table step-by-step.

Step 1: Duplicate Table Structure Only

If you only want to copy the structure of the table without the data, you can use the following SQL query:

CREATE TABLE new_table LIKE original_table;

This will create a new table with the same columns, indexes, and constraints as the original table, but without copying any data.

Step 2: Duplicate Table with Data

If you want to duplicate both the table structure and its data, you can use the following query:

CREATE TABLE new_table AS SELECT * FROM original_table;

This will create a new table with the same structure and copy all the data from the original table into it.

Step 3: Verify the Duplicated Table

After running the SQL query, you can verify that the table was successfully duplicated by using:

SHOW TABLES;

This will display a list of all tables, including your newly created table.

Step 4: Copy Indexes (Optional)

Note that the indexes of the original table are not copied when using the CREATE TABLE AS SELECT method. To ensure that the new table has the same indexes, you can manually add them using the SHOW CREATE TABLE command:

SHOW CREATE TABLE original_table;

Then, copy the index definitions from the original table's CREATE statement and apply them to the new table.

Conclusion

Duplicating a table in MySQL is a simple and effective process. Whether you’re creating backups, working on tests, or migrating data, MySQL provides simple commands to duplicate tables both with or without data. Remember that when copying data, indexes are not automatically copied, so you'll need to add them manually if necessary.