MySql无法使列自动增量

Question

我有一个表“Bestelling”，有 4 列：“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

有人有想法吗？

Answer 1

如果表包含 id 为 0（或负数）的现有记录，就会发生这种情况。更新所有现有记录以使用正值将允许在该列上设置 auto_increment。

编辑：有些人问那个 0 是怎么进去的。为了澄清起见，MySQL 参考手册指出“对于数字类型，默认值为 0，但对于使用 AUTO_INCRMENT 属性声明的整数或浮点类型除外，默认值是序列中的下一个值。”因此，如果在启用 auto_increment 之前对表执行插入操作而没有为数字列提供值，则在插入期间将使用默认值 0。更多详细信息可以在 https://dev.mysql.com/doc/ 找到refman/5.0/en/data-type-defaults.html。

Answer 2

当尝试将列转换为 auto_increment（其中一行的值为 0）时，我也遇到了这个问题。暂时更改 0 值的另一种方法是通过设置：

SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';

对于会话。

这允许将列更改为 auto_increment，并且 id 为零。

零并不理想 - 我也不建议在 auto_increment 列中使用它。不幸的是，它是继承数据集的一部分，所以我现在坚持使用它。

最好在之后清除该设置（以及任何其他设置）：

SET SESSION sql_mode='';

尽管当前客户端会话关闭时它将被清除。

有关“NO_AUTO_VALUE_ON_ZERO”设置的完整详细信息此处。

Answer 3

当 MySQL 无法确定正确的 auto_increment 值时，就会发生这种情况。在您的情况下，MySQL 选择 1 作为下一个 auto_increment 值，但是表中已经存在具有该值的行。

解决该问题的一种方法是自己选择合适的 auto_increment 值：（

ALTER TABLE ... CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = 123456;

请注意最后的 AUTO_INCRMENT=123456。）

Answer 4

我发现解决此问题的最简单方法是在更改列之前首先设置表的自动增量值。只需确保将自动增量值设置为高于该列中当前的最大值即可：

ALTER TABLE `aafest`.`aafest_bestelling` 
AUTO_INCREMENT = 100, 
CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT

我在 MySQL 5.7 上对此进行了测试，它对我来说非常有用。

Answer 5

编辑：不知道具体是如何造成的，但我确实有一个解决方法。

首先，创建一个像旧表一样的新表：

CREATE TABLE aafest_bestelling_new LIKE aafest_bestelling;

列

ALTER TABLE `aafest`.`aafest_bestelling_new` 
CHANGE COLUMN `Id` `Id` INT(11) NOT NULL AUTO_INCREMENT

然后更改新数据中的转储

INSERT INTO aafest_bestelling_new
 (KlantId, Datum, BestellingTypeId) 
SELECT 
KlantId, Datum, BestellingTypeId 
FROM aafest_bestelling;

：移动表：

RENAME TABLE 
aafest_bestelling TO aafest_bestelling_old, 
aafest_bestelling_new TO aafest_bestelling;

也许发生了一些损坏，这也可以解决这个问题。

PS：作为一个荷兰人，我强烈建议用英语编码；）

Answer 6

我有类似的问题。问题是该表有一条 ID = 0 的记录，与 SystemParadox 指出的类似。我通过以下步骤处理了我的问题：

步骤：

1. 将记录 id 0 更新为 x 其中 x = MAX(id)+1
2. 更改表设置主键和自动增量设置
3. 将种子值设置为 x+1
4. 将记录 ID x 更改回 0

代码示例：

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;

Answer 7

出现此错误的原因是 Any 表包含 id 为 0（或负数）的现有记录。更新所有现有记录以使用正值将允许在该列上设置 auto_increment。
如果这不起作用，则导出所有数据并将其保存在计算机中的任何位置，并且不要先建立外键关系，然后在父表中填充数据。

Answer 8

发生这种情况是因为您的主键列已经有值。

正如错误所述...

ALTER TABLE 导致 auto_increment 重新排序，导致键“PRIMARY”出现重复条目​​“1”，

这意味着您的列已经具有主键值 1 > 当您 auto_increment 时，该列被重新分配，导致重复，因此出现此错误

，解决方案是删除主约束，然后清空该列。然后再次更改表设置主键，这次使用自动增量。

Answer 9

如果 MyISAM 表具有复合 AUTO_INCRMENT PRIMARY KEY 并且尝试组合键，也会发生此错误

例如

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`);

Answer 10

如果您尝试修改的列包含在另一个表的外键关系中，也会发生无法将现有列设置为 auto_increment 的情况（尽管它不会产生提到的错误消息在问题中）。

（我添加了这个答案，尽管它与问题正文中的特定错误消息无关，因为这是在搜索与无法设置现有 MySQL 相关的问题时在 Google 上显示的第一个结果列自动递增。）