Comparison between insert ignore and replace into in MySQL

About replace

In a word: Normally the table has primary key or unique index, the new data will replace the old data. Insert the data without old data.

The run of replace is very similar to insert. Except for one point, if an old record in the table has the same value as a new record for primary key or a unique index, the old record is deleted before the new record is inserted. Using replace is the equivalent of a delete operation on the original data (data that is valued under the primary key or unique index), and then the insert operation. In order to be able to use replace, you must have both insert and delete permissions for the table.

Unless the table has a primary key or a unique index, it is meaningless to use a replace statement. The statement is the same as the insert, because no indexes are used to determine whether the new row replicates other rows.

The use of replace is: just want to keep a copy of the data in the database, do not want duplicate data (duplicate primary key, unique index), because the duplicate data is not what we want, will cause trouble to the business logic. But also update some of the most recent values, such as the final check time, the results of the task.

About Insert Ignore

Bottom line: Ignoring the error that occurs when the INSERT statement is executed does not ignore the syntax problem, but ignores the presence of the primary key.

If there is no ignore keyword, then an error occurs when the insert data is in a table (the unique index or the primary key has the same value), and the statement execution fails. However, this error does not occur when the Ignore keyword is used, and the new data is not inserted into the datasheet.

Use scenes: such as a multithreaded insert datasheet, you can use Insert ignore to ignore duplicate data in order not to allow multiple threads to insert the same data into the table. For example, your program down, you need to rerun, then pull the data back to insert into the database, this time there may be duplicate data cause errors, ignore can solve this problem.

Some of the connection differences between the two

Contact:

When you do not want to insert the same primary key, unique index into a datasheet, you can use replace or insert ignore to avoid duplicate data.

Difference:

Replace is the same as delete and insert, and there is the process of writing the data.
Insert ignore ignores data that already has a primary key or a unique index, without modification of the data.

Usage scenarios:

If you do not need to update the data, it is recommended that you use Insert ignore, for example: Multithreading inserts the same data.
If you need to update the data to the most recent value, use replace, such as the result of the task, and the last update time.
Something: Since I know there is insert ignore, I will never yigujin use of replace, mother no longer have to worry about the database frequently delete write operation.