Method to prevent mysql from repeatedly inserting records

ignore,Replace,ON, DUPLICATE, KEY, UPDATE, ON, DUPLICATE, KEY, UPDATE, php, ignore,Replace,ON, DUPLICATE, KEY, UPDATE, ignore,Replace,ON, DUPLICATE, KEY, UPDATE

Scenario 1: Use the ES11en keyword

If the primary key primary or the only index unique is used to distinguish the record from the only index unique, the primary key primary or the only index unique can be used to avoid repeated insert records:

The code is as follows:

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

This will ignore any duplicate records and return 0 after execution

Another application is to copy tables to avoid duplicating records:

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

Scenario 2: Use ES32en

Syntax format:

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:

REPLACE works much like INSERT, but if the old record has the same value as the new record, then the old record is deleted before the new record is inserted, that is:

Attempt to insert a new row into the table

When because for primary or only 1 keyword duplicate key error caused by the insert fails: keyword deleted from the table to contain duplicate values conflict Try again to the new row is inserted into the table Old records and new records have the same value judgment standard is: one table PRIMARY KEY or UNIQUE index, otherwise, use a REPLACE statement does not make sense. This statement would be the same as INSERT, because no index is used to determine whether a new row duplicates another row. Return value: The REPLACE statement returns a number indicating the number of rows affected. This number is the number of deleted and inserted rows, and the number of affected rows. It is easy to determine whether REPLACE added only 1 row, or whether REPLACE also replaced other rows: check whether the number is 1 (added) or greater (replaced). Example: # eg:(the phone field is a 1-only index)

The code is as follows:

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

In addition, SQL Server can be handled like this:

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'

Scheme 3: ON DUPLICATE KEY UPDATE

Such as the & # 8205; On INSERT... INTO... INSERT... . ON DUPLICATE KEY UPDATE method to achieve. If you specified ON DUPLICATE KEY UPDATE and the insertion of a row would result in duplicate values in one 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, then 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 the row is inserted as a new record, the value of the affected row is 1; If the original record is updated, the affected row has a value of 2.

Note: If column b is also the only column, then 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 more than one row direction, only one row is updated. In general, you should try to avoid using the ON DUPLICATE KEY clause for tables with multiple only 1 keywords.

You can use the VALUES(col_name) function in the UPDATE clause from the INSERT... The INSERT part of the UPDATE statement refers to column values. In other words, VALUES(col_name) in the UPDATE clause can refer to the value of the inserted col_name if no duplicate keyword conflict occurs. This function is particularly useful for multi-row inserts. The VALUES() function is only available in INSERT... UPDATE statement is meaningful, otherwise NULL will be 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 has the same effect as the following 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: The DELAYED option is ignored when you use ON DUPLICATE KEY UPDATE.

Example:

This is an example that I use in a real project: when I import data from one table into another table, the repeatability of the data has to be considered (as follows). The only 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'

Here's another example:

The code is as follows:

INSERT INTO `class` SELECT * FROM `class1` ON DUPLICATE KEY UPDATE `class`.`course`=`class1`.`course`

Other key points: As a fast insert, DELAYED does not care much about failure and improves insert performance. IGNORE only cares about the primary key corresponding record does not exist, if there is added, if there is ignored.

php prevents duplicate insertions of record instances

<?php $link=mysql_connect( ' localhost','root','1234'); // get MySQL Database connection  $username=$_GET["name"]; // Get the data passed in from the client form  $q="select * from usertable where user_name='$username'"; mysql_query("SET NAMES gb2312"); // Avoid Chinese garbled code  $rs = mysql_query($q, $link); // Query Database  $num_rows = mysql_num_rows($rs); // Gets the total number of rows of the query result  if($num_rows==0) //  The fire is the ? liehuo.net  Welcome to copy , Reject Malicious Collection   liehuo.net  { $exec="insert into student (user_name) values ($username)"; mysql_query("SET NAMES gb2312"); mysql_query($exec, $link); // If this user is not available, the data is inserted into the database ( Registered users ) echo " User registered successfully! "; } else { echo " The user name already exists, please reselect the user name! "; } ?>

Here are some ways to delete duplicate records

Query and delete duplicate SQL statements

1, Search for redundant duplicate records in the table. Duplicate records are determined by 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 from table. Duplicate records are determined by single field (peopleId), leaving only the smallest record in 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)

SQL > select * from table where id = '1';

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

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

SELECT * FROM rowid SELECT * FROM rowid SELECT * FROM rowid SELECT * FROM rowid SELECT * FROM rowid SELECT * FROM rowid SELECT * FROM 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

The above is all the content of this article, I hope to help you learn.