MySQL CREATE TABLE LIKE.

Oftentimes you may want to mirror a new table’s structure, to that of an existing table in your database. In this blog post, we will look at using CREATE TABLE LIKE syntax for this task.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and MySQL 5.7.20 for these exercises.


CREATE TABLE LIKE

Let’s see the present tables in the database with their description,

mysql> SHOW TABLES;
+--------------------+
| Tables_in_projects |
+--------------------+
| friends            |
+--------------------+
1 row in set (0.00 sec)

mysql> DESC friends;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| friend_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| f_first_name | varchar(30) | YES  |     | NULL    |                |
| f_last_name  | varchar(30) | YES  |     | NULL    |                |
| f_cell_phone | char(12)    | YES  |     | NULL    |                |
| f_age        | int(11)     | YES  |     | NULL    |                |
| f_bday       | date        | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Wouldn’t it be nifty if you could just copy the exact same structure of the friends table to another table?
Well, you can.
Using CREATE TABLE LIKE syntax as below:
CREATE TABLE new_table_name LIKE original_table_name;
Check it out…

mysql> CREATE TABLE more_friends
-> LIKE friends;
Query OK, 0 rows affected (0.39 sec)

That was simple enough. Let’s look at the description of the new more_friends table:

mysql> DESC more_friends;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| friend_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| f_first_name | varchar(30) | YES  |     | NULL    |                |
| f_last_name  | varchar(30) | YES  |     | NULL    |                |
| f_cell_phone | char(12)    | YES  |     | NULL    |                |
| f_age        | int(11)     | YES  |     | NULL    |                |
| f_bday       | date        | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

CREATE TABLE LIKE creates a table like the original table with the same column definitions and any defined attributes.

However, this new table is empty of any data.
To dig deeper, I will get a more detailed description of the original friends table with the SHOW CREATE TABLE syntax as follows,

mysql> SHOW CREATE TABLE friends\G;
*************************** 1. row ***************************
Table: friends
Create Table: CREATE TABLE `friends` (
`friend_id` int(11) NOT NULL AUTO_INCREMENT,
`f_first_name` varchar(30) DEFAULT NULL,
`f_last_name` varchar(30) DEFAULT NULL,
`f_cell_phone` char(12) DEFAULT NULL,
`f_age` int(11) DEFAULT NULL,
`f_bday` date DEFAULT NULL,
PRIMARY KEY (`friend_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

And for comparison, the more_friends table:

mysql> SHOW CREATE TABLE more_friends\G;
*************************** 1. row ***************************
Table: more_friends
Create Table: CREATE TABLE `more_friends` (
`friend_id` int(11) NOT NULL AUTO_INCREMENT,
`f_first_name` varchar(30) DEFAULT NULL,
`f_last_name` varchar(30) DEFAULT NULL,
`f_cell_phone` char(12) DEFAULT NULL,
`f_age` int(11) DEFAULT NULL,
`f_bday` date DEFAULT NULL,
PRIMARY KEY (`friend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The only real difference is the friend_id column value for the friends table is being shown at a count of 31, while the more_friends tables’ is not showing a count per the SHOW CREATE TABLE statements above.
You can additionally confirm the same by selecting the MAX() column value from both tables for the friend_id field as shown below:

mysql> SELECT MAX(friend_id) FROM friends;
+----------------+
| MAX(friend_id) |
+----------------+
|             30 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(friend_id) FROM more_friends;
+----------------+
| MAX(friend_id) |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)

This is due to the more_friends table being empty, where as that same column value for the original friends table has a value 30 for its 30 present records.

Moving forward, when you need a table with the same structure as an existing one, you can cut down on the boilerplate and try using CREATE TABLE LIKE syntax instead.


Takeaways

Some important details about CREATE TABLE LIKE:

  • User must have the SELECT privilege for the original table.
  • Does not work for a VIEW. Base tables only.
  • Does not transfer over any FOREIGN KEY definitions from the original table to the new table.
  • The newly created table is empty of any data.

Coming Up…

In a forthcoming blog post, I will look at CREATE TABLE SELECT syntax for creating copy tables, but with data. Hope to see you there!!!

As always, explore the official MySQL 5.7 Online Manual for further study.


A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.


Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

One thought on “MySQL CREATE TABLE LIKE.

Leave a Reply