A summary of some usage of insert ignore into in MySQL, replace into, etc.

Some usage summaries of insert ignore into, replace into, etc. in MySQL (GO)

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, an UPDATE statement is executed, such as a useless statement after update, such as Id=id, with the same 1 function, but the error is not 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.

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

Source Address: http://www.educity.cn/wenda/596418.html

A summary of some usages of insert ignore into, replace into, and so on in MySQL (go)