MySQL Copy Table

If we need to completely copy the MySQL data table, including the structure, index, default values, etc. If you just useCREATE TABLE ... SELECT command cannot be implemented.

This chapter will introduce how to completely copy the MySQL data table, the steps are as follows:

• using SHOW CREATE TABLE command to obtain the creation of the data table (CREATE TABLE) statement, which includes the structure, index, and other information of the original data table.

• Copy the SQL statements displayed below, modify the table name, and execute the SQL statement, through the above command to completely copy the data table structure.

• If you want to copy the content of the table, you can use INSERT INTO ... SELECT statement to achieve this.

Online example

Try the following example to copy table w3codebox_tbl .

Step 1:

Get the complete structure of the data table.

mysql> SHOW CREATE TABLE `w3codebox_tbl \G;
***************************　1. row　***************************
　　　　　　　Table: `w3codebox_tbl
Create Table: CREATE TABLE `w3codebox_tbl` (
　　`w3codebox_id` int(11) NOT NULL auto_increment,
　　`w3codebox_title` varchar(100) NOT NULL default '',
　　`w3codebox_author` varchar(40) NOT NULL default '',
　　`submission_date` date default NULL,
　　PRIMARY KEY (`w3codebox_id`),
　　UNIQUE KEY `AUTHOR_INDEX` (`w3codebox_author()`
) ENGINE=InnoDB　
1　row in set (0.00 sec)
ERROR:
No query specified

Step 2:

Modify the SQL statement to change the table name and execute the SQL statement.

mysql> CREATE TABLE `clone_tbl` (
　　-> `w3codebox_id` int(11) NOT NULL auto_increment,
　　-> `w3codebox_title` varchar(100) NOT NULL default '',
　　-> `w3codebox_author` varchar(40) NOT NULL default '',
　　-> `submission_date` date default NULL,
　　-> PRIMARY KEY (`w3codebox_id`),
　　-> UNIQUE KEY `AUTHOR_INDEX` (`w3codebox_author()`
-> ENGINE=InnoDB;
Query OK, 0 rows affected (1.80 sec)

Step 3:

After completing the second step, you will create a new clone table clone_tbl in the database. If you want to copy the data of the data table, you can use INSERT INTO... SELECT statement to achieve this.

mysql> INSERT INTO clone_tbl (w3> codebox_id,
　　　　-> w3> codebox_title,
　　　　-> w3> codebox_author,
　　　　-> submission_date)
　　　　-> SELECT w3> codebox_id,w3> codebox_title,
　　　　-> w3> codebox_author,submission_date
　　　　-> FROM w3codebox_tbl;
Query OK,　3　rows affected (0.07　sec)
Records:　3　　Duplicates: 0    Warnings: 0

After executing the above steps, the entire content of the table will be copied, including the table structure and data.