Tutorial on operating clone tables in MySQL and tutorial on cloning tables in mysql

Tutorial on operating clone tables in MySQL and tutorial on cloning tables in mysql

There may be a situation where a completely identical copy table create table... SELECT is not suitable, because the copy must contain the same index, the default value, and so on.

Follow the steps below to handle this situation.

• Use show create table to obtain the structure and index of the source TABLE specified in the create table statement.
• Modify the statement to change the clone table of the table name and execute the statement. In this way, the exact clone table exists.
• Alternatively, you can use the insert into... SELECT statement to copy the table content.

Instance:

Try the following example to create a clone table tutorials_tbl
Step 1:

Obtain the complete structure of a table

mysql> SHOW CREATE TABLE tutorials_tbl \G;*************************** 1. row ***************************    Table: tutorials_tblCreate 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=InnoDB1 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=InnoDB;Query OK, 0 rows affected (1.80 sec)

Step 3:

In the clone database table in step 2. If you want to copy data from the old table, you can use 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

Finally, the exact cloned table is displayed.