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,

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…

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

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,

And for comparison, the more_friends table:

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:

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 LIKEfrom 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.