MySQL prevent duplicate Insert Record method summary

MySQL prevent duplicate Insert Record method summary

There are many ways to prevent MySQL from repeatedly inserting records, usually Ignore,replace,on DUPLICATE KEY UPDATE, which we can also judge in PHP.

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:

The code is as follows:

The code is as follows	Copy Code
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 the table and avoid repeating the record: The code is as follows:

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

Scenario Two: Using replace

Syntax format:

The code is as follows:

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:

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 fails because of a duplicate keyword error for a primary key or a unique keyword: Deleting a conflicting row containing duplicate key values from a table again attempts to insert a new row into the table the criterion for the same value as the new record is that the table has a primary key or a unique index, otherwise There is no point in using a replace statement. 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 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)

The code is as follows:

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

In addition, this can be done in SQL Server:

The code is 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 up Date T Set update_time = GETDATE () where phone= ' 1 '

Scenario Three: on DUPLICATE KEY UPDATE

As written above, you can also do this by adding 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:

The code is as follows:

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 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: The code is as follows:

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

The code is as follows:

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 works the same as the following two statements:

The code is as follows:

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

The code is as follows:

The code is as follows	Copy Code
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:

The code is as follows:

The code is as follows	Copy Code
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.

PHP prevents repeated insertions of record instances

The code is as follows

Copy Code

<?php $link =mysql_connect (' localhost ', ' root ', ' 1234 '); Get the MySQL database connection $username =$_get["name"]; Get the data from the client table only son $q = "select * from usertable where user_name= ' $username '"; mysql_query ("SET NAMES gb2312"); Avoid garbled chinese $rs = mysql_query ($q, $link); Query database $num _rows = mysql_num_rows ($RS); Total number of rows that get the result of the query if ($num _rows==0)//fire? Liehuo.net welcome copy, refuse to collect Liehuo maliciously. NET {$exec = "INSERT into student (user_name) VALUES ($username)", mysql_query ("SET NAMES gb2312"), mysql_query ($exec, $lin k); Insert data into database (registered users) without this user echo "User registration is successful!" "; } else {echo "The user name already exists, please re-select the user name!" "; } ? >

Attach some methods to delete duplicate records

Querying and deleting duplicate records of SQL statement 1, finding redundant duplicate records in a table, repeating records are judged by a single field (Peopleid)

The code is as follows	Copy Code
SELECT * from people where Peopleid in (select Peopleid from People GROUP by Peopleid have count (Peopleid) > 1)

2, delete redundant records in the table, duplicate records are based on a single field (Peopleid) to judge, leaving only the smallest ROWID records

The code is as follows	Copy Code
Delete from people where Peopleid in (select Peopleid from People GROUP by Peopleid have count (Peopleid) > 1) and row ID not in (select min (rowid) from people GROUP by Peopleid have Count (Peopleid) >1)

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

The code is as follows	Copy Code
SELECT * from Vitae a WHERE (A.PEOPLEID,A.SEQ) in (select Peopleid,seq to Vitae GROUP by PEOPLEID,SEQ have Count (*) &G T 1)

4. Delete extra duplicate records (multiple fields) in the table, leaving only the record with ROWID minimum

The code is as follows	Copy Code
Delete from Vitae a where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1) and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

5. Find redundant duplicate records (multiple fields) in the table, not including the smallest ROWID records

The code is as follows	Copy Code
SELECT * from Vitae a WHERE (A.PEOPLEID,A.SEQ) in (select Peopleid,seq to Vitae GROUP by PEOPLEID,SEQ have Count (*) &G T 1) and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

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

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

So with added UNIQUE INDEX to the (Auctionid, order) pair has this workable code:

INSERTIGNOREIntoSelections(Selections.Auctionid,Selections.Order,Selections.Title,Startamount)SELECTAuctions.Id,1,Playera,0.01FromAuctions,GameWHEREAuctions.Betfairmark=Game.Betfairmarketid;INSERTIGNOREIntoElections(Selections.Auctionid,Selections.order,selections. Title,startamount) auctions.,2,playerb ,0.01from auctions, Gamewhere. Betfairmark = Game.               

MySQL prevent duplicate Insert Record method summary