MySQL-自动增加键的重复输入错误 [英] Mysql - duplicate entry error for key with auto increment

问题描述

为什么会出现以下形式的错误:

Why do I get an error of the form:

Error in query: Duplicate entry '10' for key 1

...当执行类似INSERT的语句时:

...when doing an INSERT statement like:

INSERT INTO wp_abk_period (pricing_id, apartment_id) VALUES (13, 27)

......，其中13和27是现有pricing和apartment行的有效id，表定义为:

...with 13 and 27 being valid id-s for existing pricing and apartment rows, and the table is defined as:

CREATE TABLE `wp_abk_period` (
  `id` int(11) NOT NULL auto_increment,
  `apartment_id` int(11) NOT NULL,
  `pricing_id` int(11) NOT NULL,
  `type` enum('available','booked','unavailable') collate utf8_unicode_ci default NULL,
  `starts` datetime default NULL,
  `ends` datetime default NULL,
  `recur_type` enum('daily','weekly','monthly','yearly') collate utf8_unicode_ci default NULL,
  `recur_every` char(3) collate utf8_unicode_ci default NULL,
  `timedate_significance` char(4) collate utf8_unicode_ci default NULL,
  `check_in_times` varchar(255) collate utf8_unicode_ci default NULL,
  `check_out_times` varchar(255) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_period_apartment1_idx` (`apartment_id`),
  KEY `fk_period_pricing1_idx` (`pricing_id`),
  CONSTRAINT `fk_period_apartment1` FOREIGN KEY (`apartment_id`) REFERENCES `wp_abk_apartment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_period_pricing1` FOREIGN KEY (`pricing_id`) REFERENCES `wp_abk_pricing` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

在这种情况下key 1 id放在auto_increment上足以不能指定它吗?

Isn't key 1 id in this case and having it on auto_increment sufficient for being able to not specify it?

注意:如果我只是为id提供一个未使用的值，例如INSERT INTO wp_abk_period (id, pricing_id, apartment_id) VALUES (3333333, 13, 27)，它可以正常工作，但是再次将其设置为auto_increment，所以我不需要这样做！

Note: If I just provide an unused value for id, like INSERT INTO wp_abk_period (id, pricing_id, apartment_id) VALUES (3333333, 13, 27) it works fine, but then again, it is set as auto_increment so I shouldn't need to do this!

注意2:好的，这是一个完整的暮光区"时刻::因此，在以巨大的id编号运行上面的查询之后，事情开始正常运行，不再有duplicate entry errors . 有人可以向我解释说WTF是MySQL产生的这种奇怪行为吗?

Note 2: OK, this is a complete "twilight zone" moment: so after running the query above with the huge number for id, things started working normally, no more duplicate entry errors. Can someone explain me WTF was MySQL doing to produce this weird behavior?

推荐答案

可能是您的表的AUTO_INCREMENT值和id列中的实际值已经不正确了.

It could be that your AUTO_INCREMENT value for the table and the actual values in id column have got out of whack.

这可能会有所帮助:

步骤1-从表格中获取最大ID

select max(id) from wp_abk_period

第2步-在表格上对齐AUTO_INCREMENT计数器

ALTER TABLE wp_abk_period AUTO_INCREMENT = <value from step 1 + 100>;

第3步-重试插入

关于为什么，我不知道AUTO_INCREMENT已经摆脱困境.在表中添加数据后添加了auto_increment吗?将数据插入表后更改了auto_increment值吗?

As for why the AUTO_INCREMENT has got out of whack I don't know. Added auto_increment after data was in the table? Altered the auto_increment value after data was inserted into the table?

希望有帮助.

这篇关于MySQL-自动增加键的重复输入错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！