Mysql to prevent repeated insert record summary, mysql to prevent insertion Summary

Mysql to prevent repeated insert record summary, mysql to prevent insertion Summary

Summary of methods for preventing repeated insert records in mysql

 

There are many methods to prevent repeated inserts of records in mysql. ignore, Replace, and on duplicate key update are commonly used. Of course, we can also judge them in php.

 

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:

The Code is as follows:

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

The Code is as follows:	Copy code
Insert ignore into 'table _ 1' ('name') SELECT 'name' FROM 'table _ 2 ';

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

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:

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:

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'

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:

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

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:

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:

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:

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

The Code is as follows:

Copy code

<? Php $ link = mysql_connect ('localhost', 'root', '000000'); // obtain MySQL database connection $ username = $ _ GET ["name"]; // obtain the data transmitted from the client form $ q = "select * from usertable where user_name = '$ username'"; mysql_query ("set names gb2312 "); // avoid Chinese garbled characters $ rs = mysql_query ($ q, $ link); // query the database $ num_rows = mysql_num_rows ($ rs ); // obtain the total number of rows in the query result if ($ num_rows = 0) // fire? Liehuo.net welcome to copy and reject malicious collection of liehuo.net {$ exec = "insert into student (user_name) values ($ username)"; mysql_query ("set names gb2312"); mysql_query ($ exec, exec, $ link); // if this user does not exist, the data is inserted into the database (registered user) echo "User Registration successful! ";} Else {echo" this user name already exists. Please select another user name! ";}?>

Some methods for deleting duplicate records are provided.

SQL statement for querying and deleting duplicate records 1. Find redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleId ).

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

The Code is as follows:	Copy code
Select * from vitae a where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)

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

The Code is as follows:	Copy code
Delete from vitae a where (. peopleId,. 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:	Copy code
Select * from vitae a where (. peopleId,. 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