什么可能导致自动增量主键字段（mysql）上出现重复的 id？
已解决
来自开发人员：问题是以前版本的代码仍在写入使用手动 ID 而不是自动增量的表。 自我注意：始终检查表写入的其他可能位置。
我们在表中得到重复的键。 它们不是同时插入的（相隔 6 小时）。
表结构：
CREATE TABLE `table_1` (
`sales_id` int(10) unsigned NOT NULL auto_increment,
`sales_revisions_id` int(10) unsigned NOT NULL default '0',
`sales_name` varchar(50) default NULL,
`recycle_id` int(10) unsigned default NULL,
PRIMARY KEY (`sales_id`),
KEY `sales_revisions_id` (`sales_revisions_id`),
KEY `sales_id` (`sales_id`),
KEY `recycle_id` (`recycle_id`)
) ENGINE= MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=26759 ;
插入：
insert into `table_1` ( `sales_name` ) VALUES ( "Blah Blah" )
我们正在使用 PHP5 运行 MySQL 5.0.20，并使用 mysql_insert_id() 在插入查询后立即检索插入 id。
RESOLVED
From the developer: the problem was that a previous version of the code was still writing to the table which used manual ids instead of the auto increment. Note to self: always check for other possible locations where the table is written to.
We are getting duplicate keys in a table. They are not inserted at the same time (6 hours apart).
Table structure:
CREATE TABLE `table_1` (
`sales_id` int(10) unsigned NOT NULL auto_increment,
`sales_revisions_id` int(10) unsigned NOT NULL default '0',
`sales_name` varchar(50) default NULL,
`recycle_id` int(10) unsigned default NULL,
PRIMARY KEY (`sales_id`),
KEY `sales_revisions_id` (`sales_revisions_id`),
KEY `sales_id` (`sales_id`),
KEY `recycle_id` (`recycle_id`)
) ENGINE= MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=26759 ;
The insert:
insert into `table_1` ( `sales_name` ) VALUES ( "Blah Blah" )
We are running MySQL 5.0.20 with PHP5 and using mysql_insert_id() to retrieve the insert id immediately after the insert query.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
过去，即使定义了主键并且自动增量，我也曾在MySql数据库中突然出现过一些重复键错误。 每次都是因为表已损坏。
如果它被损坏，执行检查表应该会暴露问题。 您可以通过运行来完成此操作：
如果无论如何它都损坏了（通常会说大小比实际应该的大），那么只需运行以下命令即可修复它：
I have had a few duplicate key error suddenly appear in MySql databases in the past even though the primary key is defined and auto_increment. Each and every time it has been because the table has become corrupted.
If it is corrupt performing a check tables should expose the problem. You can do this by running:
If it comes back as corrupt in anyway (Will usually say the size is bigger than it actually should be) then just run the following to repair it:
sales_id 字段是否有主（或唯一）键？ 如果没有，则可能是其他原因正在重复使用现有数字进行插入或更新。 我所说的“其他东西”并不仅仅指代码；我指的是其他东西。 可能是有权访问数据库的人意外地执行了此操作。
Does the sales_id field have a primary (or unique) key? If not, then something else is probably making inserts or updates that is re-using existing numbers. And by "something else" I don't just mean code; it could be a human with access to the database doing it accidentally.
正如对方所说； 以你的例子来说这是不可能的。
这与您的问题无关，但您不必为主键列创建单独的 KEY - 它只是在您已经拥有唯一（主）键时向表添加额外的非唯一索引。
As the other said; with your example it's not possible.
It's unrelated to your question, but you don't have to make a separate KEY for the primary key column -- it's just adding an extra not-unique index to the table when you already have the unique (primary) key.
您的意思是您在尝试插入时遇到错误，还是您的意思是您在列中多次存储了某些值？
仅当您从 INSERT 中省略该列或尝试插入 NULL 或零时，自动增量才会启动。 否则，您可以在 INSERT 语句中指定一个值，从而覆盖自动增量机制。 例如：
如果您指定的值已存在于表中，您将收到错误消息。
Do you mean you are getting errors as you try to insert, or do you mean you have some values stored in the column more than once?
Auto-increment only kicks in when you omit the column from your INSERT, or try to insert NULL or zero. Otherwise, you can specify a value in an INSERT statement, over-riding the auto-increment mechanism. For example:
If the value you specify already exists in the table, you'll get an error.
如果您在其他字段上有唯一键，则可能是问题所在。
如果您已达到 auto_increment 列的最高值，MySQL 将继续尝试重新插入它。 例如，如果 sales_id 是一个tinyint 列，则在达到 id 127 后，您将收到重复键错误。
If you have a unique key on other fields, that could be the problem.
If you have reached the highest value for your auto_increment column MySQL will keep trying to re-insert it. For example, if sales_id was a tinyint column, you would get duplicate key errors after you reached id 127.
请发布此查询的结果：
Please post the results of this query: