Home>

There are many ways to prevent mysql from repeatedly inserting records.Commonly used are ignore, replace, on duplicate key update, of course, we can also judge in PHP.

Option One:Use ignore keyword

If the uniqueness of the records is distinguished by the primary key or unique index,To avoid repeated insertion of records you can use:

code show as below:

This will be ignored when there are duplicate records,Returns number 0 after execution

Another application is copying tables.Avoid duplicate records:The code is as follows:

insert ignore into `table_1` (` name`) select `name` from` table_2`;

Option II:Use replace

Syntax format:

code show as below:

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,Before the new record is inserted,Old records are deleted,which is:

Try to insert a new row into the table

When the insert fails due to duplicate key errors for the primary key or unique key:Delete conflicting rows with duplicate key values ​​from the table and try again to insert new rows into the table where the old record and the new record have the same value The judgment criterion is:The table has a primary key or unique index, otherwise, it does not make sense to use a replace statement.This statement will be the same as insert because no index is used to determine if a new row copied another 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 can easily determine whether replace only added one row,Or does replace replace other lines:check if the number is 1 (add) or greater (replace). Example:#eg:(phone field is a unique index)

code show as below:

replace into `table_name` (` email`, `phone`,` user_id`) values ​​("test569", "99999", "123");

In addition, you can do this in SQL Server:

code show as below:

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"

third solution:on duplicate key update

As written above, you can also add on duplicate key update method after insert into .....If you specify on duplicate key update and inserting a row results in duplicate values ​​in a unique index or primary key,Then execute the old line update. For example, if column a is defined as unique and contains the value 1, the following two statements have the same effect:

code show as below:

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,The insert is equivalent to this update statement: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.In general, you should try to avoid using on duplicate key clauses for tables with multiple unique keywords.

You can use the values ​​(col_name) function in the update clause to reference column values ​​from the insert part of the insert ... update statement.in other words,If there are no duplicate keyword conflicts,Then the values ​​(col_name) in the update clause can reference the value of the inserted col_name. This function is particularly suitable for multi-row inserts.The values ​​() function only makes sense in insert ... update statements,Otherwise it returns null.

code show as below:

insert into `table` (` a`, `b`,` c`) values ​​(1, 2, 3), (4, 5, 6) on duplicate key update `c`=values ​​(` a`) + values (`b`);

This statement has the same effect as the following two statements:

code show as below:

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:the data of one table is imported into another table,The repeatability of the data must be considered (as follows), the unique index is:email:

code show as below:

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:

code show as below:

insert into `class` select * from` class1` on duplicate key update `class`.`course` =` class1`.`course`

Other key:delayed for fast insertion,Not very concerned about failure,Improve insertion performance. ignore only pay attention to whether the corresponding record of the primary key does not exist,Add if not,Ignore if there are.

Php prevent duplicate insert record instance

<?php $link=mysql_connect ("localhost", "root", "1234");//Get mysql database connection $username=$_ get ["name"];//Get passed from client form Over data $q="select * from usertable where user_name =" $username "";mysql_query ("set names gb2312");//Avoid Chinese garbled characters $rs=mysql_query ($q, $link);//Query Database $num_rows=mysql_num_rows ($rs);//Get the total number of rows of the query result if ($num_rows == 0) //Lie Huo ##Welcome to copy,Refuse malicious collection liehuo. net {$exec="insert into student (user_name) values ​​($username)";mysql_query ("set names gb2312");mysql_query ($exec, $link);//If there is no such user, insert data into the database ( (Registered user) echo "User registered successfully!";} Else {echo "The username already exists,Please select a new username! ";}?>

Attach some methods to delete duplicate records

Query and delete duplicate SQL statements

1. Look up redundant duplicate records in the table,Duplicate records are judged 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 redundant duplicate records in the table,Duplicate records are judged based on a single field (peopleid), leaving only the record with the smallest rowid

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, redundant duplicate records in the lookup table (multiple fields)

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 duplicate records (multiple fields) in the table, leaving only the record with the smallest rowid

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, redundant duplicate records (multiple fields) in the lookup table, does not contain the record with the smallest rowid

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 into
selections
(
selections.auctionid,selections.order,selections.title,startamount
)
select
auctions.id,1,playera,0.01
from
auctions, game
where
auctions.betfairmark=game.betfairmarketid
;
insert ignore into
elections
(
selections.auctionid,selections.order,selections.title,startamount
)
select
auctions.id,2,playerb,0.01
from
auctions, game
where
auctions.betfairmark=game.betfairmarketid
  • Previous Considerations for JavaScript data type conversion
  • Next Summary of essential knowledge for learning GO programming