Entrepreneur Geek

Here’s a great thing I learned about MySQL today:

[sql]CREATE TABLE newtable LIKE oldtable[/sql]

Will create a copy of the table along with structure and keys (primary keys, auto increments etc) but not the data.

This is perfect solution to create new tables based on an existing one – we use it to create a new database for every customer that signs up on a project. We go in a loop of the list of tables, and use the CREATE TABLE statement to duplicate the table in a new name. This works across databases too (assuming you have permissions).

Of course, if you wanted to get all the data, you can do something like:
[sql]CREATE TABLE newtable LIKE oldtable;
INSERT INTO newtable SELECT * FROM oldtable;[/sql]

The full CREATE TABLE syntax is here.