Summary of some usage of insert ignore into and replace into in MySQL

 

The following statements may be used for conditional data insertion in MySQL. Create a simple table for testing:

Create table 'books '(

'Id' INT (11) not null AUTO_INCREMENT,

'Name' VARCHAR (200) not null,

Primary key ('id '),

Unique key 'newindex1' ('name ')

) ENGINE = InnoDB default charset = utf8;

1. insert ignore

When data is inserted, if an error occurs, such as duplicate data, no error is returned and only a warning is returned. Therefore, use ignore to ensure that the statement is correct, otherwise it will be ignored. For example:

Insert ignore into books (name) VALUES ('mysql Manual ')

2. on duplicate key update

When primary or unique is repeated, the update statement is executed. For example, if it is useless after update, for example, id = id, the function is the same as 1, but the error is not ignored. For example, to insert duplicate name data without an error, you can use the following statement:

Insert into books (name) VALUES ('mysql Manual') ON duplicate key update id = id

3. insert... Select... Where not exist

You can determine whether to insert data based on the select conditions, 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

If a record with the same primary or unique exists, delete it first. Insert a new record.

Replace into books SELECT 1, 'mysql Manual' FROM books