MySql 不能使列 auto_increment
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5402949/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql can't make column auto_increment
提问by Sander Declerck
I have a table "Bestelling" with 4 columns: "Id" (PK), "KlantId", "Datum", "BestellingsTypeId", now I want to make the column Id auto_increment, however, when I try to do that, I get this error:
我有一个包含 4 列的“Bestelling”表：“Id”（PK）、“KlantId”、“Datum”、“BestellingsTypeId”，现在我想让列 Id 自动递增，但是，当我尝试这样做时，我得到这个错误：
ERROR 1062: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
SQL Statement:
ALTER TABLE `aafest`.`aafest_bestelling` CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT
ERROR: Error when running failback script. Details follow.
ERROR 1046: No database selected
SQL Statement:
CREATE TABLE `aafest_bestelling` (
`Id` int(11) NOT NULL,
`KlantId` int(11) DEFAULT NULL,
`Datum` date DEFAULT NULL,
`BestellingstypeId` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Anyone got an idea?
有人有想法吗？
回答by SystemParadox
This will happen if the table contains an existing record with an id of 0 (or negative). Updating all existing records to use positive values will allow auto_increment to be set on that column.
如果表包含 id 为 0（或负数）的现有记录，就会发生这种情况。更新所有现有记录以使用正值将允许在该列上设置 auto_increment。
Edit: Some people asked how that 0 got in there. For clarification, the MySQL Reference Manual states that "For numeric types, the default is 0, with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence." So, if you performed an insert on a table without providing a value for the numeric column before the auto_increment was enabled, then the default 0 would be used during the insert. More details may be found at https://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html.
编辑：有些人问 0 是如何进入的。为了澄清起见，MySQL 参考手册指出“对于数字类型，默认值为 0，但对于使用 AUTO_INCREMENT 属性声明的整数或浮点类型，默认值为序列中的下一个值。” 因此，如果在启用 auto_increment 之前在表上执行插入而不为数字列提供值，则在插入期间将使用默认值 0。更多细节可以在https://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html找到。
回答by Garoke
I also had this issue when trying to convert a column to auto_increment where one row had a value of 0. An alternative to changing the 0 value temporarily is via setting:
在尝试将列转换为 auto_increment 时，我也遇到了这个问题，其中一行的值为 0。临时更改 0 值的替代方法是通过设置：
SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';
for the session.
为会议。
This allowed the column to be altered to auto_increment with the zero id in place.
这允许将列更改为带有零 id 的 auto_increment。
The zero isn't ideal - and I also wouldn't recommend it being used in an auto_increment column. Unfortunately it's part of an inherited data set so I'm stuck with it for now.
零并不理想 - 我也不建议在 auto_increment 列中使用它。不幸的是，它是继承数据集的一部分，所以我现在坚持使用它。
Best to clear the setting (and any others) afterwards with:
最好在之后清除设置（和任何其他设置）：
SET SESSION sql_mode='';
although it will be cleared when the current client session clsoes.
虽然它会在当前客户端会话 clsoes 时被清除。
Full details on the 'NO_AUTO_VALUE_ON_ZERO' setting here.
有关此处“NO_AUTO_VALUE_ON_ZERO”设置的完整详细信息。
回答by Arnaud Le Blanc
This happens when MySQL can not determine a proper auto_increment value. In your case, MySQL choose 1as next auto_increment value, however there is already row with that value in the table.
当 MySQL 无法确定正确的 auto_increment 值时会发生这种情况。在您的情况下，MySQL 选择1作为下一个 auto_increment 值，但是表中已经存在具有该值的行。
One way to resolve the issue is to choose a proper auto_increment value yourself:
解决问题的一种方法是自己选择一个合适的 auto_increment 值：
ALTER TABLE ... CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = 123456;
(Note the AUTO_INCREMENT=123456at the end.)
（注意AUTO_INCREMENT=123456最后。）
回答by Evert
Edit: Don't know exactly how that would be caused, but I do have a workaround.
编辑：不知道究竟是如何引起的，但我确实有一个解决方法。
First, create a new table like the old one:
首先，像旧表一样创建一个新表：
CREATE TABLE aafest_bestelling_new LIKE aafest_bestelling;
Then change the column
然后改变列
ALTER TABLE `aafest`.`aafest_bestelling_new`
CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT
Dump in the new data:
转储新数据：
INSERT INTO aafest_bestelling_new
(KlantId, Datum, BestellingTypeId)
SELECT
KlantId, Datum, BestellingTypeId
FROM aafest_bestelling;
Move the tables:
移动表：
RENAME TABLE
aafest_bestelling TO aafest_bestelling_old,
aafest_bestelling_new TO aafest_bestelling;
Maybe there's some corruption going on, and this would fix that as well.
也许有一些腐败正在发生，这也可以解决这个问题。
P.S.: As a dutchman, I'd highly recommend coding in english ;)
PS：作为一个荷兰人，我强烈推荐用英语编码；)
回答by ironpilot
The easiest way that I have found to solve this issue is to first set the table's AUTO INCREMENTvalue before altering the column. Just make sure that you set the auto increment value higher than the largest value currently in that column:
我发现解决此问题的最简单方法是AUTO INCREMENT在更改列之前首先设置表的值。只需确保您将自动增量值设置为高于该列中当前的最大值：
ALTER TABLE `aafest`.`aafest_bestelling`
AUTO_INCREMENT = 100,
CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT
I tested this on MySQL 5.7 and it worked great for me.
我在 MySQL 5.7 上对此进行了测试，对我来说效果很好。
回答by James Oravec
I had a similar issue. Issue was the table had a record with ID = 0similar to what SystemParadox pointed out. I handled my issue by the following steps:
我有一个类似的问题。问题是该表有一个ID = 0类似于 SystemParadox 指出的记录。我通过以下步骤处理了我的问题：
Steps:
脚步：
- Update record id 0 to be
xwherex = MAX(id)+1 - Alter table to set primary key and auto increment setting
- Set seed value to be
x+1 - Change record id
xback to0
- 更新记录 id 0 到
x哪里x = MAX(id)+1 - 更改表以设置主键和自动增量设置
- 将种子值设置为
x+1 - 将记录 ID 改
x回0
Code Example:
代码示例：
UPDATE foo SET id = 100 WHERE id = 0;
ALTER TABLE foo MODIFY COLUMN id INT(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE foo AUTO_INCREMENT = 101;
UPDATE foo SET id = 0 WHERE id = 100;
回答by Lalit Mehra
This happens because your primary key column already has values.
发生这种情况是因为您的主键列已经有值。
As the error says ...
正如错误所说......
ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
ALTER TABLE 导致 auto_increment 重新排序，导致键 'PRIMARY' 的重复条目 '1'
which means that your column already has a primary key value 1which when you auto_increment that column is reassigned causing duplication and hence this error
这意味着您的列已经有一个主键值1，当您自动增加该列时，该列被重新分配导致重复，因此出现此错误
the solution to this is to remove the primary constraint and then empty the column. Then alter the table setting the primary key again, this time with auto increment.
对此的解决方案是删除主要约束，然后清空该列。然后再次更改表设置主键，这次是自动增量。
回答by Timo Huovinen
This error will also happen if have a MyISAM table that has a composite AUTO_INCREMENTPRIMARY KEYand are trying to combine the keys
如果 MyISAM 表具有组合AUTO_INCREMENTPRIMARY KEY并尝试组合键，也会发生此错误
For example
例如
CREATE TABLE test1 ( `id` int(11) NOT NULL, `ver` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`,`ver`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO test1 (`id`, `ver`) VALUES (1,NULL),(1,NULL),(1,NULL), (2,NULL),(2,NULL),(2,NULL); ALTER TABLE test1 DROP PRIMARY KEY, ADD PRIMARY KEY(`ver`);
