MYSQL insert several methods for processing duplicate key values

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)
1. 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 |
| 1, 202 | bbb | NULL |
| 1, 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.
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> 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 only want to INSERT data that is not in the original table, you can use the third method.
3. IGNORE
Determine whether or not there is, there is no insert, otherwise insert. It is easy to understand that when inserting, MySQL will not try to execute this statement because it violates the uniqueness constraint. 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)