Several ways to avoid repeated insertions of records by MySQL

This article to provide you with three kinds of in MySQL to avoid the repeated insertion record method, mainly refers to the Ignore,replace,on DUPLICATE key update three methods, the need for friends can refer to

Scenario One: Using the Ignore keyword

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

Copy CodeThe code is as follows:
INSERT IGNORE into ' table_name ' (' email ', ' phone ', ' user_id ') VALUES (' [email protected] ', ' 99999 ', ' 9999 ');

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

Another application is to copy tables to avoid duplicate records:

Copy CodeThe code is as follows:
INSERT IGNORE into ' table_1 ' (' name ') SELECT ' name ' from ' table_2 ';

Scenario Two: Using replace

Syntax format:

Copy CodeThe code is as follows:
REPLACE into ' table_name ' (' col_name ', ...) VALUES (...);
REPLACE into ' table_name ' (' col_name ', ...) SELECT ...;
REPLACE into ' table_name ' SET ' col_name ' = ' value ',

... Algorithm Description:
Replace runs much like an 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, that is:

Try inserting a new row into the table
When an insert failure occurs because of a duplicate keyword error for a primary key or unique keyword:
Delete conflicting rows with duplicate key values from the table
Try inserting a new row into the table again
The criteria by which the old record has the same value as the new record is:
The table has a primary key or unique index, otherwise, using a replace statement has no meaning. The statement is the same as insert, because no index is used to determine whether the new row has replicated other rows.

return value:
The Replace statement returns a number that indicates the number of rows affected. The number is the number of rows that are deleted and inserted, and
The number of rows affected can easily determine if replace adds only one row, or if replace replaces other rows: Check whether the number is 1 (added) or larger (replace).

Example:
# Eg: (phone field is a unique index)

Copy CodeThe code is as follows:
REPLACE into ' table_name ' (' email ', ' phone ', ' user_id ') VALUES (' test569 ', ' 99999 ', ' 123 ');

In addition, this can be done in SQL Server:

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

For more information, 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 an on DUPLICATE KEY update and the insert row causes duplicate values to appear in a unique index or primary KEY, the old line UPDATE is performed.

For example, if column A is defined as unique and contains a value of 1, the following two statements have the same effect:

Copy CodeThe code is as follows:
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 original record is updated, the value of the affected row is 2.

NOTE: If column B is also the only column, the insert is equivalent to this UPDATE statement:

Copy CodeThe code is as follows:
UPDATE ' table ' SET ' c ' = ' C ' +1 WHERE ' a ' =1 OR ' B ' =2 LIMIT 1;

If A=1 OR b=2 matches multiple rows, only one row is updated. In general, you should try to avoid using the on DUPLICATE key clause on a table with multiple unique keywords.

You can use the values (col_name) function from the Insert ... in the UPDATE clause. The insert portion of the UPDATE statement refers to the column value. In other words, if a duplicate keyword conflict does not occur, values (col_name) in the update clause can refer to the value of the col_name being inserted. This function is especially useful for multi-row insertions. The VALUES () function is only in the insert ... The UPDATE statement makes sense, and returns null at other times.

Copy CodeThe code is as follows:
INSERT into ' table ' (' A ', ' B ', ' C ') VALUES (1, 2, 3), (4, 5, 6) on DUPLICATE KEY UPDATE ' C ' =values (' a ') +values (' B ');

This statement works the same as the following two statements:

Copy CodeThe code is as follows:
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: The delayed option is ignored when you use the on DUPLICATE KEY update.

Example:
This example is what I used in the actual project: to import data from one table into another, the repeatability of the data has to be considered (see below), and the unique index is: email:

Copy CodeThe code is as follows:
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 ', from ' table_name2 '
WHERE ' table_name2 '. ' Status ' = 1
On DUPLICATE KEY UPDATE ' table_name1 '. ' Status ' = ' pending '

One more example:

Copy CodeThe code is as follows:
INSERT into ' class ' SELECT * from ' 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 the primary key corresponding record is not present, none is added, there is ignored.

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

Special note: In MySQL, the unique index will invalidate the null field, i.e. (a unique index on the A field):

Copy CodeThe code is as follows:
INSERT into ' test ' (' a ') VALUES (NULL);

can be repeatedly inserted (the same as the Federated Unique Index).

Several ways to avoid repeated insertions of records by MySQL