Transferred from: Http://blog.163.com/[email protected]/blog/static/173131045201222122732435/
In MySQL, insert into and replace into and insert ignore usage differences:
Three commonly used statements for inserting data in MySQL:
Insert into indicates the data is inserted, the database will check the primary key, if there is a repeat error;
Replace into represents inserting replacement data, a PrimaryKey in the requirements table, or a unique index, which is replaced with new data if there is already data in the database, and if no data effects are inserted into the insert;
Insert Ignore indicates that if the same record already exists, the current new data is ignored;
The following are the differences between code descriptions, 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;//is still 1, "BB", 13, because ID is primary key, duplicate primary key but use ignore error is ignored
Replace into TESTTB (id,name,age) VALUES (1, "AA", 12);
SELECT * from TESTTB; Data becomes 1, "AA", 12
MySQL Insert primary key repeat problem