MySql several methods to avoid repeated insert records _ MySQL

Several methods for MySql to avoid repeated inserts of records: bitsCN.com

Solution 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:

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:

Insert ignore into 'Table _ 1' ('name') SELECT 'name' FROM 'Table _ 2 ';

Solution 2: Use Replace

Syntax format:

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)

Replace into 'Table _ name' ('email ', 'phone', 'User _ id') VALUES ('test569', '123', '123 ');

In addition, SQL Server can handle the problem as follows:

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

For example‍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:

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:

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.

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:

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:

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:

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 ):

Insert into 'test' ('A') VALUES (NULL );

Yes, it can be inserted repeatedly (The same is true for the Union of unique indexes ).

BitsCN.com