If we need to completely copy the MySQL data table, including the table structure, index, default value, etc. If you only use the CREATE TABLE … SELECT Commands cannot be realized.
This section will show you how to copy the MySQL data table completely. The steps are as follows:
Use SHOW CREATE TABLE Command to get the create data table ( CREATE TABLE Statement, which contains the structure of the original data table, index, and so on.
Copy the SQL statement shown in the following command, modify the datasheet name, and execute the SQL statement, which will completely copy the datasheet structure.
If you want to copy the contents of the table, you can use the INSERT INTO … SELECT Statement to implement.
Example ¶
Try the following example to copy the table runoob_tbl.
步骤一：
Gets the complete structure of the data table.
mysql> SHOW CREATE TABLE runoob_tbl \G;
*************************** 1. row ***************************
Table: runoob_tbl
Create Table: CREATE TABLE `runoob_tbl` (
`runoob_id` int(11) NOT NULL auto_increment,
`runoob_title` varchar(100) NOT NULL default '',
`runoob_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`runoob_id`),
UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB
1 row in set (0.00 sec)
ERROR:
No query specified
步骤二：
Modify the data table name of the SQL statement and execute the SQL statement.
mysql> CREATE TABLE `clone_tbl` (
-> `runoob_id` int(11) NOT NULL auto_increment,
-> `runoob_title` varchar(100) NOT NULL default '',
-> `runoob_author` varchar(40) NOT NULL default '',
-> `submission_date` date default NULL,
-> PRIMARY KEY (`runoob_id`),
-> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.80 sec)
步骤三：
After performing the second step, you will create a new clone table clone_tbl in the database. If you want to copy the data from the data table, you can use the INSERT INTO… SELECT Statement to implement.
mysql> INSERT INTO clone_tbl (runoob_id,
-> runoob_title,
-> runoob_author,
-> submission_date)
-> SELECT runoob_id,runoob_title,
-> runoob_author,submission_date
-> FROM runoob_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
After performing the above steps, the contents of the table are fully copied, including the table structure and table data.