MySQL prevent duplicate insertion record method summary

Scenario One: Using the Ignore keyword

If the uniqueness of the record is distinguished by the primary key primary or unique index unique, it is possible to avoid duplicate insertion 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 ', ' 99999 ', ' 9999 ');

This will be ignored when duplicate records are executed, and the number 0 is returned after execution.

Another application is to copy the table and avoid duplicate records:
The code is as follows:

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

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

The run of replace is similar to 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, namely:

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:
Delete conflicting rows from a table that contain duplicate key values
Try inserting the new row into the table again
The old record has the same value as the new record:
The table has a primary key or a unique index, otherwise, it is meaningless to use a replace statement. The statement is the same as the insert, because no indexes are used to determine whether the new row replicates 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
The number of rows affected can easily determine whether replace has added only one row, or if replace has also replaced other rows: check to see if 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 ') inserts 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 add the on DUPLICATE KEY Update method after insert INTO ... If you specify on DUPLICATE key update and the insert row causes duplicate values to occur in a unique index or primary key, the old row update is executed.
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 existing record is updated, the value of the affected row is 2.

NOTE: If column B is also a unique column, 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 the a=1 OR b=2 matches more than one row, only one row is updated. In general, you should try to avoid using the on DUPLICATE key clause on tables with multiple unique keywords.

You can use the values (col_name) function in the UPDATE clause from the INSERT ... The insert portion of the UPDATE statement references the column value. In other words, if no duplicate keyword conflict occurs, values (col_name) in the update clause can reference the value of the inserted col_name. This function is especially useful for multiple rows of inserts. The VALUES () function is only in the insert ... is meaningful in the UPDATE statement 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 acts 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: When you use on DUPLICATE KEY update, the delayed option is ignored.

Example:

This example is what I used in the actual project: to import the data from one table into another, the repeatability of the data should be considered (as follows), the only 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 ', ' table_name2 ' WHERE ' table_name2 '. ' Status ' = 1 On DUPLICATE KEY UPDATE ' table_name1 '. ' Status ' = ' pending '

Put another example:

The code is as follows:

The code is as follows	Copy Code
INSERT into ' class ' SELECT * "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 primary key corresponding record is not present, added without, and ignored.

PHP prevents repeated insertion of record instances

code is as follows

copy code

<?php $link =mysql_connect (' localhost ', ' root ', ' 1234 '); Get the MySQL database connection $username =$_get["name"];//Get Data from client table conveys $q = "SELECT * from usertable where user_name= ' $username ' "; mysql_query ("SET NAMES gb2312");//Avoid Chinese garbled $rs = mysql_query ($q, $link);//Query database $num _rows = Mysql_num_row S ($rs); The total number of rows that get the results of the query if ($num _rows==0) //fire? Liehuo.net welcome replication, refused to malicious acquisition Liehuo. NET { $exec = INSERT INTO student (user_name) values ($username); mysql_query ("SET NAMES gb2312"); mysql_query ($exec, $link);//If no such user is inserted into the database (registered user) Echo user registration is successful! "; } Else { echo "This user name already exists, please select User name again!" "; }

attach some ways to delete duplicate records

SQL statements for querying and deleting duplicate records
1, look for redundant records in the table, duplicate records are based on a single field (Peopleid) to judge

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

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

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

3. Find redundant 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 have count (*) > 1)

4, delete redundant records in the table (multiple fields), leaving only the smallest ROWID records

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, look for redundant records in the table (multiple fields), does not contain the smallest ROWID records

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 has count (*) > 1) and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)