MySQL replication table

MySQL copy table

If we need to completely copy the MySQL data table, including the table structure, indexes, and default values wait. If you just use the CREATE TABLE ... SELECT command, it cannot be achieved.

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

• Use the SHOW CREATE TABLE command to get the created data table. (CREATE TABLE) statement, which contains the structure, index, etc. of the original data table.

• Copy the SQL statement displayed by the following command, modify the data table name, and execute the SQL statement, through the above command The data table structure will be completely copied.

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

Example

Try the following example to copy the table php_tbl.

Step 1:

Get the complete structure of the data table.

mysql> SHOW CREATE TABLE php_tbl \G;
*************************** 1. row ***************************
       Table: php_tbl
Create Table: CREATE TABLE `php_tbl` (
  `php_id` int(11) NOT NULL auto_increment,
  `php_title` varchar(100) NOT NULL default '',
  `php_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`php_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`php_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` (
  -> `php_id` int(11) NOT NULL auto_increment,
  -> `php_title` varchar(100) NOT NULL default '',
  -> `php_author` varchar(40) NOT NULL default '',
  -> `submission_date` date default NULL,
  -> PRIMARY KEY  (`php_id`),
  -> UNIQUE KEY `AUTHOR_INDEX` (`php_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 in the data table, you can use the INSERT INTO... SELECT statement.

mysql> INSERT INTO clone_tbl (php_id,
    ->                        php_title,
    ->                        php_author,
    ->                        submission_date)
    -> SELECT php_id,php_title,
    ->        php_author,submission_date
    -> FROM php_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0