MySQL inserts several ways to handle duplicate key values

When the unique column inserts a record with duplicate values on a unique key, the default insert will report a 1062 error, and MySQL has three different processing methods, as described below.

Create a unique constraint on the ID column by creating 2 test tables.
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
If the record has multiple fields and if there are no assigned values when inserting, the newly inserted records will be empty.
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)

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. If the inserted content column is the same as the table column being inserted, no null appears. 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 when inserting, you can use the second method. Http://www.pprar.com/f01/index_23_1.html

2. INSERT into on DUPLICATE KEY UPDATE
The update operation was found to be duplicates. On the basis of the original record, the contents of the specified field are updated and the contents of other fields are preserved. 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 from 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 is not in the original table, you can use the third method.

3. IGNORE into
The judgment is present, there is no insertion, otherwise inserted. It is easy to understand that when inserting a violation of a uniqueness constraint, MySQL does not attempt to execute the statement. For example:
mysql> Insert Ignore to 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)

MySQL inserts several ways to handle duplicate key values