Изменение типа первичного ключа с помощью ALTER в MySQL приведёт ли к сбросу значения автоинкремента на 0?

На чтение 3 мин Опубликовано 19.09.2024

Вопрос или проблема

версия=5.7.32-log

я использую такие SQL-запросы для тестирования.

    CREATE TABLE test_table (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255)
    );

    INSERT
        INTO
        test_table (name)
    VALUES ('Alice'),
    ('Bob');

структура таблицы

данные таблицы

и затем выполняю

    ALTER TABLE test_table MODIFY id BIGINT(30);

измененная структура таблицы

вы можете увидеть, что автоинкремент стал 0.

Я пытался найти это в руководстве MySQL и в Google, но не нашел ответов по этой теме.

Более того, когда я тестирую это в 8.0.39-0ubuntu0.22.04.1, автоинкремент не изменяется.

Я хочу знать, является ли это элементом конфигурации, функцией или ошибкой.

Когда вы выполнили ALTER TABLE, вы пропустили опцию AUTO_INCREMENT.

Это делает колонку просто BIGINT, без поведения AUTO_INCREMENT.

В этом случае значение следующего автоинкремента не имеет значения, и метаданные показывают 0.

Когда вы изменяете тип данных, вы должны убедиться, что включили опцию AUTO_INCREMENT:

ALTER TABLE test_table MODIFY id BIGINT AUTO_INCREMENT;

SHOW CREATE TABLE test_table;

Вывод:

CREATE TABLE `test_table` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
                              ^^^

П.С.: Это не связано с вашим вопросом, но я рекомендую использовать просто INT или BIGINT. Аргумент “длина” для целочисленных типов не имеет никакого эффекта. В MySQL 8.0 добавление длины к целому числу считается устаревшим.

Ответ или решение

Изменение типа первичного ключа с помощью оператора ALTER в MySQL может приводить к сбоям в автоинкременте, если вы не укажете правильно опцию AUTO_INCREMENT. Давайте рассмотрим это подробнее.

При создании таблицы вы определили столбец id как INT AUTO_INCREMENT PRIMARY KEY, что позволяет автоматически увеличивать значение этого столбца при добавлении новых записей. Когда вы вводите записи, как в вашем примере, у вас есть записи с id = 1 для ‘Alice’ и id = 2 для ‘Bob’.

Когда вы выполняете следующий оператор:

ALTER TABLE test_table MODIFY id BIGINT(30);

вы изменяете тип столбца id на BIGINT, но при этом не указываете, что данный столбец по-прежнему должен быть автоинкрементным. В результате столбец перестает вести себя как автоинкрементный, и метаданные значения для автоинкремента сбрасываются до 0, поскольку MySQL больше не считает его автоинкрементным полем.

Чтобы избежать этой ситуации и сохранить функциональность автоинкремента, вы должны включить ключевое слово AUTO_INCREMENT в вашем запросе, как показано ниже:

ALTER TABLE test_table MODIFY id BIGINT AUTO_INCREMENT;

Если вы выполните этот запрос и затем проверите структуру таблицы с помощью команды SHOW CREATE TABLE test_table;, вы увидите, что столбец id по-прежнему будет настроен как автоинкрементный, и последующее значение для вставок будет корректно установлено, например:

CREATE TABLE `test_table` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Важно отметить, что аргумент длины для целочисленных типов, который вы указали как BIGINT(30), не оказывает влияния на хранение данных и, с версии MySQL 8.0, является устаревшим. Рекомендуется просто использовать INT или BIGINT без указания длины.

Итак, ваша проблема заключается в том, что не было указания AUTO_INCREMENT при изменении типа столбца. Это не является конфигурационной проблемой или ошибкой в MySQL, а скорее особенностью работы с автоинкрементными полями.