MySQL replication table-insert into SELECT

The base table is:

Mysql>Select* fromStaff ;+----+----------+-------+| ID | name | Slary |+----+----------+-------+|3| Haofugui |10000||4| guoming |3500||5| Haotian |2900|+----+----------+-------+3Rowsinch Set(0.00sec) MySQL>describe staff;+-------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+----------------+| ID |int( One)  | NO | PRI | NULL | auto_increment | | name |Char( -) |     YES | |                NULL | || Slary |int( One)  |     YES | |                NULL | |+-------+----------+------+-----+---------+----------------+3Rowsinch Set(0.00Sec

The INSERT INTO SELECT statement copies data from a table, and then inserts the data into an existing table (the target table), and any existing rows in the target table are unaffected.

Statement 1: Insert into Table2 (field1,field2,...) Select value1,value2,... from Table1 where Condition;

Statement 2: Insert into Table2 select * from Table1;

Description: 1) The target table Table2 must exist, MySQL will not be created automatically;

2) Statement 1 is generally used for two table structure of the same situation, and statement 2 is used in the case of inconsistent table structure;

3) The field1,field2 involved in statement 1 must also exist;

4) If Table2 has a primary key and does not have automatic growth, then field1, field2 ... Must include the primary key.

Example:

Mysql>CREATE table staff_bak_1 like staff; Query OK,0Rows Affected (0.05sec) MySQL>describe Staff_bak_1;+-------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+----------------+| ID |int( One)  | NO | PRI | NULL | auto_increment | | name |Char( -) |     YES | |                NULL | || Slary |int( One)  |     YES | |                NULL | |+-------+----------+------+-----+---------+----------------+3Rowsinch Set(0.01sec) MySQL>Select* fromStaff_bak_1; EmptySet(0.00sec) MySQL>INSERT INTO Staff_bak_1 select * fromStaff  ; Query OK,3Rows Affected (0.00sec) Records:3Duplicates:0Warnings:0MySQL>Select* fromStaff_bak_1;+----+----------+-------+| ID | name | Slary |+----+----------+-------+|3| Haofugui |10000||4| guoming |3500||5| Haotian |2900|+----+----------+-------+3Rowsinch Set(0.00Sec

Mysql>CREATE TABLE Staff_bak_2(IDintauto_increment primary KEY,NameChar( -)); Query OK,0Rows Affected (0.05sec) MySQL>describe staff_bak_2;+-------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+----------------+| ID |int( One)  | NO | PRI | NULL | auto_increment | | name |Char( -) |     YES | |                NULL | |+-------+----------+------+-----+---------+----------------+2Rowsinch Set(0.01sec) MySQL>INSERT into Staff_bak_2 (name) select name from the staff where id!=5; Query OK,2Rows Affected (0.00sec) Records:2Duplicates:0Warnings:0MySQL>Select* fromstaff_bak_2;+----+----------+| ID | Name |+----+----------+|1| Haofugui | |2| guoming |+----+----------+2Rowsinch Set(0.00Sec

MySQL replication table-insert into SELECT