How to Copy Table Structures Without Copying Data in MySQL

1. Copy Table Structures Without Copying Data in MySQL
2. Use CREATE TABLE ... LIKE to Copy Table Structure in MySQL

Today, we will see how to copy table structures without copying data in MySQL. We will use the CREATE TABLE ... LIKE statement to copy the table structure only.

Copy Table Structures Without Copying Data in MySQL

To learn in detail, let’s create a table named users that resides in the test database and has an ID, USERNAME, and EMAIL as its attributes.

Create a Table (users table):

#create a table named 'users' in the 'test' database
CREATE TABLE `test`.`users` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `USERNAME` VARCHAR(45) NOT NULL,
  `EMAIL` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`));

Next, we insert a record to differentiate that only the table’s structure is copied into new tables, not the data.

INSERT INTO `test`.`users` (USERNAME, EMAIL) VALUES ('mehvish', 'delfstack@example.com');

Use CREATE TABLE ... LIKE to Copy Table Structure in MySQL

We can copy the table structure, also called table definition, by running the CREATE TABLE ... LIKE statement, which is given below.

# Syntax: CREATE TABLE new_table LIKE original_table;
CREATE TABLE new_user_one LIKE test.users;

The query given above will make the structure of the new_user_one table exactly like the users table.

It will copy all the column names, data types, default values, and everything except the table’s contents. It will not copy the table data.

We can re-check the new table’s column names, their data type etc., by using the following query.

SHOW CREATE TABLE test.new_user_one;

If you have some situation to have the data, you can do it two ways. Either copy the data while copying the table structure or insert it after copying; both are given below.

Copy Data After Copying the Table Structure:

# Syntax: INSERT INTO new_table SELECT * FROM original_table;
INSERT INTO new_user_one SELECT * FROM test.users;

Copy Data While Copying the Structure of the Table:

# Syntax: CREATE TABLE new_table AS SELECT * FROM original_table;
CREATE TABLE new_user_two AS SELECT * FROM test.users;

In a second way, we can copy or clone the content of the table by running the CREATE TABLE ... AS SELECT statement. Copying data may take time in case of having big tables.

Remember, the new table (new_user_two here) will only copy the basic column definitions, default values and null settings. It never inherits the definitions of AUTO_INCREMENT and INDEXES.

You can confirm by using the following query.

SHOW CREATE TABLE test.new_user_two;