MySQL insert ignore differs from replace into

I've never been in touch with replace into this grammar before, but I've seen a lot of people using this syntax, and it's been used in many production environments, so I've learned a little bit about how to use it repalce into .

about replace

Bottom line: Normally the table has a PRIMARY KEY or a unique index , and the new data replaces the old data. Insert the data without old data.

REPLACEis 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 with values at 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 a table has a primary key or a unique index, it is meaningless to use a replace statement. The statement is the same as insert, because no index is used to determine whether the new row has replicated other rows.

The use of Replace is generally: only want to save a copy of the data in the database, do not want to duplicate the data (duplicate primary key, unique index), because the duplicate data is not what we want, will cause business logic trouble. But update some fields with the latest values, such as the last check time and the result of the task.

The Replace statement returns a number that indicates the number of rows affected. The number is the number of rows that are deleted and inserted, and
The number of rows affected can easily determine if replace adds only one row, or if replace replaces other rows: Check whether the number is 1 (added) or larger (replace).

about Insert Ignore

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

If there is no ignore keyword, then in the Insert data to a table (the unique index or PRIMARY KEY has the same value), an error occurs and the statement execution fails. However, after using the keyword ignore , this error does not occur and the new data is not inserted into the data table.

Usage scenarios: For example, a multi-threaded insert data table, in order to not allow multiple threads to insert the same data into the table, you can use the Insert ignore to ignore duplicate data. For example, if your program is down and needs to be re-run, it will pull the data back into the database again, there may be duplicate data caused by the error, ignore can solve the problem.

Some connection differences between the two

Contact:

You can use replace or to avoid duplicate data when you do not want to insert the same primary key, unique index into the data table insert ignore .

Difference:

• Replace is equivalent to delete and insert, and there is a process for writing the data.
• Insert ignore ignores data that already exists in the primary key or unique index without any modification of the data.

Usage scenarios:

• If you do not need to update the data values, then it is recommended insert ignore to use, such as: multi-threaded insertion of the same data.
• If you need to update the data to the latest value, then use replace , for example, the result of the task, the last update time.

Something: Since I know there is an insert ignore, I will never use replace again Yigujin, mother no longer have to worry about the database of frequent deletion.

References

MySQL insert ignore differs from replace into