How can we automatically define the structure of MySQL table same as the structure of another table?

CREATE TABLE command with LIKE keyword will be able to define the structure of a MySQL table same as the structure of another table.

Syntax

CREATE TABLE new_table LIKE old_table;

Example

mysql> Create table employee(ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, NAME VARCHAR(20)); Query OK, 0 rows affected (0.21 sec) mysql> Describe employee; +-------+-------------+------+-----+---------+----------------+ | Field | Type        | Null | Key | Default | Extra          | +-------+-------------+------+-----+---------+----------------+ | ID    | int(11)     | NO   | PRI | NULL    | auto_increment | | NAME  | varchar(20) | YES  |     | NULL    |                | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.07 sec)

The query below will create the table employee1 having a similar structure as table employee. It can be checked by running the DESCRIBE query.

mysql> create table employee1 like employee; Query OK, 0 rows affected (0.19 sec) mysql> describe employee1; +-------+-------------+------+-----+---------+----------------+ | Field | Type        | Null | Key | Default | Extra          | +-------+-------------+------+-----+---------+----------------+ | ID    | int(11)     | NO   | PRI | NULL    | auto_increment | | NAME  | varchar(20) | YES  |     | NULL    |                | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.14 sec)