MySql не может сделать столбец auto_increment

Question

У меня есть таблица "Bestelling" с 4 столбцами: "Id" (PK), "KlantId", "Datum", "BestellingsTypeId", теперь я хочу сделать Id столбца auto_increment, однако, когда я пытаюсь это сделать, я получите эту ошибку:

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

Это произойдет, если таблица содержит существующую запись с идентификатором 0 (или отрицательным). Обновление всех существующих записей для использования положительных значений позволит установить auto_increment в этом столбце.

Изменить: Некоторые люди спрашивали, как этот 0 попал туда. Для пояснения в Справочном руководстве MySQL говорится, что "Для числовых типов значением по умолчанию является 0, за исключением того, что для целочисленных типов или типов с плавающей точкой, объявленных с атрибутом AUTO_INCREMENT, значением по умолчанию является следующее значение в последовательности". Таким образом, если вы выполнили вставку в таблицу, не указав значение для числового столбца до того, как 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 с нулевым идентификатором.

Ноль не идеален - и я бы также не рекомендовал использовать его в столбце 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_INCREMENT=123456 в конце.)

Answer 4

Самый простой способ, который я нашел, чтобы решить эту проблему, это сначала установить таблицу AUTO INCREMENT значение до изменения столбца. Просто убедитесь, что вы установили значение автоинкремента выше, чем наибольшее значение в этом столбце:

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. Обновить идентификатор записи 0, чтобы быть x где x = MAX(id)+1
2. Изменить таблицу, чтобы установить первичный ключ и настройку автоматического приращения
3. Установите начальное значение как x+1
4. Изменить идентификатор записи 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

Эта ошибка возникает из-за того, что любая таблица содержит существующую запись с идентификатором 0 (или отрицательным). Обновление всех существующих записей для использования положительных значений позволит установить auto_increment для этого столбца. Если это не сработало, экспортируйте все данные и сохраните их в любом месте на вашем компьютере и не создавайте сначала отношения внешнего ключа, а затем заполните данные в родительской таблице.

Answer 8

Это происходит потому, что ваш столбец первичного ключа уже имеет значения.

Как говорит ошибка...

ALTER TABLE вызывает повторное упорядочение auto_increment, что приводит к дублированию записи "1" для ключа "PRIMARY"

это означает, что у вашего столбца уже есть значение первичного ключа 1, которое, когда вы auto_increment, этот столбец переназначается, вызывая дублирование и, следовательно, эту ошибку

Решение этой проблемы состоит в том, чтобы удалить основное ограничение и затем очистить столбец. Затем измените таблицу, задав первичный ключ снова, на этот раз с автоматическим приращением.

Answer 9

Эта ошибка также произойдет, если таблица MyISAM имеет составную AUTO_INCREMENTPRIMARY KEY и пытаемся совместить ключи

Например

CREATE TABLE test1 (
 `id` int(11) NOT NULL,
 `ver` int(10) без знака NOT NULL AUTO_INCREMENT,
ПЕРВИЧНЫЙ КЛЮЧ (`id`,`ver`)) ENGINE=MySAM CHARSET ПО УМОЛЧАНИЮ =utf8;

INSERT INTO test1 (`id`, `ver`) ЗНАЧЕНИЯ (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 также возникает, если столбец, который вы пытаетесь изменить, включен в отношение внешнего ключа в другой таблице (хотя это не приведет к появлению сообщения об ошибке, упомянутого в вопросе).

(Я добавляю этот ответ, хотя он не относится к конкретному сообщению об ошибке в теле вопроса, потому что это первый результат, который появляется в Google при поиске проблем, связанных с невозможностью установить существующий MySQL столбец в auto_increment.)