CREATE TABLE LIKE

As with Oracle MySQL offers the ability to create a table based on the definition of another. This can be done in two ways, one which will be familiar to Oracle users and another which is only available in MySQL.

The first way is to use a select statement in the create table command like so.

mysql> create table emps2 as select * from emps;
Query OK, 3 rows affected (0.20 sec)
Records: 3  Duplicates: 0  Warnings: 0

This creates a table with the same structure as the table we based the select statement on. We can add or remove columns from the table by explicitly specifying the column names in the select like so.

mysql> create table emps3 as select emp_id, emp_name from emps;
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc emps3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| emp_id   | int(11)     | NO   |     | 0       |       |
| emp_name | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

The good thing about using this method is that it populates the table with the data from the select statement also. This makes it a great way to backup tables before running potentially damaging SQL against them. If you want the new table to be empty once it's created you can simply specify a where clause that returns no rows. Personally I use WHERE 1=2 as unless the rules of mathmatics change will always be false and hence return no rows.

mysql> create table emps4 as select emp_id, emp_name from emps where 1=2;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emps4;
Empty set (0.00 sec)

One downside to this approach on both MySQL and Oracle is that it doesn't make an exact replica of the table, for example any index against the table will not be receated for the new table.

MySQL however offers a solution to this with the LIKE keyword. This can be used to create a table based on the definition of another. This is done as follows.

mysql> create table emps5 like emps;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from emps5;
Empty set (0.00 sec)

The downside of this method is that the data isn't copied across in the way it is using an SQL statement in the create. But it's easy to do that using a command like follows.

mysql> insert into emps5 select * from emps;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0