MySQL Avoid duplicate insertion record method (Ignore,replace,on DUPLICATE KEY UPDATE)

Case one: Use the Ignore keyword

If the uniqueness of the record is distinguished by the primary key primary or unique index unique, it is possible to avoid duplicate insertion records:

The code is as follows	Copy Code
1 INSERT IGNORE into ' table_name ' (' email ', ' phone ', ' user_id ') VALUES (' test9@163.com ', ' 99999 ', ' 9999 ');

This will be ignored when duplicate records are executed, and the number 0 is returned after execution.

Another application is to copy the table and avoid duplicate records:

The code is as follows	Copy Code
1 INSERT IGNORE into ' table_1 ' (' name ') SELECT ' name ' from ' table_2 ';

Programme II: Using replace

Syntax format:

The code is as follows	Copy Code
REPLACE into ' table_name ' (' col_name ', ...) VALUES (...); REPLACE into ' table_name ' (' col_name ', ...) SELECT ...; 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:

Try inserting a new row into the table
When an insert fails because of a duplicate keyword error for a primary key or a 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 is meaningless 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. The number of rows that are deleted and inserted, and the
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, this can be done in SQL Server:

The code is as follows	Copy Code
If not EXISTS (select phone from t where phone= ' 1 ') inserts into T (phone, Update_time) VALUES (' 1 ', GETDATE ()) Else up Date T Set update_time = GETDATE () where phone= ' 1 '

For more information please see: http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#replace

Scenario Three: on DUPLICATE KEY UPDATE

As written above, you can also add the on DUPLICATE KEY Update method after insert INTO ... 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:

The code is as follows	Copy Code
INSERT into ' table ' (' A ', ' B ', ' C ') VALUES (1, 2, 3) on DUPLICATE KEY UPDATE ' c ' = ' C ' +1; 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:

The code is as follows	Copy Code
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.

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 ');

This statement acts the same as the following two statements:

The 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;

Note: When you use on DUPLICATE KEY update, the delayed option is ignored.

Example:
This example is what I used in the actual project: to import the data from one table into another, the repeatability of the data should be considered (as follows), the only index is: email:

The code is as follows	Copy Code
INSERT into ' table_name1 ' (' title ', ' first_name ', ' last_name ', ' email ', ' phone ', ' user_id ', ' role_id ', ' status ', ' Campaig n_id ') SELECT ', ', ', ', ' table_name2 '. ' Email ', ' table_name2 '. ' phone ', null, NULL, ' pending ', ' table_name2 ' WHERE ' table_name2 '. ' Status ' = 1 On DUPLICATE KEY UPDATE ' table_name1 '. ' Status ' = ' pending '

Put another example:

The code is as follows	Copy Code
1 INSERT into ' class ' SELECT * "Class1 ' on DUPLICATE KEY UPDATE ' class '. ' Course ' = ' class1 '. ' Course '

Other key: Delayed as a quick insert, not very concerned about the failure, improve the insertion performance.
IGNORE only focus on primary key corresponding record is not present, added without, and ignored.

For more information please see: Http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert

Special note: In MySQL, the unique index will invalidate null fields, that is to say (a unique index on a field):

The code is as follows	Copy Code
1 INSERT into ' test ' (' a ') VALUES (NULL);

can be inserted repeatedly (same as Union unique index).