Why INSERT IGNORE increments the auto_increment primary key?

This has been the default behaviour since MySQL 5.1.22.

You can set the configuration variable innodb_autoinc_lock_mode to 0 (a.k.a “traditional” lock mode) If you’d like to avoid gaps in your auto-increment columns. It may incur a performance penalty, though, as this mode has the effect of holding a table lock until the INSERT completes.

From the docs on InnoDB AUTO_INCREMENT Lock Modes:

innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

The traditional lock mode provides the same behavior that existed
before the innodb_autoinc_lock_mode configuration parameter was
introduced in MySQL 5.1. The traditional lock mode option is provided
for backward compatibility, performance testing, and working around
issues with “mixed-mode inserts”, due to possible differences in
semantics.

In this lock mode, all “INSERT-like” statements obtain a special
table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT
columns. This lock is normally held to the end of the statement (not
to the end of the transaction) to ensure that auto-increment values
are assigned in a predictable and repeatable order for a given
sequence of INSERT statements, and to ensure that auto-increment
values assigned by any given statement are consecutive.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)