MySQL

How To Create a Table Like Another Table in MySQL

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

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MYSQL 8.0+
THE MYSQL DATABASE VERSION 8 OR LATER

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:

  1. CREATE TABLE ... SELECT: creates one table from the selected columns of another table.
  2. CREATE TABLE ... LIKE: creates an empty table from the definition of another table.

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:

Copy
        
1 CREATE TABLE new_table [AS] SELECT * FROM original_table;

Where

  • new_table is the name of the new table to create
  • original_table is the name of the original table to execute the SELECT query on

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:

Copy
        
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:

The wp_data table in DbVisualizer.
The wp_data table in DbVisualizer

Now, let’s launch a CREATE TABLE ... SELECT query:

Copy
        
1 CREATE TABLE countries 2 SELECT * FROM wp_country 3 LIMIT 0;
Running a CREATE TABLE ... SELECT query in DbVisualizer.
Running a CREATE TABLE ... SELECT query in DbVisualizer

You now have access to the countries table.

The countries table in DbVisualizer.
The countries table in DbVisualizer

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:

Copy
        
1 CREATE TABLE new_table LIKE original_table;

Where

  • new_table is the name of the new table
  • original_table is the name of the original table to copy the definition from

With CREATE TABLE … LIKE, the destination table will preserve:

  • any column attribute from the columns of the original table
  • the primary key specified in the original table
  • any index defined in the original table
  • any generated column from the original table
  • any CHECK constraint from the original table

At the same time, the CREATE TABLE … LIKE MySQL statement will not preserve:

  • any DATA DIRECTORY or INDEX DIRECTORY option set on the original table
  • any foreign key definition specified in the original table
  • any trigger associated with the original table

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.

Dbvis download link img

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:

Copy
        
1 CREATE TABLE countries LIKE wp_country;
Executing a CREATE TABLE ... LIKE query in DbVisualizer.
Executing a CREATE TABLE ... LIKE query DbVisualizer

This is what the new countries table looks like:

Overview of the new countries table in DbVisualizer.
Overview of the new countries table in DbVisualizer.

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.

Countries table with Null column attributes.
Note the Null 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.

About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

A Guide To the SQL DECLARE Statement

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SERVER 7 min 2024-12-12
title

SQL Upsert: Inserting a Record If It Does Not Exist

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-12-10
title

A Complete Guide to the MySQL COUNT Function

author Antonello Zanini tags MySQL 9 min 2024-12-09
title

MySQL Nullable Columns: Everything You Need to Know

author Antonello Zanini tags MySQL 6 min 2024-12-03
title

A Complete Guide to the MySQL GROUP_CONCAT Function

author Antonello Zanini tags Data aggregation MySQL 8 min 2024-12-02
title

How and Why to Add an Index in SQL

author Lukas Vileikis tags MySQL POSTGRESQL Search SQL SQL SERVER 8 min 2024-11-28
title

MySQL 9.0 and the New EXPLAIN Features

author Lukas Vileikis tags MySQL SQL 9 min 2024-11-27
title

MySQL CAST: A Guide to the Data Conversion Function

author Antonello Zanini tags MySQL 3 min 2024-11-25
title

List of SQL Reserved Words in All Major Databases

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 16 min 2024-11-20
title

SQL Variable: What It Is and How To Use It

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-11-18

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗