防止 InnoDB 在 DUPLICATE KEY 上自动递增 [英] Prevent InnoDB auto increment ON DUPLICATE KEY

问题描述

我目前在设置为 auto increment 的主键 ID 有问题.它不断递增 ON DUPLICATE KEY.

I am currently having problems with a primary key ID which is set to auto increment. It keeps incrementing ON DUPLICATE KEY.

例如:

ID | field1     | field2

1  | user       | value

5  | secondUser | value

86 | thirdUser  | value

从上面的描述中，您会注意到我在该表中有 3 个输入，但由于每次更新时自动递增，第三个输入的 ID 为 86.

From the description above, you'll notice that I have 3 inputs in that table but due to auto increment on each update, ID has 86 for the third input.

有没有办法避免这种情况?

Is there anyway to avoid this ?

这是我的 mySQL 查询的样子:

Here's what my mySQL query looks like:

INSERT INTO table ( field1, field2 ) VALUES (:value1, :value2)
            ON DUPLICATE KEY
            UPDATE field1 = :value1, field2 = :value2 

这是我的桌子的样子；

CREATE TABLE IF NOT EXISTS `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` varchar(200) NOT NULL,
  `field2` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `field1` (`field1`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

推荐答案

你可以设置innodb_autoinc_lock_mode 配置选项 "0" 用于传统"自动递增锁定模式，保证所有 INSERT 语句将为 AUTO_INCREMENT 列分配连续的值.

You could set the innodb_autoinc_lock_mode config option to "0" for "traditional" auto-increment lock mode, which guarantees that all INSERT statements will assign consecutive values for AUTO_INCREMENT columns.

也就是说，您不应该依赖于应用程序中连续的自动递增 ID.它们的目的是提供唯一标识符.

That said, you shouldn't depend on the auto-increment IDs being consecutive in your application. Their purpose is to provide unique identifiers.

这篇关于防止 InnoDB 在 DUPLICATE KEY 上自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！