为什么插入忽略递增的auto_increment主键?

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

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

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

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

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:

下表如下:

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;

Thank you!

谢谢你！

4 个解决方案

#1

This has been the default behaviour since MySQL 5.1.22.

这是自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.

您可以将配置变量innodb_autoinc_lock_mode设置为0 (a.k。“传统”锁模式)如果您希望避免自动递增列中的空白。不过，这可能会导致性能损失，因为这种模式的作用是在插入完成之前保持表锁。

From the docs on InnoDB AUTO_INCREMENT Lock Modes:

来自InnoDB AUTO_INCREMENT锁定模式的文档:

innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

innodb_autoinc_lock_mode = 0(“传统”锁模式)

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.

传统的锁模式提供了与innodb_autoinc_lock_mode配置参数引入MySQL 5.1之前相同的行为。由于语义上可能存在差异，传统的锁模式选项用于向后兼容性、性能测试和处理“混合模式插入”问题。

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.

在此锁定模式中，所有“插入式”语句都获得一个特殊的表级自动inc锁，用于将插入到具有AUTO_INCREMENT列的表中。这个锁通常持有的最后声明结束(事务),以确保自动递增值被分配在一个可预见的和可重复的订单对于一个给定的INSERT语句序列,并确保连续自动递增值指定的任何声明。

4 个解决方案

#1

更多相关文章

随机推荐