MySQL Tutorial: Using CREATE TABLE LIKE for Easy Cloning

Creating a new table in MySQL that mirrors the structure of an existing table is both straightforward and efficient using the CREATE TABLE LIKE statement. This exploration equips you with the knowledge and tools to streamline your database management, enhance efficiency, and more.

What isCREATE TABLE LIKE in MySQL?

The CREATE TABLE LIKE statement allows you to clone the structure of an existing table in MySQL. This clones column names, data types, indexes, and other attributes, while excluding the data from the original table.

Here’s the basic syntax:

CREATE TABLE new_table LIKE existing_table;

In this syntax, replace new_table with the name of the table you want to create, and existing_table with the name of the table whose structure you wish to clone.

Example

Let’s say you have an employees table and want to create a employees_backup table with the same structure. Execute the following command:

CREATE TABLE employees_backup LIKE employees;

Executing this command creates a new employees_backup table with identical column definitions, indexes, and table properties as the employees table but omits the data.

Tips and considerations

• Remember that CREATE TABLE LIKE does not copy foreign key constraints. Manually add these to your new table if necessary.
• The new table inherits the engine and default charset from the original. If required, alter these attributes in the new table.
• Apply this command judiciously in production environments to avoid performance impacts, especially when duplicating large tables.

Employing this method effectively sets up test environments or backs up table structures efficiently, bypassing the data copying overhead and maintaining development momentum.