Several ways to update in MySQL without duplicate inserts

First Solution:

If you specify the on DUPLICATE KEY Update command statement, the duplicate contents of the database record will not be inserted under the unique index or the primary index, but the old records in the database are also updated. For example, a field A is declared as a unique index and contains only records with a value of 1, and the following two statements achieve the same effect:

The code is as follows	Copy Code
INSERT into table (a,b,c) VALUES (1,2,3) On DUPLICATE KEY UPDATE c=c+1; Second, UPDATE table SET c=c+1 WHERE a=1; INSERT into table (a,b,c) VALUES (1,2,3) On DUPLICATE KEY UPDATE c=c+1; Second, UPDATE table SET c=c+1 WHERE a=1;

The

is affected by the a=1 row, and when inserted, the value of C is added by 1.
If field B is unique, the INSERT statement will have the same effect as the following statement:
Update table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
if a=1 or b=2 matches more than one row, only the first row is updated 。 Generally, you should avoid using the on DUPLICATE key clause if there are multiple unique indexes in the table.
You can insert the UPDATE statement inserts ... The VALUES (field name) function is used in UPDATE to associate a row of records. That is, values (field names) can be used in an UPDATE statement to update the value of a field without duplicate keys. This function is especially useful in multiple rows of inserts. But the function VALUES () are only used when the INSERT ... In the UPDATE statement, otherwise NULL is returned. For example,

The code is as follows	Copy Code
INSERT into table (a,b,c) VALUES (1,2,3), (4,5,6) On DUPLICATE KEY UPDATE c=values (a) +values (b); INSERT into table (a,b,c) VALUES (1,2,3), (4,5,6) On DUPLICATE KEY UPDATE c=values (a) +values (b);

This statement has the same effect as the following two:

code is as follows	copy code
INSERT into table (a,b,c) VALUES (1,2,3)      on DUPLICATE KEY UPDATE c=3;   INSERT into table (A,B,C) VALUES (4,5,6)      on DUPLICATE KEY UPDATE c=9;  INSERT into table (A,B,C) VALUES (1,2,3) br>   on DUPLICATE KEY UPDATE c=3; INSERT into table (a,b,c) VALUES (4,5,6)   on DUPLICATE KEY UPDATE c=9;

If the table contains an AutoIncrement field auto_increment, and the INSERT ... Update inserts a row, the function last_insert_id () returns the Auto_increment value, and if the statement updates a row, last_insert_id () makes no sense. However, you can make it meaningful by using last_insert_id (expr), and if the ID field is an AutoIncrement column, the method that makes last_insert_id () meaningful for the UPDATE statement is as follows:

The code is as follows	Copy Code
INSERT into table (a,b,c) VALUES (1,2,3) On DUPLICATE KEY UPDATE id=last_insert_id (id), c=3; INSERT into table (a,b,c) VALUES (1,2,3) On DUPLICATE KEY UPDATE id=last_insert_id (id), c=3;

If you use the on DUPLICATE KEY UPDATE statement, the deferred execution option delayed will be ignored.

The second type of solution

This solution is more general, but personal feeling performance is not very good (no test)
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:

The code is as follows

Copy Code

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); 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 two: inserting a single record

The code is as follows	Copy Code
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); 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 third type of solution

Replace syntax

The syntax format for replace is:

The code is as follows	Copy Code
1. Replace into table_name (col_name, ...) VALUES (...) 2. Replace into table_name (col_name, ...) Select ... 3. Replace into table_name set Col_name=value, ...

Algorithm Description:

The run of replace is similar to insert, but if the old record has the same value as the new record, the old record is deleted before the new record is inserted, namely:

1. Try inserting the new row into the table

2. When an insert fails because of a duplicate keyword error for a primary key or unique keyword:

Delete conflicting rows from a table that contain duplicate key values

Try inserting the new row into the table again

The old record has the same value as the new record: The table has a primary key or a unique index, otherwise, it doesn't make sense to use a replace statement.

The statement is the same as the insert, because no indexes are used to determine whether the new row replicates other rows.

return value:

The Replace statement returns a number that indicates the number of rows affected. This number is the same as the number of rows that were deleted and inserted.

The number of rows affected can easily determine whether replace has added only one row, or if replace has also replaced other rows: check to see if the number is 1 (added) or larger (replace).

Example:

Eg: (phone field is a unique index)

The code is as follows	Copy Code
Replace into table_name (EMAIL,PHONE,USER_ID) VALUES (' test569′, ' 99999′, ' 123′ ')

In addition: You can do this in SQL Server:

code is as follows	copy code
If not Exists (select phone from t where phone= ' 1′) INSERT into t (phone, Update_time) VALUES (' 1′, GETDATE ()) else< /p> Update t set update_time = GETDATE () where phone= ' 1′