Posted on 8 Comments

Copying a table in MySQL

This question often comes up, and the general answer given appears to be “CREATE TABLE … SELECT …”
But actually, that does not do what you might expect, as this statement creates a table structure based on the resultset of the select, so the column types may differ from your original table, and the table will not have indexes either.
The syntax does allow you to add and override pretty much everything, but since we were talking about copying, let’s look at another way:

CREATE TABLE bar LIKE foo;
INSERT INTO bar SELECT * FROM foo;

This produces an exact copy of the original table, both structure and data, indexes and everything.
No, you can’t combine these two into a single statement. Sorry 😉

Posted on 8 Comments

8 thoughts on “Copying a table in MySQL

  1. Take a look at this 😉

    http://anothersysadmin.wordpress.com/2008/01/17/create-a-copy-of-a-table-with-mysql/

    basically:
    mysql> CREATE TABLE new_destination_table SELECT * FROM source_table;

  2. Foreign key constraints aren’t copied. Obtaining the definition from SHOW CREATE TABLE would seem to be the only foolproof way.

    Tested 5.0.45

  3. Sorry, but that blog is wrong. This was the whole point of my post; see the first paragraph where I explain what CREATE … SELECT actually does.

  4. Did you file that as a bug? What’s the bug#?

  5. You can put the table definition inline in the CREATE … SELECT.

    CREATE TABLE FOO (
    … any legal table definition here …
    )
    AS SELECT … any legal SELECT statement here;

  6. So how/when/where do you get the legal table definition?
    You’ll need to run a SHOW CREATE TABLE …
    That’s still 2 statements total?

  7. Sorry, I didn’t read carefully :/ I’ll update that post… thanks for the hint!

  8. using this lua script http://forge.mysql.com/tools/tool.php?id=136 you could use only one statement like:

    CREATE FULL TABLE bar LIKE SELECT * FROM foo;

Comments are closed.