Differences between insert ignore, insert into, and replace into in mysql

In mysql, the insert ignore, insert into, and replace into statements INSERT data INTO the mysql database. What is the difference between them?

The following statements are commonly used in mysql to insert data:

Insert into indicates that the data is inserted. The database checks the primary key and reports an error if repeated Values exist;
Replace into indicates that data is inserted and replaced. The request table contains the PrimaryKey or unique index. If data already exists in the Database, replace it with new data. If no data effect exists, replace it with insert;
Insert ignore indicates that if the same record already exists, the current new data is ignored;

The following code describes the differences between them:

Create table testtb (
Id int not null primary key,
Name varchar (50 ),
Age int
);

Insert into testtb (id, name, age) values (1, "bb", 13 );
Select * from testtb;
Insert ignore into testtb (id, name, age) values (1, "aa", 13 );
Select * from testtb; // It is still 1, "bb", 13. Because id is the primary key, the primary key is repeated, but ignore is used, the error is ignored.
Replace into testtb (id, name, age) values (1, "aa", 12 );
Select * from testtb; // The data is changed to 1, "aa", 12

 

Example

 

Insert

 

Table 1

 

Id name

 

1 tb

 

2 zp

 

Table 2

 

Id (primary key) name

 

1 tb

 

2 cw

 

3 zp

 

Insert ignore into table1 select * from table2 execution result:

 

Table1

 

Id name

 

1 tb

 

2 zp

 

3 zp

 

Note: If the insert into statement is used to identify duplicate data, an error is returned. If the insert ignore into statement is used to find that the field value containing the unique index already exists in the Data row to be inserted, this row of data is discarded, no Processing

 

Replace

 

Table 1

 

Id name ps

 

1 tb

 

2 zp B

 

Table 2

 

Id (primary key) name

 

1 tb

 

2 cw

 

3 zp

 

Replace into table1 select * from table2: The execution result is

 

Table1

 

Id name ps

 

1 tb NULL

 

2 cw NULL

 

3 zp NULL

 

Note: REPLACE finds that repeated fields are deleted before insertion. If there are multiple fields in the record and some fields are not assigned a value during insertion, the newly inserted fields are empty.

Summary

The difference between nsert ignore and insert into is that insert ignore ignores existing data in the database. If there is no data in the database, INSERT new data. If there is data, skip this data. In this way, the existing data in the database can be retained to insert data in the gap.