MySQL Avoid duplicate insertion record method summary

MySQL inserts when record does not exist, update when record exists
There are basically three kinds of solutions on the Internet.

First type:

Example one: inserting more than one record

Suppose you have a clients table with a primary key of client_id, you can use the following statement:

INSERT into clients
(client_id, Client_name, Client_type)
SELECT supplier_id, Supplier_name, ' advertising '
From suppliers
WHERE NOT EXISTS (SELECT * from clients
where clients.client_id = suppliers.supplier_id);
Example one: inserting a single record

INSERT into clients
(client_id, Client_name, Client_type)
SELECT 10345, ' IBM ', ' advertising '
From dual
WHERE NOT EXISTS (SELECT * from clients
where clients.client_id = 10345);
Using dual as a table name allows you to follow directly to the value of the field you want to insert after the SELECT statement, even if the values do not yet exist in the current table.

The second type:

Use of on DUPLICATE KEY update in INSERT

If you specify on DUPLICATE key update and the insert row causes duplicate values to occur in a unique index or primary key, the old row update is executed. For example, if column A is defined as unique and contains a value of 1, the following two statements have the same effect:mysql> INSERT into table (a,b,c) VALUES (1,2,3)
-> on DUPLICATE KEY UPDATE c=c+1;

mysql> UPDATE table SET c=c+1 WHERE a=1;
If the row is inserted as a new record, the value of the affected row is 1, and if the existing record is updated, the value of the affected row is 2.
NOTE: If column B is also a unique column, insert is equivalent to this UPDATE statement:
mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
If the a=1 OR b=2 matches more than one row, only one row is updated. In general, you should try to avoid using the on DUPLICATE key clause on tables with multiple unique keywords.
You can use the values (col_name) function in the UPDATE clause from the INSERT ... The insert portion of the UPDATE statement references the column value. In other words, if no duplicate keyword conflict occurs, values (col_name) in the update clause can reference the value of the inserted col_name. This function is especially useful for multiple rows of inserts. The VALUES () function is only in the insert ... is meaningful in the UPDATE statement and returns null at other times.
Example:
mysql> INSERT into table (a,b,c) VALUES (1,2,3), (4,5,6)
-> on DUPLICATE KEY UPDATE c=values (a) +values (b);
This statement acts the same as the following two statements:
mysql> INSERT into table (a,b,c) VALUES (1,2,3)
-> on DUPLICATE KEY UPDATE c=3;
mysql> INSERT into table (a,b,c) VALUES (4,5,6)
-> on DUPLICATE KEY UPDATE c=9;
When you use on DUPLICATE KEY update, the delayed option is ignored.

The third type:
Replace statement

This may be the case when we use the database. If a table establishes a unique index on a field, when we insert a record into the table using the existing key value, it throws a primary key conflict error. Of course, we might want to overwrite the original record value with the value of the new record. If you use a traditional approach, you must first delete the original record by using the DELETE statement, and then insert the new record using insert. And in MySQL for us to provide a new solution, this is the replace statement. When you insert a record with replace, if you do not repeat, replace is the same as the Insert feature, and if there are duplicate records, replace replaces the original record value with the value of the new record.

The biggest advantage of using replace is that delete and insert can be combined to form an atomic operation. This eliminates the need to consider complex operations such as adding transactions while using both delete and insert.

When you use Replace, the table must have a unique index, and the field in which the index is located cannot allow null values, otherwise replace will be exactly the same as the insert.

After the replace is executed, the system returns the number of rows affected, and if you return 1, there is no duplicate record in the table, and if you return 2, there is a duplicate record, and the system automatically calls delete to delete the record, and then records the insertion with insert. If the returned value is greater than 2, then there are multiple unique indexes, and multiple records are deleted and inserted.

The syntax for replace is very similar to insert, as the following replace statement inserts or updates a record.

REPLACE into Users (id,name,age) VALUES (123, ' Zhao Benshan ', 50);
　
To insert more than one record:

REPLACE into the users (ID, name, age)
VALUES (123, ' Zhao Benshan ', M), (134, ' Mary ', 15);

Replace can also use the SET statement

REPLACE into users SET ID = 123, name = ' Zhao Benshan ', age = 50;

The above mentioned that replace might affect more than 3 records because there are more than one unique index in the table. In this case, replace takes into account each unique index, deletes the duplicate records for each index, and inserts the new record. Suppose you have a Table1 table with 3 fields A, B, C. They all have a unique index.

CREATE TABLE table1 (a int not null UNIQUE,B int isn't null UNIQUE,C int not null UNIQUE);

Let's say there are 3 records in Table1.

A b C
1 1 1
2 2 2
3 3 3

Below we use the Replace statement to insert a record into the Table1.

REPLACE into Table1 (A, B, c) VALUES (1,2,3);

The results returned are as follows

Query OK, 4 rows Affected (0.00 sec)

The records in the Table1 are as follows

A b C
1 2 3

We can see that replace deletes the original 3 records and then inserts (1, 2, 3).
Summary: Although there is no specific test, feel the first one of the most cost resources (just feel), but if you do not have the primary key can only use him. The second and third difference is: 1 Insert is first attempted insert, if the primary key exists is updated. Replace is the first attempt to insert, if the primary key exists then delete the original record and then insert. 2 If there is more than one unique keyword conflict (different keyword conflicts occur in different records), for example, now there are 2 fields, 2 records are conflicting (no record conflicts with a field), then insert is the first one to update after selecting the sort, replace to delete the two records, and then insert the new record. I have a small point of view, if there is a mistake, please correct me.