Analyze three commonly used insert statements in mysql and their differences

Three commonly used statements for inserting data in mysql:

1.insert into means insert Data, the database will check the primary key (PrimaryKey), and an error will be reported if there is a duplication;

2.replace into means inserting replacement data. If there is a PrimaryKey or unique index in the demand table, if the database If the data already exists, it will be replaced with new data. If there is no data, the effect will be the same as insert into; the

REPLACE statement will return a number to indicate the number of affected rows. This number is the sum of the number of deleted and inserted rows. If this number is 1 for a single-row REPLACE, then one row is inserted and no rows are deleted. If this number is greater than 1, one or more old rows are deleted before a new row is inserted. If a table contains multiple unique indexes, and the new row copies the values ​​of different old rows in different unique indexes, it is possible that a single row replaces multiple old rows.

3.insert ignore means that if the same record already exists, the current new data will be ignored;

The following is explained through the code The difference is as follows:

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;//仍是1，“bb”,13，因为id是主键，出现主键重复但使用了ignore，则错误被忽略
replace into 
testtb(id,name,age)values(1,"aa",12);
select * from testtb; //数据变为1,"aa",12

[Related recommendations]

1. Share tips for optimizing insert into statements

2. Share a tutorial on inserting multiple records in batches using an insert statement

The above is the detailed content of Analyze three commonly used insert statements in mysql and their differences. For more information, please follow other related articles on the PHP Chinese website!