MySQL avoids duplicate Insert recording method (Ignore,replace,on duplicate key update,not exist)

The following statements may be used when inserting data into a condition in MySQL, summary. Let's start by building a simple table for testing:

CREATE TABLE ' Books ' (

' ID ' INT (one) not NULL auto_increment,

' Name ' VARCHAR ($) Not NULL,

PRIMARY KEY (' id '),

UNIQUE KEY ' NewIndex1 ' (' name ')

) Engine=innodb DEFAULT Charset=utf8;

1.insert Ignore into

When data is inserted, such as when an error occurs, such as repeating data, no error is returned, only as a warning. So use ignore make sure the statement itself is not a problem, otherwise it will be ignored. For example:

INSERT IGNORE into books (name) VALUES (' MySQL Manual ')

2.on Duplicate key update

When primary or unique repeats, the UPDATE statement is executed, and the contents of the specified field are updated on the basis of the original record, and the contents of the other fields are retained. If the update is a useless statement, such as Id=id, then the same 1 function, but the error will not be ignored. For example, to implement name duplication of data insertion without an error, use the following statement:

INSERT into books (name) VALUES (' MySQL Manual ') on duplicate KEY UPDATE id = ID

3.insert ... select ... where not exist

Judging whether or not to insert according to the condition of select, it can be judged not only by primary and unique, but also by other conditions. For example:

INSERT into books (name) SELECT ' MySQL Manual ' from dual where not EXISTS (SELECT ID from books where id = 1)

4.replace into

If there is a record with the same primary or unique, it is deleted first. Insert a new record again. If the record has more than one field, the newly inserted record will be empty if the field is not assigned a value when it is inserted.

REPLACE into Books SELECT 1, ' MySQL Manual ' from books

Further test cases:

CREATE TABLE ' Books ' (

' ID ' INT (one) not NULL auto_increment,

' Name ' VARCHAR ($) Not NULL,

' Author ' VARCHAR ($) Not NULL,

PRIMARY KEY (' id '),

UNIQUE KEY ' NewIndex1 ' (' name ')

) Engine=innodb DEFAULT Charset=utf8;

INSERT IGNORE into Books (Name,author) VALUES (' MySQL Manual ', ' aaa ');

INSERT into Books (Name,author) VALUES (' MySQL Manual ', ' bbbb ') on DUPLICATE KEY UPDATE id = id+1;

INSERT into Books (Id,name,author) VALUES (1, ' MySQL Manual ', ' bbbb ') on DUPLICATE KEY UPDATE id = id+1;

INSERT into Books (Id,name,author) VALUES (1, ' MySQL manualaaaa ', ' bbbb ') on DUPLICATE KEY UPDATE id = id+1;

REPLACE into Books SELECT 1, ' MySQL Manual ', ' CCCC ' from books;

REPLACE into Books (name,author) SELECT ' MySQL Manual ', ' ddddd ' from books;

http://blog.163.com/wunan_23/blog/static/19556232020125212382924/

http://blog.csdn.net/jbboy/article/details/46828917

http://blog.163.com/wunan_23/blog/static/19556232020125212321897/

MySQL avoids duplicate Insert recording method (Ignore,replace,on duplicate key update,not exist)