Mysql inserts several methods for processing duplicate key values _mysql

Set up 2 test tables and create a unique constraint on the ID column.
Mysql> CREATE TABLE test1 (ID int,name varchar (5), type int,primary key (ID));
Query OK, 0 rows affected (0.01 sec)
Mysql> CREATE TABLE test2 (ID int,name varchar (5), type int,primary key (ID));
Query OK, 0 rows affected (0.01 sec)
Mysql> select * from Test1;
+-----+------+------+
| ID | name | Type |
+-----+------+------+
| 101 |    AAA | 1 |
| 102 |    BBB | 2 |
| 103 |    CCC | 3 |
+-----+------+------+
3 Rows in Set (0.00 sec)
Mysql> select * from Test2;
+-----+------+------+
| ID | name | Type |
+-----+------+------+
| 201 |    AAA | 1 |
| 202 |    BBB | 2 |
| 203 |    CCC | 3 |
| 101 |    xxx | 5 |
+-----+------+------+
4 rows in Set (0.00 sec)
1, REPLACE into
The duplicate deletion and insertion is found, and if there are multiple fields in the record, the newly inserted records will be empty if there are no assigned fields at the time of insertion.
Mysql> Replace into Test1 (id,name) (select Id,name from Test2);
Query OK, 5 rows affected (0.04 sec)
Records:4 duplicates:1 warnings:0
Mysql> select * from Test1;
+-----+------+------+
| ID | name | Type |
+-----+------+------+
| 101 | xxx | NULL |
| 102 |    BBB | 2 |
| 103 |    CCC | 3 |
| 201 | AAA | NULL |
| 202 | BBB | NULL |
| 203 | CCC | NULL |
+-----+------+------+
6 rows in Set (0.00 sec)
It is important to note that when you replace, if the inserted table does not specify a column, it is represented by null instead of the original contents of the table. Null is not present if the inserted content column is the same as the table column being inserted. For example
Mysql> Replace into Test1 (id,name,type) (select Id,name,type from Test2);
Query OK, 8 rows affected (0.04 sec)
Records:4 duplicates:4 warnings:0
Mysql> select * from Test1;
+-----+------+------+
| ID | name | Type |
+-----+------+------+
| 101 |    xxx | 5 |
| 102 |    BBB | 2 |
| 103 |    CCC | 3 |
| 201 |    AAA | 1 |
| 202 |    BBB | 2 |
| 203 |    CCC | 3 |
+-----+------+------+
6 rows in Set (0.00 sec)
If you want to keep the columns of the inserted table and update only the specified columns, you can use the second method if you insert.
2, INSERT into on DUPLICATE KEY UPDATE
The update operation was found to be duplicate. On the basis of the original record, the specified field contents are updated and the other field contents are retained. For example, I just want to insert the Id,name field of the Test2 table, but keep the Type field of the Test1 table:
mysql> INSERT INTO Test1 (id,name,type) (select Id,name,type to Test2) on DUPLICATE KEY UPDATE test1.name=test2.name;
Query OK, 5 rows affected (0.04 sec)
Records:4 duplicates:1 warnings:0
Mysql> select * from Test1;
+-----+------+------+
| ID | name | Type |
+-----+------+------+
| 101 |    xxx | 1 |
| 102 |    BBB | 2 |
| 103 |    CCC | 3 |
| 203 |    CCC | 3 |
| 202 |    BBB | 2 |
| 201 |    AAA | 1 |
+-----+------+------+
6 rows in Set (0.00 sec)
If you want to insert only the data that the original table does not have, you can use the third method.
3, IGNORE into
To determine whether there is, is not inserted, otherwise inserted. It is easy to understand that MySQL does not attempt to execute this statement when inserting a violation of uniqueness constraints. For example:
mysql> Insert Ignore into test1 (Id,name,type) (select Id,name,type from Test2);
Query OK, 3 rows affected (0.01 sec)
Records:4 duplicates:1 warnings:0
Mysql> select * from Test1;
+-----+------+------+
| ID | name | Type |
+-----+------+------+
| 101 |    AAA | 1 |
| 102 |    BBB | 2 |
| 103 |    CCC | 3 |
| 203 |    CCC | 3 |
| 202 |    BBB | 2 |
| 201 |    AAA | 1 |
+-----+------+------+
6 rows in Set (0.00 sec)