MySql several ways to avoid the repeated insertion of records

Scenario 1: use the ignore keyword

If the primary key primary or the one-only index unique are used to distinguish the record from the one-only property, the following can be used to avoid repeated insertion of records:

 INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test9@163.com', '99999', '9999'); 
 

This will be ignored if there are duplicate records, and the number 0 will be returned after execution

Another application is copying tables to avoid duplicating records:

 INSERT IGNORE INTO `table_1` (`name`) SELECT `name` FROM `table_2`; 
 

Scenario 2: use Replace

Grammatical 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:
REPLACE works much like 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, i.e. :

Try inserting a new row into the table
When insertion fails due to a duplicate keyword error for the primary key or only 1 keyword:
Removes conflicting rows from the table that contain duplicate keyword values
Try again to insert the new row into the table
The old record and the new record have the same value according to the following criteria:
The table has an PRIMARY KEY or UNIQUE index; otherwise, it makes no sense to use an REPLACE statement. This statement will be the same as INSERT, because no index is used to determine whether the new row copies other rows.

The return value:
The REPLACE statement returns a number indicating the number of affected rows. This number is the sum of the number of rows deleted and inserted
The number of affected rows can be easily determined if REPLACE has only added 1 row, or if REPLACE has also replaced other rows: check to see if the number is 1 (add) or larger (replace).

Example:
# eg:(the phone field is a 1-only index)

REPLACE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test569', '99999', '123');

In addition, in SQL Server, it can be handled 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

Option 3: ON DUPLICATE KEY UPDATE

Such as the & # 8205; As written above, you can also check out INSERT INTO... . The ON DUPLICATE KEY UPDATE method is added. If you specify ON DUPLICATE KEY UPDATE and inserting a row results in a duplicate value in either an UNIQUE index or PRIMARY KEY, the old row UPDATE is executed.

For example, if the column a is defined as UNIQUE and contains the value 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 the 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 the only 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 row directions, only 1 row is updated. In general, you should try to avoid using the ON DUPLICATE KEY clause for tables with multiple 1-only keywords.

You can use the VALUES(col_name) function in the UPDATE clause from the INSERT... The INSERT part of the UPDATE statement references the column values. In other words, VALUES(col_name) in the UPDATE clause can reference the inserted value of col_name if there is no duplicate keyword conflict. This function is especially useful for multi-row inserts. VALUES() function only in INSERT... It makes sense in the UPDATE statement, otherwise it returns NULL.

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:

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 my actual project: to import data from one table into another table, the repeatability of the data should be considered (as follows), and the index of only 1 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'

Another example:

 INSERT IGNORE INTO `table_1` (`name`) SELECT `name` FROM `table_2`; 
 

0
Other key points: DELAYED as a fast insert, does not care much about failure, improve insert performance.
IGNORE only CARES if the record corresponding to the primary key does not exist, if no, it is added, and if any, it 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 invalidated the null field, that is to say (the a field has a unique index) :

 INSERT INTO `test` (`a`) VALUES (NULL);

Can be inserted repeatedly (the union only 1 index is also 1).