MySQL INSERT ....ON DUPLICATE UPDATE - Adds one to the autoincrement
Unfortunately the solution is at the application level if you don't want to affect auto-increment id's. Do a SELECT
first and count the result rows. If 0 results, INSERT
the data. If more than 0, UPDATE
that row.
INSERT ... ON DUPLICATE KEY UPDATE
is described as a "mixed-mode insert" for the purposes of InnoDB's AUTO_INCREMENT
handling. Mixed-mode inserts are basically ones where the maximum number of required AUTO_INCREMENT
values is known, but the amount that will actually be needed is not.
Mixed-mode inserts get handled specially by default, as described in the MySQL docs:
...for “mixed-mode inserts”... InnoDB will allocate more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.
If you're using InnoDB, your alternatives are:
- Avoid
INSERT ... ON DUPLICATE KEY UPDATE
. - Set the
innodb_autoinc_lock_mode
parameter to0
, for "traditional" autoincrement lock mode, which guarantees that allINSERT
statements will assign consecutive values forAUTO_INCREMENT
columns. However, this is accomplished by locking during the statement, so there's a performance loss associated with this setting. - (Recommended) Ignore the gaps in the
AUTO_INCREMENT
column.
Note: AUTO_INCREMENT
handling is totally different under MyISAM, which does not exhibit this behavior.
The storage engine must increment the AUTO_INCREMENT
value before inserting a row. It does not know if the insert will fail yet at that point. It cannot simply roll back the increment because there may be other inserts happening on other connections concurrently. This is normal behavior and not something you should (or can) change. The purpose of AUTO_INCREMENT
is to provide unique identifiers, not an unbroken sequence of numbers.