How to duplicate a MySQL table, including indexes and data?

In this post, we are going to show you how to copy a table in MySQL.

First, this query will copy the data and structure, but the indexes are not included:

CREATE TABLE new_table SELECT * FROM old_table;

Second, this query will copy the table structure and indexes, but not data:

CREATE TABLE new_table LIKE old_table;

So, to copy everything, including database objects such as indexes, primary key constraint, foreign key constraints, triggers, etc., run these queries:

CREATE TABLE new_table LIKE old_table; 
INSERT new_table SELECT * FROM old_table;

If you want to copy a table from one database to another database:

CREATE TABLE destination_db.new_table LIKE source_db.old_table;

INSERT destination_db.new_table
SELECT
    *
FROM
    source_db.old_table;

Using TablePlus, you can use the Export and Import Wizard to duplicate one table as well as multiple tables.

If you want to copy the creation of the table using TablePlus, you can do it in two ways:

Switch to the structure tab from the table view (Cmd + Shift + ]), then click on the Definition button near the top right to see the CREATE TABLE statement.
Or you can install the Dump Table plugin: press Cmd + L to open plugin manager, then install Dump Table. After you install it successfully, you can right-click on a table and choose Copy Creation.

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 