Two different solutions for MySQL insertion and processing of duplicate key values

We all know that when a unique column needs to insert records containing duplicate values on a UNIQUE key, an error is reported during the default insert operation, mySQL inserts two different processing methods for processing duplicate key values. Next we will introduce them separately.

Create two test tables and create the 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)  
 
 

Method 1 for duplicate key values during MySQL insertion and processing; REPLACE

If the record contains multiple fields and some fields are not assigned a value during insertion, the newly inserted records are 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 a table, if the inserted Table does not specify a column, it will be expressed as NULL, rather than the original content of the table. If the inserted content column is the same as the inserted Table column, no NULL occurs. 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 the columns in the inserted Table need to be retained during the INSERT operation and only the specified columns are updated, the second method can be used.

Method 2 For MySQL to insert duplicate key values 2. INSERT INTO ON DUPLICATE KEY UPDATE

Duplicate update operations are found. Based on the original record, the specified field content is updated, and other fields are retained. For example, I only want to insert the id and name fields of the test2 table, but keep the type FIELD OF THE test1 table:

 
 

  
  
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 only want to INSERT data that is not in the original table, you can use other MySQL INSERT methods to process duplicate key values.