Method to prevent repeated mysql insert records _ MySQL

This article describes in detail how to prevent repeated mysql insert records. if you are interested, you can refer to many methods to prevent repeated mysql insert records. ignore is commonly used, replace, on duplicate key update, of course we can also judge in php.

Solution 1:The code is as follows:

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

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:

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

Solution 2 use Replace:

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

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: deleting conflicting rows with duplicate keyword values from a table tries to insert a new row into the table. the old record has the same value as the new record. the criterion is that 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 number is the number of deleted and inserted rows and the number of affected rows. you can easily determine whether REPLACE only adds one row or whether REPLACE also replaces other rows: check whether the value is 1 (add) or greater (replace ). Example: # eg :( The phone field is a unique index)

The code is as follows:

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

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

The code is 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'

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:

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 a unique column, INSERT is equivalent to the UPDATE statement. The code is as follows:

The 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. 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:

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:

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:

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:

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.

Php prevents repeated insert record instances

Some methods for deleting duplicate records are provided.

SQL statement for querying and deleting duplicate records

1. search for redundant duplicate records in the table. duplicate records are determined based on a single field (peopleId ).

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2. delete unnecessary duplicate records in the table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid

The code is as follows:

delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

3. search for redundant duplicate records in the table (multiple fields)

delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

4. delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid

The code is as follows:

delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5. search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid

The code is as follows:

select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

====================================

SOLVED! Created 3 separated queries for each row, working for now!

So with added unique index to the (auctionid, order) pair have this workable code:

INSERT IGNORE INTOselections(selections.auctionid,selections.order,selections.title,startamount)SELECTauctions.id,1,PlayerA,0.01FROMauctions, gameWHEREauctions.BetfairMark = game.BetfairMarketID;INSERT IGNORE INTOelections(selections.auctionid,selections.order,selections.title,startamount)SELECTauctions.id,2,PlayerB,0.01FROMauctions, gameWHEREauctions.BetfairMark = game.BetfairMarketID

The above is the method to prevent mysql from repeatedly inserting records _ MySQL content. For more information, see PHP Chinese network (www.php1.cn )!