MySQL avoids repeatedly inserting records

Today, using Python to crawl data storage needs to avoid duplication of data insertion, on the Internet to find some methods:

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:

Insert ignore into table_name (EMAIL,PHONE,USER_ID) VALUES (' [email protected] ', ' 99999 ', ' 9999 '), so when there are repeated

The record will be ignored, after execution returns the number 0, there is another application is to copy the table, to avoid duplicate records:

Insert ignore into table (name) select name from Table2

For example, the following table record is a valid ignore into syntax for inserting data:

+-----+------+------+   | id | name | type |   +-----+------+------+   1011 |   102 2 |   103 3 |   203 3 |   202 2 |   201 1 |   +-----+------+------+   

If you encounter a duplicate ID if you do not add ignore, you will get an error because the unique constraint is violated.

Scenario Two: Using replace

The syntax format for replace is:

1. Replace into table_name (col_name, ...) VALUES (...)

2. Replace into table_name (col_name, ...) Select ...

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

1. try inserting a new row into the table

2. When an insert fails because of a duplicate keyword error for a primary key or a unique keyword:

Delete conflicting rows with duplicate key values from the table

Try inserting a new row into the table again

The criterion for the same value of the old record and the new record is that the table has a PRIMARY KEY or a UNIQUE index, otherwise, using a REPLACE statement has no meaning

。 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 is the number of rows that are deleted and inserted.

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)

Replace into table_name (EMAIL,PHONE,USER_ID) VALUES (' test569 ', ' 99999 ', ' 123 ')

In addition: This can be done in SQL Server:

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 '

For more information, see:http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#replace

Scenario Three: on DUPLICATE KEY UPDATE

As written above, you can also insert into ..... followed by the on DUPLICATE KEY Update method to implement.

If you specify an on DUPLICATE KEY UPDATE, and the row is inserted, it causes a UNIQUE index

or a duplicate value in the PRIMARY KEY,

The old line UPDATE is executed . For example, if column a is defined as UNIQUEand contains a value of 1, thefollowing two statements have a phase

The same effect:

mysql>INSERT into table (a,b,c) VALUES

-OnDUPLICATE KEY UPDATE c=c+1;  

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

mysql> 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 duplicate keyword conflicts do not occur, the values (col_name) in the update clause can refer to the inserted

The value of the col_name. This function is especially useful for multi-row insertions. The VALUES () function is only in the insert ... The UPDATE statement is meaningful, and other times

will return NULL.

mysql> INSERT into table (a,b,c) VALUES (4,5,6)

-On DUPLICATE KEY UPDATE c=values (a) +values (b);

This statement works the same as the following two statements:

mysql> INSERT into table (a,b,c) VALUES

-On DUPLICATE KEY UPDATE c=3;

mysql> INSERT into table (a,b,c) VALUES (4,5,6)

-On DUPLICATE KEY UPDATE c=9;

When you use the on DUPLICATE KEY update, the delayed option is ignored.

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

The unique index is: email

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 ',

From table_name2

WHERE table_name2.status = 1

On DUPLICATE KEY UPDATE table_name1.status = ' Pending '

The key place of the statement, has been highlighted ~

One more example:

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.

For more information, see: http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert

Special Note: In MySQL , the unique index will invalidate the null field, i.e. (a unique index on the A field):

INSERT into Test (a) values (NULL)

INSERT into Test (a) values (NULL)

can be repeatedly inserted (the same as the Federated Unique Index).

Original: http://www.cnblogs.com/zeroone/archive/2012/04/18/2454728.html

Ignore

Ignore into

Ignore

The judgment is present, there is no insertion, otherwise inserted. It is easy to understand that when inserting a violation of a uniqueness constraint, MySQL does not attempt to execute the statement

MySQL avoids repeatedly inserting records