MySQL provides us with several ways to copy a database table. The method we use will depend on our requirements.

We can choose to copy the table, its data, and its indexes. Or we can copy just the table and data without the indexes. We can alternatively generate the code that will enable us to copy the table later.

The CREATE TABLE ... AS SELECT Statement

We can use the CREATE TABLE ... AS SELECT statement to copy the table and its data, but not its indexes.

Suppose we have a table called Pets. We can use the following statement to copy that table, along with its data:

CREATE TABLE Pets2 AS
(SELECT * FROM Pets);

Here, I copied a table called Pets and named the new table Pets2. I also copied all data from the original table to its clone.

If we don’t want to insert the data, we can modify the query to return no results. For example:

CREATE TABLE Pets2 AS 
(SELECT * FROM Pets LIMIT 0);

As mentioned, this method doesn’t copy any indexes associated with the table. If we want those, we can use the next option.

The CREATE TABLE ... LIKE Statement

MySQL also has a CREATE TABLE ... LIKE option, which allows us to clone a table along with any column attributes and indexes defined in the original table. However, this creates an empty table (no data is copied).

Let’s clone our Pets table again:

CREATE TABLE Pets3 LIKE Pets;

This time I called the new table Pets3. As alluded to, this is an empty table (it contains no data), but it does have the column attributes and indexes associated with the original table.

The CREATE TABLE ... AS TABLE Statement

Another way to copy the table and its data is to use the CREATE TABLE ... AS TABLE statement:

CREATE TABLE Pets4 AS TABLE Pets;

This created a table called Pets4, based on the Pets table, and copied the data from Pets to Pets4.

Note that this didn’t copy the indexes (such as the primary key).

The SHOW CREATE TABLE Statement

Another option is to use the SHOW CREATE TABLE statement. Running this statement generates the code that we can use later to create the same table.

Example:

SHOW CREATE TABLE pets;

Result:

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pets  | CREATE TABLE `pets` (
  `PetId` int NOT NULL,
  `PetTypeId` int NOT NULL,
  `OwnerId` int NOT NULL,
  `PetName` varchar(60) NOT NULL,
  `DOB` date DEFAULT NULL,
  PRIMARY KEY (`PetId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

That shows the CREATE TABLE statement required to recreate the pets table. To clone the table, we would need to change the name of the table in the code, as well as the name of any indexes.