REPLACE INTO or INSERT IGNORE

[Thomas Bartkus]

The meaning of REPLACE INTO is clear to me.
IF the new record presents new key values, then it is inserted as a new
record.
IF the new record has key values that match a pre-existing record, then
the key violation is ignored and the new record *replaces* the pre-existing
record.

But what about INSERT IGNORE?

Is this a synonym for REPLACE INTO - i.e. it does precisely the same thing?
Or
Does INSERT IGNORE discard the key violation causing record in order to
allow the insert to proceed?

Thomas Bartkus

[Chris Hope]

Thomas Bartkus wrote:

The meaning of REPLACE INTO is clear to me.
IF the new record presents new key values, then it is inserted as a
new
record.
IF the new record has key values that match a pre-existing record,
then
the key violation is ignored and the new record *replaces* the
pre-existing record.

But what about INSERT IGNORE?

Is this a synonym for REPLACE INTO - i.e. it does precisely the same
thing?
Or
Does INSERT IGNORE discard the key violation causing record in order
to allow the insert to proceed?

From the manual at http://dev.mysql.com/doc/mysql/en/INSERT.html
Note in particular the last sentence.

"If you specify the IGNORE keyword in an INSERT statement, errors that
occur while executing the statement are treated as warnings instead.
For example, without IGNORE, a row that duplicates an existing UNIQUE
index or PRIMARY KEY value in the table causes a duplicate-key error
and the statement is aborted. With IGNORE, the error is ignored and the
row is not inserted."

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/