mysql - 为什么 INSERT IGNORE 会增加 auto_increment 主键？

Question

我编写了一个访问 MySQL innodb 数据库的 java 程序。

每当 INSERT IGNORE 语句遇到重复条目时，自动递增主键就会递增。

这种行为是预期的吗？我认为 IGNORE 不应该发生这种情况。这意味着 IGNORE 实际上会导致写入新主键值的额外开销。

表格如下:

CREATE 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;

谢谢!

Answer 1

这是自 MySQL 5.1.22 以来的默认行为。

您可以设置配置变量innodb_autoinc_lock_mode到 0(又名“传统”锁定模式)如果您想避免自动增量列中的间隙。但是，它可能会导致性能下降，因为此模式具有在 INSERT 完成之前保持表锁定的效果。

来自 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.