MySql avoids repeated insertion of records (ignore, Replace, on duplicate key update), ignoreduplicate
Reference link: http://blog.csdn.net/jbboy/article/details/46828917
[I think it is better to modify the document for study and reference]
This article provides you with three methods to avoid repeated insertion of records in mysql: ignore, Replace, and on duplicate key update.
Case 1: Use the ignore keyword
If you use the primary key primary or the unique index unique to distinguish the uniqueness of a record, you can use the following to avoid repeated insertion of records:
The Code is as follows: |
Copy code |
1 insert ignore into 'table _ name' ('email ', 'phone', 'user _ id') VALUES ('test9 @ 163.com', '123 ', '123 '); |
In this way, when there is a duplicate record, it will be ignored. After execution, the number 0 will be returned.
Another application is to copy the table to avoid repeated records:
The Code is as follows: |
Copy code |
1 insert ignore into 'table _ 1' ('name') SELECT 'name' FROM 'table _ 2 '; |
Solution 2: Use 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 REPLACE operation is similar to the INSERT operation. However, 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 to Insert a new row into the table
When insertion fails due to a duplicate keyword error for the primary key or unique Keyword:
Delete conflicting rows with duplicate keyword values from the table
Try to Insert a new row into the table again
The criteria for determining the values of the old record and the new record are as follows:
The table has a primary key or UNIQUE index. Otherwise, using a REPLACE statement is meaningless. This statement is the same as INSERT, because no index is used to determine whether other rows have been copied in the new row.
Return Value:
The REPLACE statement returns a number to indicate the number of affected rows. This is the sum of the number of deleted and inserted rows.
The number of affected rows can be easily determined whether REPLACE only adds one row, or whether REPLACE also replaces other rows: Check whether the number is 1 (Added) or larger (replaced ).
Example:
# Eg: (the phone field is a unique index)
The Code is as follows: |
Copy code |
Replace into 'table _ name' ('email ', 'phone', 'user _ id') VALUES ('test569', '123', '123 '); |
In addition, SQL Server can handle the problem as follows:
The 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 update 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
Solution 3: ON DUPLICATE KEY UPDATE
As written above, you can also insert ..... The on duplicate key update method is added later. If you specify on duplicate key update and insert a row, DUPLICATE values will appear in a UNIQUE index or primary key, the old row will be updated.
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 a row is inserted as a new record, the value of the affected row is 1. If the original 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 a = 1 OR B = 2 matches multiple rows, only one row is updated. Generally, you should avoid using the on duplicate key clause for tables with multiple unique keywords.
You can use the VALUES (col_name) function from INSERT... The INSERT part of the UPDATE statement references the column value. In other words, if there is no duplicate keyword conflict, the VALUES (col_name) in the UPDATE clause can reference the value of the inserted col_name. This function is particularly applicable to multiline inserts. The VALUES () function is only used in INSERT... The UPDATE statement is meaningful. Otherwise, NULL is returned.
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 serves the same purpose 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 used in my actual project: import data from a table to another table. The data repeatability must be considered (as shown below). The unique 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', 'campaign _ id ') SELECT '', 'table _ name2 '. 'email ', 'table _ name2 '. 'phone', NULL, NULL, 'Pending', 29 FROM 'table _ name2' WHERE 'table _ name2 '. 'status' = 1 On duplicate key update 'table _ name1 '. 'status' = 'Pending' |
Add another example:
The Code is as follows: |
Copy code |
1 insert into 'class' SELECT * FROM 'class1' on duplicate key update 'class'. 'Course' = 'class1'. 'Course' |
Other key: DELAYED is used as a quick insert, and is not very concerned with the invalidation and improves the insert performance.
IGNORE only follows that the record corresponding to the primary key does not exist. If no record exists, the record is added. If yes, the record is ignored.
For more information, see: http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert
Note: in MYSQL, the UNIQUE index will invalidate the null field, that is, (create a UNIQUE index on field ):
The Code is as follows: |
Copy code |
1 insert into 'test' ('A') VALUES (NULL ); |
Yes, it can be inserted repeatedly (the same is true for the union of unique indexes ).