Copy an existing MySQL table to a new table

This is a great set of two commands that allow the creation and population of a new table with the structure and data of an existing table. This provides a quick means of making a point-in-time copy of a table and is a safe, easy way to make a quick copy of a table for testing an application in development on live data without risking a production environment.

To make a copy of the table mytable which is in a different database called production into a new table called mytable_new in the currently selected database, use these two commands:

CREATE TABLE mytable_new LIKE production.mytable;
INSERT mytable_new SELECT * FROM production.mytable;

The first command creates the new table mytable_new by duplicating the structure of the existing table. The second command copies the data from old to new.

The nomenclature production.mytable is a means of specifying the database and table in the same way that a file can be specified by its directory path. It is optional. If production was left off, MySQL would assume that the mytable table was also in the currently selected database.

Αφήστε μια απάντηση

Η ηλ. διεύθυνσή σας δεν δημοσιεύεται. Τα υποχρεωτικά πεδία σημειώνονται με *