MySQL copies the table

MySQL copies the table

If we need to fully copy MySQL data tables, including table structures, indexes, default values, and so on. I f you only use CREATE TABLE ... SELECT command, which is not possible.

This section will show you how to copy the MySQL data sheet in its entiret, as follows:

• Use the SHOW CREATE TABLE command to get the Create Data Table (CREATE TABLE) statement, which contains the structure, index, and so on of the original data table.
• Copy the SQL statement displayed by the following command, modify the data table name, and execute the SQL statement, which will fully copy the data table structure through the above command.
• If you want to copy the contents of the table, you can use INSERT INTO ... SELECT statement to implement.

Instance

Try the following instance to copy the w3cschool_tbl.

Step 1:

Gets the full structure of the data table.

mysql> SHOW CREATE TABLE w3cschool_tbl \G;
*************************** 1. row ***************************
       Table: w3cschool_tbl
Create Table: CREATE TABLE `w3cschool_tbl` (
  `w3cschool_id` int(11) NOT NULL auto_increment,
  `w3cschool_title` varchar(100) NOT NULL default '',
  `w3cschool_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`w3cschool_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`w3cschool_author`)
) ENGINE=InnoDB
1 row in set (0.00 sec)

ERROR:
No query specified

Step 2:

Modify the data table name of the SQL statement and execute the SQL statement.

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

Step 3:

After you complete the second step, you will create a new clone table in the database clone_tbl. I f you want to copy data from a data sheet you can use INSERT INTO... SELECT statement to implement.

mysql> INSERT INTO clone_tbl (w3cschool_id,
    ->                        w3cschool_title,
    ->                        w3cschool_author,
    ->                        submission_date)
    -> SELECT w3cschool_id,w3cschool_title,
    ->        w3cschool_author,submission_date
    -> FROM w3cschool_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

After you perform the above steps, you will copy the table in its entiret, including the table structure and table data.