SQL

Cloning a MySQL table with data and indexes


Problem

While you've been developing a system using MySQL you probably had situations where you needed a copy of an existing table to test a few things. Here's how!



Solution

Let's assume we've got a table called test. That table contains data and also has some indexes. We an quickly clone the data and its data using this:

CREATE TABLE new_test AS SELECT * FROM test

If you have a close look at the duplicated table, you'll notice, that all indexes are missing. MySQL can't clone those in just a single query. We'll need these two to create a proper clone of our table:

CREATE TABLE new_test LIKE test;
INSERT INTO new_test SELECT * FROM test;

Have fun creating clones!

Comments




Please sign-in to post a comment