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,
+--------------------+
| 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…
-> 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:
+--------------+-------------+------+-----+---------+----------------+
| 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,
*************************** 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:
*************************** 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:
+----------------+
| 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
from the documentation (link below):
- 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.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
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.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
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). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. 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.”