intro
TL;DR: Learn everything you need to know about how you can create a table like another table in MySQL with the CREATE TABLE SELECT or CREATE TABLE LIKE query
In this article, you will learn how you can create a new table like another table using a single MySQL query. This is possible thanks to the CREATE TABLE ... SELECT and CREATE TABLE ... LIKE statements. These two MySQL variants of CREATE TABLE can help you copy a table definition into a new table.
Let's now dig into how to create a new empty table like another table in MySQL.
Can You Create a Table Like Another Table in MySQL?
The short answer is, “yes, you can!” In detail, you have two approaches to creating a table like another table in MySQL:
As you can see, these two SQL statements allow you to create a table from another. At the same time, they serve two different use cases. Let’s now learn more about both. At the end of this article, you will know everything about those two statements.
CREATE TABLE ... SELECT Statement
Let’s learn more about the CREATE TABLE ... SELECT statement in MySQL.
What Is the CREATE TABLE ... SELECT Statement?
CREATE TABLE ... SELECT creates a new table with one column for each element specified in the SELECT query. You can use this SQL statement with the following syntax:
1
CREATE TABLE new_table [AS] SELECT * FROM original_table;
Where
You can also use this query to append columns read from the original table to an existing table. In detail, the columns specified in the SELECT statement will be appended to the right side of the existing table.
Keep in mind that CREATE TABLE ... SELECT also copies the data from the selected columns. In detail, it creates a new row for each row in original_table. The selected columns will have the values read from the original tables, while the other existing columns will be initialized with their default values.
If you only want to use CREATE TABLE ... SELECT to create a new table given the definition of another table, then you should add the LIMIT 0 statement. In this case, the syntax becomes:
1
CREATE TABLE new_table [AS]
2
SELECT * FROM original_table
3
LIMIT 0;
Now, no data will be copied from original_table to new_table and no rows will be created.
Note that the CREATE TABLE ... SELECT statement does preserve the primary key info, indexes, triggers, generated column info, foreign keys, or CHECK constraints specified in the original table.
CREATE TABLE ... SELECT in Action
Let’s assume you have a wp_country table that contains all the countries in the world, as follows:
Now, let’s launch a CREATE TABLE ... SELECT query:
1
CREATE TABLE countries
2
SELECT * FROM wp_country
3
LIMIT 0;
You now have access to the countries table.
Note that the new table has the same columns as wp_country with the same column attributes, but does not have a primary key. You can notice this by the fact that the Null attribute is not empty, while the Key section is empty for each column. This is because the CREATE TABLE ... SELECT statement keeps column attributes but does not preserve primary key info.
CREATE TABLE ... LIKE Statement
Let’s dig into the CREATE TABLE ... LIKE statement in MySQL.
What Is the CREATE TABLE ... LIKE Statement?
CREATE TABLE ... LIKE creates a new empty table based on the definition of another table. You can use this MySQL statement with the following syntax:
1
CREATE TABLE new_table LIKE original_table;
Where
With CREATE TABLE … LIKE, the destination table will preserve:
At the same time, the CREATE TABLE … LIKE MySQL statement will not preserve:
Keep in main that a CREATE TABLE ... LIKE query performs the same checks as a CREATE TABLE one. In other words, if the current SQL mode is different from the mode used when creating the original table, the table definition may be considered invalid for the new mode and cause the query to fail. Also, you cannot perform CREATE TABLE ... LIKE while a LOCK TABLE statement is running on the original table.
If you are not familiar with this, each table has a lock flag associated with it. MySQL uses these lock to prevent other client sessions from accessing a table for a limited time. In detail, a client session is the period of time between a client's connection to a MySQL database and its disconnection. Note that a client session can only acquire or release table locks for itself.
CREATE TABLE ... LIKE in Action
Just like before, let's start from the wp_country table. This contains the list of all countries. Now, let’s assume you want to copy this table definition into a new table called countries.
You can achieve this with a CREATE TABLE ... LIKE query:
1
CREATE TABLE countries LIKE wp_country;
This is what the new countries table looks like:
As you can see, wp_country is empty but has the same primary key as the countries table. It also has the same column attributes.
This is because the CREATE TABLE ... LIKE statement preserves column attributes and primary key info. In other terms, that you can think of CREATE TABLE ... LIKE as an operation to copy the definition of a table, including all its characteristics but with no data. On the other hand, CREATE TABLE ... SELECT only performs a shallow copy of column names and data from a table to another. This is the main difference between the two SQL statements.
Conclusion
In this article, you learned everything you need to know about how you can create a table like another table in MySQL. As you saw, MySQL offers two approaches to achieve this. CREATE TABLE ... SELECT allows you to copy columns from one table to another, including their data. At the same time, it does not preserve information related to primary keys or indexes.
On the other hand, CREATE TABLE ... LIKE enables you to create a new table from the definition of another table. This statement does not copy data, but includes info about primary keys, indexes, CHECK constraints, and check constraints.
Here, you also took a look at how you can run those queries in DbVisualizer. If you are not familiar with this tool, DbVisualizer allows you to generate reference ER schemas automatically. This helps you understand what columns a table consists of and how it is related to other tables. You can use this feature to visually understand how the new table created with CREATE TABLE ... SELECT or CREATE TABLE ... LIKE relates to existing tables. Download and try DbVisualizer for free!
Thanks for reading! We hope that you found this article helpful.
