Why INSERT IGNORE increments the auto_increment primary key?

I wrote a java program that accesses a MySQL innodb database.

Whenever an INSERT IGNORE statement encounters a duplicate entry the Auto Increment primary key is incremented.

Is this behaviour the expected? I think it shouldn't happen with IGNORE. That means that IGNORE actually incurs an extra overhead for writing the new primary key value.

The table is the following:

CREAT开发者_JAVA技巧E TABLE `tablename` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `rowname` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rowname` (`rowname`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Thank you!

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.

I believe this is a configurable setting in InnoDB. See: AUTO_INCREMENT Handling in InnoDB

You'd want to go with

innodb_autoinc_lock_mode = 0

INSERT INTO `tablename` (id, rowname) SELECT '1', 'abc' FROM dual WHERE NOT EXISTS(SELECT NULL FROM `tablename` WHERE `rowname`='abc');

or short (because the id field has an increment in the table )

INSERT INTO `tablename` (rowname) SELECT 'abc' FROM dual WHERE NOT EXISTS(SELECT NULL FROM `tablename` WHERE `rowname`='abc');

The solution may look cumbersome, but it works as the author needs.

I think this behaviour is reasonable. The auto-increment should not be relied upon to give sequences that don't have gaps.

For example, a rolled back transaction still consumes IDs:

 INSERT INTO t (normalcol, uniquecol) VALUES 
   ('hello','uni1'),
   ('hello','uni2'),
   ('hello','uni1');

Generates a unique key violation obviously, and inserts no rows into the database (assuming transactional engine here). However, it may consume up to 3 auto-inc values without inserting anything.

Not sure if it's expected, though I would recommend switching to:

INSERT ... ON DUPLICATE KEY UPDATE