We have several options when it comes to using SQL to clone a table in MariaDB. And we have the option to clone the table with data, or without data.

Here are three SQL options for cloning a table in MariaDB.

The CREATE TABLE ... AS SELECT Statement

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

Suppose we have a table called Pets. We can use the following statement to copy that table and 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 the data, we can modify the query to return no results. For example:

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

This method doesn’t copy any indexes associated with the table. If we want those, we can use the CREATE TABLE ... LIKE option below.

Using the CREATE TABLE ... AS SELECT option, if you want to use the same indexes, or the same columns attributes ([NOT] NULL, DEFAULT, AUTO_INCREMENT) in the new table, you need to specify them manually. Types and sizes are not automatically preserved if no data returned by the SELECT requires the full size, and VARCHAR could be converted into CHAR.

The CREATE TABLE ... LIKE Statement

MariaDB also has a CREATE TABLE ... LIKE option, which allows us to clone a table with the same definition as another table, including columns, indexes, and table options. Foreign key definitions, as well as any DATA DIRECTORY or INDEX DIRECTORY table options specified on the original table, will not be created.

This option 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. This is an empty table (it contains no data), but it does have the column attributes, indexes etc associated with the original table.

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;

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. We would also need to insert the data.