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.