How do we make a MySQL clone table?

Sometimes there may be a situation where we need an exact copy of a table and CREATE TABLE ... SELECT is not suitable for us purpose, since the copy must contain the same indexes, default values, etc.

You can follow the steps below to handle this situation and create a clone of the table as shown below-

• Use SHOW CREATE TABLE to get the CREATE TABLE statement, which specifies the structure of the source table , index, etc.
• Modify the statement, change the table name to the table name of the clone table, and then execute the statement. This way we will get an exact clone table.
• Alternatively, if we also need to copy the table contents, we can also issue an INSERT INTO ... SELECT statement.

Example

In the following example, we will create a clone table for tutorials_tbl.

Step 1

Get the complete structure of the table.

mysql> SHOW CREATE TABLE tutorials_tbl \G;

*************************** 1. row ***************************
Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
   `tutorial_id` int(11) NOT NULL auto_increment,
   `tutorial_title` varchar(100) NOT NULL default '',
   `tutorial_author` varchar(40) NOT NULL default '',
   `submission_date` date default NULL,
   PRIMARY KEY (`tutorial_id`),
   UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) TYPE = MyISAM
1 row in set (0.00 sec)
ERROR:
No query specified

Step 2

Rename the table and create another table.

mysql> CREATE TABLE clone_tbl (
    -> tutorial_id int(11) NOT NULL auto_increment,
    -> tutorial_title varchar(100) NOT NULL default '',
    -> tutorial_author varchar(40) NOT NULL default '',
    -> submission_date date default NULL,
    -> PRIMARY KEY (tutorial_id),
    -> UNIQUE KEY AUTHOR_INDEX (tutorial_author)
    -> ) TYPE = MyISAM;
Query OK, 0 rows affected (1.80 sec)

Step 3

After performing step 2, you will create a clone table in the database. If you want to copy data from an old table, you can do so using the INSERT INTO...SELECT statement.

mysql> INSERT INTO clone_tbl (tutorial_id, tutorial_title, tutorial_author, submission_date)
    -> SELECT tutorial_id,tutorial_title,
    ->    tutorial_author,submission_date
    -> FROM tutorials_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0

In the end, we will have a clone table that is exactly what you want.

The above is the detailed content of How do we make a MySQL clone table?. For more information, please follow other related articles on the PHP Chinese website!